CODEWITHSUNDEEP
sqlsql-server
Snake Eyes
Snake Eyes

Reputation: 16754

How to order by a column (which match a criteria) in SQL?

I have two different columns as DateTime type : CREATION_DATE and UPDATE_DATE

I want to create a SELECT (some fields) statement which order by the column (UPDATE_DATE or CREATION_DATE not both) where UPDATE_DATE > CREATION_DATE and UPDATE_DATE must be NOT NULL

Why ? Any items will be create in DB and CREATION_DATE will be filled automatically but not UPDATE_DATE column.

When somebody modify an existing item then the UPDATE_DATE will be filled/updated at every modification and will be listed on UserInterface by UPDATE_DATE (ascendent).

How to do that ?

Sorry if my question is bad ...

Upvotes: 1

Views: 135

Answers (4)

Diego
Diego

Reputation: 36126

another option is to add a CASE column and order by it. I like this solution better because it doesn't use sub-queries 

select 
CASE
     WHEN UPDATE_DATE > CREATION_DATE THEN UPDATE_DATE 
     else CREATION_DATE 
END as MyOrderDate
, *
from fund_datasource_feed
order by MyOrderDate

Upvotes: 0

Arion
Arion

Reputation: 31239

Something like this:

DECLARE @tbl TABLE(UPDATE_DATE DATETIME,CREATION_DATE DATETIME)
INSERT INTO @tbl
SELECT '2011-01-01','2011-02-01'
UNION ALL
SELECT '2012-01-01','2011-12-10'

SELECT
    *
FROM
    @tbl AS tbl
ORDER BY
    (
    CASE WHEN tbl.UPDATE_DATE>tbl.CREATION_DATE
        THEN tbl.UPDATE_DATE
        ELSE tbl.CREATION_DATE
    END) ASC

Upvotes: 3

Michał Powaga
Michał Powaga

Reputation: 23173

Use isnull, if UPDATE_DATE is null it uses CREATION_DATE to order rows.

select * 
from table
order by isnull(UPDATE_DATE, CREATION_DATE) asc

Read more about isnull on MSDN.

coalesce is an alternative and it's going to work in most RDBMS (afaik).

select * 
from table
order by coalesce(UPDATE_DATE, CREATION_DATE) asc

Upvotes: 1

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174289

select
    *
from
(
    SELECT
        your_columns,
        ISNULL(UPDATE_DATE, CREATION_DATE) as sort_date
    FROM
        ....
)
order by
    sort_date

This selects the UPDATE_DATE column as sort_date. However, because of the ISNULL function, the column CREATION_DATE is used instead if UPDATE_DATE is NULL.

Upvotes: 1

Related Questions