Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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         
share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.)

share|improve this answer
    
he doesn't have any temporary table in his query, only a CTE, which is quite different. –  ypercube Mar 7 at 10:04
    
When executed, the result of the u query is stored in a temporary table, regardless of whether it's written as a CTE, a view, or inline as a subquery. –  CL. Mar 7 at 10:58
    
Thanks, this version is much easier to write than the one I stumbled into. Knowing the right terminology is also helpful for me to look into this more. –  user35292 Mar 7 at 12:00
    
@CL Is that how SQLite executes queries with CTEs? Do you have a reference for that? Because other DBMS do not necessarily use temp tables for ctes. –  ypercube Mar 7 at 12:04
    
@ypercube CTEs, views, and subqueries are flattened or implemented as coroutines, if possible. But a GROUP BY on a non-indexed column must be able to collect the data for all groups in parallel, so it requires some form of temporary table (in all databases). –  CL. Mar 7 at 12:25

Shouldn't be this query exactly what you need?

select min(num), text, num2 group by text, num2
share|improve this answer
    
This will return all four records, because the num2 values are unique. –  CL. Mar 7 at 8:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.