select all rows with a minimum value

Question

In Sqlite 3 I'm trying to figure out how to select rows based on a minimum value. I think that I'm limited by not knowing enough of the related terminology to effectively search google.

The table looks like:

num         text        num2      
----------  ----------  ----------
0           a           1         
0           a           2         
1           a           3         
1           b           4         

I want to get the rows where num2 is 1, 2, and 4. I want to do the selection based on the minimum value of num for each unique value of the text column.

So, for text = 'a', the minimum value of numis 0, so I want rows 1 and 2. For text = 'b', the minimum value of num is 1, so I want row 4.

Using various combination of group by, I'm able to get either rows 1 and 2 or rows 1 and 4. I feel like I'm missing a SQL component that would do what I want, but I haven't been able to figure out what it could be.

What is the proper way to do this type of query?

Possible Solution

I've found a way to do this. I'm not reputable enough to answer my own question, so I'm doing the update here. I'm not sure if it is always correct or what the efficiency is like. Any comments are welcome.

I used a compound select statement, where one query finds the minimum value of num for each unique value of text:

sqlite> select num, text from t group by text having num = min( num );
num         text      
----------  ----------
0           a         
1           b         

Then I joined this with the full table to get all rows matching these two columns.

sqlite> with u as
      ( select num, text from t group by text having num = min( num ) )
        select t.* from t join u on t.num = u.num and t.text = u.text;
num         text        num2      
----------  ----------  ----------
0           a           1         
0           a           2         
1           b           4         

Answer 1

As you have seen, a simple GROUP BY will not work because it would return only one record per group.

Your join works fine. For a large table, it will be efficient only if there is an index on the join columns (num and text).

Alternatively, you could use a correlated subquery:

SELECT *
FROM t
WHERE num = (SELECT MIN(num)
             FROM t AS t2
             WHERE t2.text = t.text);

SQLFiddle

When being executed, this query does not require a temporary table (your query does for the result of u), but will execute the subquery for each record in t, so text should be indexed. (Or use an index on both text and num to get a covering index.)

Answer 2

So how can you find the answer for your question yourself the next time? In my opinion, it is by decomposing and following the logic. And you got that right:

I want to do the selection based on the minimum value of num for each unique value of the text column

This translates to:

select text, min(num) from t group by text;

(This should be equivalent to your having query. It might be interesting to have a look at the rows where num equals NULL. The be more precise: Have a look what effect rows with nulls have, which you might want to filter out first with a where num is not null)

From here you can achieve your wanted result by:

select * from t where (num, text) in ( *insert query above* )

Or using a join:

select t1.* from t t1,
    (select text, min(num) as n from t group by text) t2
where t1.num = t2.n and t1.text = t2.text.

And when performance is not sufficient for your tables, start looking at more complex statements.

Answer 3

I tend to do this type of thing with an outer self join:

SELECT
    M1.Num,
    M1.Text,
    M1.Num2
FROM
    MyDb M1
LEFT OUTER JOIN
    MyDB M2
ON
    M1.text = M2.text
AND
    M1.num > m2.num
WHERE
    M2.num is null

This is basically saying; give me all records that don't have a higher value i.e. null.

Answer 4

Shouldn't be this query exactly what you need?

select min(num), text, num2 group by text, num2