8

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         
8

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

  • he doesn't have any temporary table in his query, only a CTE, which is quite different. – ypercubeᵀᴹ Mar 7 '14 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 '14 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 '14 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 '14 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 '14 at 12:25
1

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.

1

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.

-2

Shouldn't be this query exactly what you need?

select min(num), text, num2 group by text, num2
  • This will return all four records, because the num2 values are unique. – CL. Mar 7 '14 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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