CODEWITHSUNDEEP
sqldatabasesqlite
littleK
littleK

Reputation: 20123

SQLite Result is out of order when using DISTINCT

I have an SQLite query in which I am ordering the results by date. This works fine. However, when I introduce "DISTINCT" into the SQL statement, the results no longer stay in order.

Any thoughts?

SELECT date 
FROM details 
ORDER BY date(date); // Results are ordered correctly

     April 04, 2011
     April 04, 2011
     April 04, 2011
     April 03, 2011
     April 01, 2011
     March 25, 2011

SELECT DISTINCT date 
FROM details 
ORDER BY date(date); // Results are out of order

     April 01, 2011
     April 03, 2011
     April 04, 2011
     March 25, 2011

Upvotes: 0

Views: 692

Answers (2)

manji
manji

Reputation: 47968

Your date column is not in recognized by the date() function, so date(date) gives NULL.

Try both queries with ORDER BY NULL you'll have the same results as with ORDER BY date(date)

Without DISTINCT it's ordering them by insert order.

With DISTINCT, the order comes from DISTINCTitself and is applied to the date column which is a text so it's in alphabetic order.

SQLite does not support month names. Change your data to supported formats (see Date And Time Functions) or transform the date column to recognized format before giving it to date()(with string functions)

For example (transforming date to the format YYYY-mmm-dd before calling date() works):

(here CASE WHEN is limited to your data, you can extend it to all months)

SELECT DISTINCT date 
  FROM (SELECT *,
               substr(date, -4) year,
               substr(date, -8, 2) day,
               trim(substr(date, 1, length(date)-8)) month
         FROM details) as details
 ORDER BY date(year
               || '-'
               || CASE WHEN month = 'April' THEN '04' ELSE '03' END
               || '-'
               || day)

, will give the correct results with or without DISTINCT.

Upvotes: 3

BrianH
BrianH

Reputation: 8241

I don't think your date string is in a valid date format.

Try running this SQL to see what you get:

SELECT strftime('%Y-%m-%d', date) FROM details;

If you get errors, it is not parsing your dates correctly.

Your second query is returning in alphabetic order, not chronological order - if it is not a valid date in that field, that would be the reason.

Upvotes: 1

Related Questions