Order by date is not working properly

Question

I am using

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

When I try to sort order by date it is not giving the correct order

DECLARE @dd table (ID int,rate numeric(5,2), orderdate smalldatetime)
INSERT INTO @dd
SELECT 1,10,'2013-03-05 10:11:00'
UNION ALL 
SELECT 2,25,'2013-03-05 10:11:00' 
UNION ALL
SELECT 3,30,'2013-03-05 10:10:00'
UNION ALL
SELECT 4,50,'2013-03-05 10:11:00'
UNION ALL
SELECT 5,60,'2013-03-05 10:15:00'

SELECT top 2 * FROM @dd Order by orderdate desc

Id 1,2,4 has same orderdate value

It is displaying the result in this order id 5 and 2 which is not correct. It should actually display orderid 5,4.

No. The order is correct. Ordering within a group of same values is undefined. — Mitch Wheat
i want the result based on orderdate sorting...if the orderdate is same then get the id which is last inserted..here 4 is inserted after 1,2 — SQL006

Mitch Wheat Mitch Wheat · Accepted Answer · 2013-03-05T07:11:16

In response to your comments, you also need to order by the ID column:

SELECT top 2 * 
FROM @dd 
Order by orderdate desc, ID desc

Result:

ID  rate    orderdate
5   60.00   2013-03-05 10:15:00
4   50.00   2013-03-05 10:11:00

Order by date is not working properly

1 Answers