Clever Geek Handbook

SQL for row selection with add_date and updated_date

I am trying to select the order of rows using, added_date

or update a row, then order by updated_date

.

Updated date by default Null

.

For the above table, the date specified in ( dd-mm-yy

).

In the table below, the first row is added on Feb 2 and the second is added on 1 feb, so using

select * from tablename order by added_date desc;

I get the last line, but if any line is updated, that updated line becomes the last line.

For the second line, the line is added to the 1st fb, but it is updated to the 3rd feb, so it becomes the last line.

How can I get the entry with the added and updated date?

    id   title    name   added_date   updated_date
     1   asd      rff     02-02-2013    NULL
     2   fsfs     rwr     01-02-2013    03-02-2012
     3   ddd      wwrr    30-01-2013    04-02-2013

Edit: using MYSQL

+3


source to share


3 answers


You didn't mention which RDBMS you are using. For Oracle, you can try this:

SELECT * FROM tablename ORDER BY NVL(updated_date,added_date) DESC;

SQL Server:



SELECT * FROM tablename ORDER BY ISNULL(updated_date,added_date) DESC

MySQL:

select * from tablename order by ifnull(updated_date,added_date) desc;
+4


source


try using COALESCE 

SELECT  * 
FROM    tablename 
ORDER   BY COALESCE(updated_date, added_date) DESC


From Docs:

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

+2


source 


ORDER BY 
    CASE updated_date 
        WHEN NULL THEN added_date 
        ELSE updated_date
         END 
    desc
+2


source






More articles:


All Articles