CODEWITHSUNDEEP
sql-server-2008sql-order-bydistinct
sp_m
sp_m

Reputation: 2705

Get unique records using order by in sql server 2008

ID    OrderId  status
    1  12       1
    2  13       1
    3  14       2
    4  12       3
    5  15       1
    6  14       1

hi all,

trying to build sql query for table like above to get result like below :

   OrderId  status
         12       3
         13       1
         14       1
         15       1

I want unique orderid with latest status code

I have tried this query 

Select Distinct OrderID 
from OrdersStatusHistory 
order by Status desc

getting records but with repeating orderid

Select OrderID, Status 
from Orders_Status_History 
order by Status desc

But I'm getting an error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Upvotes: 2

Views: 5310

Answers (4)

VIPUL PARMAR
VIPUL PARMAR

Reputation: 292

You have to try this:

Select DISTINCT OrderID, Status from Orders_Status_History

Upvotes: 0

Sunny
Sunny

Reputation: 99

Try this query.

Select orderid
,status 
from orders_status_history
where rowid in (Select max(rowid) 
from orders_status_history
group by orderid) order by order_id

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460360

Is your desired result wrong? 14 occurs twice, but the latest status is 2.

WITH CTE AS
(
   SELECT RN = ROW_NUMBER()OVER(PARTITION BY OrderId ORDER BY ID DESC) , OrderId, status
   FROM dbo.Orders
)
SELECT  OrderId, status FROM CTE
WHERE RN = 1

DEMO

ORDERID STATUS
12          3
13          1
14          1  
15          1

Upvotes: 1

valex
valex

Reputation: 24144

select OrdersStatusHistory.* from OrdersStatusHistory
JOIN
 (select OrderId, max(id) maxId 
      from OrdersStatusHistory 
      group by OrderId) t1
 on OrdersStatusHistory.Id=t1.maxId
 order by OrdersStatusHistory.OrderId

SQLFiddle demo

or if you need juts maximum status for the OrdeId

select OrderId,max(status)
from OrdersStatusHistory
group by OrderID
order by OrderId

Upvotes: 2

Related Questions