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 num
is 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
Best Answer
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
andtext
).Alternatively, you could use a correlated subquery:
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 int
, sotext
should be indexed. (Or use an index on bothtext
andnum
to get a covering index.)