选择有限数量的不同行

Question

I have a table in this format:

Name     | Description    | option_id
"Apple"  | "Sweet fruit"  | 3
"Apple"  | "Sour fruit"   | 4
"Pear"   | "Sweet fruit"  | 5
"Orange" | "Orange fruit" | 3
"Carrot" | "Vegetable"    | 3

I run the query SELECT * FROM table WHERE Description LIKE '%fruit%' and get this:

Name     | Description    | option_id
"Apple"  | "Sweet fruit"  | 3
"Apple"  | "Sour fruit"   | 4
"Pear"   | "Sweet fruit"  | 5
"Orange" | "Orange fruit" | 3

I want to get only 2 distinct fruit Names, so the result of query should be:

Name     | Description    | option_id
"Apple"  | "Sweet fruit"  | 3
"Apple"  | "Sour fruit"   | 4
"Orange" | "Orange fruit" | 3

But I obviously don't get 3 records, as using ORDER BY Name ASC LIMIT 2 causes query to stop when there are 2 records ready.

I want to produce as many records as possible, as soon as their name is in first 10 of distinct. Here is what I came up so far:

SELECT * FROM table WHERE Name IN (
    SELECT DISTINCT Name ORDER BY Name ASC LIMIT 2
) ORDER by option_id ASC

Is there a better way of doing it without nesting? Query should be as fast as possible.

P.S. To complicate the matters, this is not my full query, but a mere part of it. In full, it looks something like this (without LIMIT):

SELECT table.*,
    MATCH (Name, Description) AGAINST ('fruit') AS relevance,
    MATCH (Name) AGAINST ('fruit') AS name_relevance
FROM table
WHERE MATCH (Name, Description) AGAINST ('fruit')
GROUP BY Name
ORDER BY name_relevance DESC, relevance DESC, Name ASC

And I Name is more than just a name, but also some information about it, such as "Fresh Green Apple", "Apple Fruit", etc.

Answer 1

One way or another, you need to get the list of names that you are accepting. Doing this in a subquery, as you have, is probably the simplest.

You can also use a temporary table which will probably speed this up a bit.

Perhaps Try Comparing these two. I suspect the second will be faster with bigger tables.

Here is one that's essentially the same as yours I believe

WITH fruit AS (SELECT TOP 2 DISTINCT Name FROM table1 WHERE Description LIKE '%fruit%' ORDER BY Name ASC)

SELECT * FROM table WHERE Name IN (
    SELECT * FROM fruit
) ORDER by option_id ASC

This one uses the Temp table

Create TABLE #fruit (Name NVARCHAR(50))
INSERT INTO #fruit
SELECT TOP 2 DISTINCT Name FROM table1 WHERE Description LIKE '%fruit%' ORDER BY Name ASC

SELECT * FROM table WHERE Name IN (
    SELECT * FROM #fruit
) ORDER by option_id ASC

Edit for MySQL

Here is a TempTable solution for MySQL

CREATE TEMPORARY TABLE fruit
SELECT DISTINCT Name FROM table1 WHERE Description LIKE '%fruit%' ORDER BY Name ASC LIMIT 2

SELECT * FROM table WHERE Name IN (
    SELECT * FROM fruit
) ORDER by option_id ASC

Edit to use Join

This should preserve the performance gains above, although it might be worth testing.

CREATE TEMPORARY TABLE fruit
SELECT DISTINCT Name, order FROM table1 WHERE Description LIKE '%fruit%' ORDER BY Name ASC LIMIT 2

SELECT * FROM table t
JOIN fruit f ON t.Name = f.Name
ORDER by f.order