Odd behavior with select distinct in sqlite

Databases
1

The query: select distinct projid from task order by changed desc limit 5

If I run this, I receive five rows, as expected. However, if one uses a database tool (e.g. Valentina Studio or SqliteManager) and asks it to sort on the changed column (descending), rows appear that should be included in the result of the query but are not (the query should include only one row because of “distinct”).

There is another column in the table (“taskid”), and it is blank for the rows in question. If I change the query to “select distinct projid, taskid…”, I get the same result. If I put a value in the taskid column (anything), I get the expected result.

I’ve created a database that shows this behavior (ripped out all non-relevant tables and columns), however, it’s not obvious how to upload it.

2

You need to put your sample in Dropbox, Google Drive, One Drive, other, and share the download link here.

3

Thanks. Here’s a link:

4

I’m not sure what is the problem that you are seeing. Using ‘limit 5’ at the end only shows 5 records, removing that shows 17 using Valentina. What do you get?

EDIT: as far as I can see all ‘projid’ are distinct

5

I got same results as @AlbertoD using DB Browser for SQLite

6

What are you wanting to achieve?

7

My guessing is, list of projects and the last changes:

SELECT
	projid,
	max( changed ) last_change 
FROM
	task 
GROUP BY
	projid 
ORDER BY
	projid ASC,
	changed DESC;
projid last_change
1675702510
JAJJDSYXAIZ 1679766086
JAYJASMDRJR 1674578791
JAZQDAMWIKU 1674686174
JBAJBRWNASH 1675466530
JBOHBRWPKQB 1677083807
JBSKCYECAHJ 1676560549
JBSMDJSABJS 1676391261
JBVIBNGNIUD 1676557251
JBWGDOIUKTB 1678640972
JCAODMAUMGM 1677282108
JCCIDXOVLMY 1678873564
JCFIEGUKHNH 1679171117
JCMMANWZMFF 1678379819
JCSMBIQOKLE 1678657007
JCYICBCGGMF 1679232263
JDAOCVWDURW 1679658810
8

If from those you want projects in order of last changes, reorder the findings:

SELECT
	* 
FROM
	( SELECT projid, max( changed ) last_change FROM task GROUP BY projid ORDER BY projid ASC, changed DESC ) recent_ones 
ORDER BY
	last_change DESC

If you want the top 5:

SELECT
	* 
FROM
	( SELECT projid, max( changed ) last_change FROM task GROUP BY projid ORDER BY projid ASC, changed DESC ) recent_ones 
ORDER BY
	last_change DESC 
	LIMIT 5;
9

Thanks for all the help.

The issue is that if you do the query I posted, projid “JAJJDSYXAIZ” does not show up, and I think it should.

10

Ok, query: SELECT DISTINCT projid FROM task ORDER BY changed DESC LIMIT 5

you have several projid = JAJJDSYXAIZ right? each one with different changed so what you expect is that SQLite assign the max changed value to JAJJDSYXAIZ so that will show on your list but that is not how SQLite works for this, you will need to use a different query.

This query from Rick is what you want (I think):

SELECT
	* 
FROM
	( SELECT projid, max( changed ) last_change FROM task GROUP BY projid ORDER BY projid ASC, changed DESC ) recent_ones 
ORDER BY
	last_change DESC 
	LIMIT 5;

or

SELECT
	projid 
FROM
	( SELECT projid, max( changed ) last_change FROM task GROUP BY projid ORDER BY projid ASC, changed DESC ) recent_ones 
ORDER BY
	last_change DESC 
	LIMIT 5;

to show:
image

11

Ah. Of course. The order by is on the result not part of the query.

Haven’t done this in awhile.

Much thanks to everyone. What a great group of people.

2 Likes
12

Again, thanks to all.

What I did (this morning) is simply ask for the most recent rows (e.g. changed > some value).