CODEWITHSUNDEEP
sqlite
JohnTit
JohnTit

Reputation: 79

Sorting dates using SQL

How can I sort by date here, while keeping all the parameters? I'm sorry, I'm really stupid in SQL.

SELECT 
  chat_identifier, 
  datetime(date/1000000000 + 978307200,'unixepoch','localtime'), 
  case when [error]=0 then 'ok' when [error]=1 then 'Not ok' end 
FROM 
  chat 
  INNER JOIN message 
    ON chat.ROWID = message.ROWID"

This query returns this:

[('Alex',), ('2020-02-16 05:22:49',), ('ok',)]
[('Grace',), ('2020-02-20 04:17:27',), ('ok',)]
[('Bob',), ('2020-02-16 05:22:25',), ('Not ok',)]
[('Brad',), ('2020-02-20 04:17:32',), ('ok',)]
[('Haruhi',), ('2020-02-16 05:22:10',), ('ok',)]

Upvotes: 0

Views: 21

Answers (1)

Isaiah
Isaiah

Reputation: 680

In general, to sort data, you use the ORDER BY clause followed by the colunns you wish to order by. By default, order by defaults to ascending order, but you can specifiy DESC after a column to indicate descending order.

For instance, given the following query:

SELECT chat_identifier, 
  datetime(date/1000000000 + 978307200,'unixepoch','localtime'), 
  case when [error]=0 then 'ok' when [error]=1 then 'Not ok' end 
FROM chat INNER JOIN message 
ON chat.ROWID = message.ROWID" 
ORDER BY chat_identifier

Should give something like this:

[('Alex',), ('2020-02-16 05:22:49',), ('ok',)]
[('Bob',), ('2020-02-16 05:22:25',), ('Not ok',)]
[('Brad',), ('2020-02-20 04:17:32',), ('ok',)]  
[('Grace',), ('2020-02-20 04:17:27',), ('ok',)]
[('Haruhi',), ('2020-02-16 05:22:10',), ('ok',)]

Since you are calculating the date field manually, you will also want to use a column alias. Thus, your query would look something like this:

SELECT chat_identifier, 
  datetime(date/1000000000 + 978307200,'unixepoch','localtime') AS my_date, 
  case when [error]=0 then 'ok' when [error]=1 then 'Not ok' end 
FROM chat INNER JOIN message 
ON chat.ROWID = message.ROWID" 
ORDER BY my_date

Upvotes: 2

Related Questions