CODEWITHSUNDEEP
sqlsql-server
AirWolf
AirWolf

Reputation: 597

SQL Server 2012 Group only by one column

I need to group only by one column

SQL Server 2012 query:

SELECT OrderID,Status,DateEdited 
FROM orders 
WHERE Status='Pending' 
GROUP BY OrderID,Status,DateEdited

and the result is:

enter image description here

As you can see there are duplicate OrderId column values.

Works but it groups by OrderId,Status,DateEdit but what I need is that the OrderId would be unique in the results can I have something like:

SELECT OrderID,Status,DateEdited 
FROM orders 
WHERE Status='Pending' 
GROUP BY OrderID

Upvotes: 0

Views: 4866

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79949

You have to use an aggregate function for status and DateEdited, to get distinct valeus for each grouped ORDERID. You can also use the ranking functions to do so like this:

WITH CTE
AS
(
   SELECT 
     OrderID,
     Status,
     DateEdited,
     ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY DateEdited DESC) rownum
   FROM orders 
   WHERE Status='Pending'
)
SELECT 
  OrderID,
  Status,
  DateEdited
FROM CTE
WHERE rownum = 1;

This will give you distinct ORDERIDs. But which status and DateEdited to return for each grouped ORDERID?

Upvotes: 4

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

Then you can do like this,

SELECT OrderID,'Pending' as Status,max(DateEdited) 
FROM orders 
WHERE Status='Pending' 
GROUP BY OrderID

If you dont want to loose any record then you can go for GROUP_CONCAT()

SELECT OrderID,'Pending' as Status,GROUP_CONCAT(DateEdited) 
FROM orders 
WHERE Status='Pending' 
GROUP BY OrderID

Note: Am not sure whether you GROUP_CONCAT in sqlserver Incase it's not there go for a function like that. :)

Upvotes: 1

Related Questions