How to SQL distinct with ORDER BY

Question

How do i bring the rows distinctly by OrderID and those who has ProductID 1. if there is no 1 then bring 2

I've tried:

Select DISTINCT OrderDetailID,OrderID,ProductID,Quantity from Products 
ORDER BY OrderID,ProductID

OrderDetailID	OrderID	ProductID	Quantity
1	10248	1	12
2	10248	2	10
3	10249	1	5
4	10250	1	9
5	10250	2	40
6	10251	2	10
7	10252	1	35
8	10252	2	15
9	10253	2	6
10	10254	2	15

Output should be like this:

OrderDetailID	OrderID	ProductID	Quantity
1	10248	1	12
3	10249	1	5
4	10250	1	9
6	10251	2	10
7	10252	1	35
9	10253	2	6
10	10254	2	15

See this link please : https://stackoverflow.com/questions/5391564/how-to-use-distinct-and-order-by-in-same-select-statement — Keyvan Soleimani, Jan 09 '22 at 11:11
I can see every single answer on your questions has not gotten your upvote or approval. This makes questions like this one pop up at the homepage of Stackoverflow because the idea is that there is no answer yet. Could you accept (all other questions you have answers for too) if the answer is useful please? This also motivates others to write answers. — Randy, Apr 04 '22 at 13:45

Stu · Answer 1 · 2022-01-09T16:48:24.907

1

You can use row_number to asign ordering to each group of ProductId

with o as (
select *, 
    Row_Number() over(partition by orderid order by productid) rn
from t
)
select OrderDetailID, OrderID, ProductID, Quantity 
from o
where rn=1

edited Jan 09 '22 at 16:48

answered Jan 09 '22 at 11:13

Stu

30,392
6
14
33

score 1 · Answer 2 · answered Jan 09 '22 at 11:13

1

Use row_number window function

select OrderDetailID,OrderID,ProductID,Quantity
from
  select *,row_number()over(partition by OrderID order by OrderDetailID) as seq
  from ) t
where seq = 1

answered Jan 09 '22 at 11:13

Meysam Asadi

6,438
3
7
17

How to SQL distinct with ORDER BY

2 Answers2