CODEWITHSUNDEEP
c#sqllinqsorting
Babak Saeedi
Babak Saeedi

Reputation: 157

Ascending sort date

i want sort date, and use this code:

SELECT     TID, TName, TOrder
FROM         tblOrder
ORDER BY TOrder

Result:

1 maikel null

5 emil null

4 kabol 1

3 samin 2

2 mihad 3

but i want this result:

4 kabol 1

3 samin 2

2 mihad 3

1 maikel null

5 emil null

please help me for Ascending sort.

Upvotes: 2

Views: 206

Answers (5)

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9158

Have a look at this query and Fiddle

SELECT TID, TName, TOrder INTO #TempTab
FROM         tblOrder
WHERE TOrder IS NOT NULL
ORDER BY TOrder
SELECT * FROM #TempTab 
UNION ALL
SELECT     TID, TName, TOrder
FROM         tblOrder
WHERE TOrder IS NULL;

Upvotes: 0

mvp
mvp

Reputation: 116197

This is easiest way for SQL Server:

SELECT TID, TName, TOrder
FROM   tblOrder
ORDER BY isnumeric(TOrder) DESC, TOrder

For other databases like MySQL and PostgreSQL, there is simpler solution:

SELECT TID, TName, TOrder
FROM   tblOrder
ORDER BY TOrder IS NULL, TOrder

For Oracle and PostgreSQL, this works and will take advantage of possible index on TOrder:

SELECT TID, TName, TOrder
FROM   tblOrder
ORDER BY TOrder ASC NULLS LAST

Upvotes: 0

John Woo
John Woo

Reputation: 263733

SELECT  TID, TName, TOrder
FROM    tblOrder
ORDER   BY  CASE WHEN TORDER IS NULL THEN 1 ELSE 0 END,
            TOrder

Linq:

var x = from a in tblOrder
        orderby (a.TORDER == null ? 1 : 0), a.TORDER 
        select a

Upvotes: 5

mgibsonbr
mgibsonbr

Reputation: 22007

You can use COALESCE:

SELECT     TID, TName, TOrder
FROM         tblOrder
ORDER BY COALESCE(TOrder,'9999-12-31 23:59:59.997')

Upvotes: 0

Daniel Lundmark
Daniel Lundmark

Reputation: 2390

Try to use "ORDER BY TOrder ASC".

http://www.w3schools.com/sql/sql_orderby.asp

Upvotes: 0

Related Questions