CODEWITHSUNDEEP
sqlsql-server
user3262364
user3262364

Reputation: 371

Distinct keyword not working while selecting multiple columns

I have a query like this :

select distinct po_no, rescan_status 
from T_PoHeader 
order by po_no

My output is like this:

po_no   rescan_status
---------------------
P-01    True
P-02    True
p-03    False
p-04    False
p-04    True

Here my po_no is showing duplicates if I use distinct keyword. I want to only show distinct po_no.

Upvotes: 0

Views: 268

Answers (3)

user7715598
user7715598

Reputation: 

;with cte(po_no,rescan_status)
AS
(
SELECT 'P-01','True'  UNION ALL
SELECT 'P-02','True'  UNION ALL
SELECT 'p-03','False' UNION ALL
SELECT 'p-04','False' UNION ALL
SELECT 'p-04','True'
)
SELECT po_no,rescan_status From
(
select po_no,rescan_status ,ROW_NUMBER()OVER(Partition By po_no order by po_no) Seq from cte
)Dt
where Dt.Seq=1

Upvotes: 0

luly
luly

Reputation: 614

The distinct keyword works over all the columns you select. Being that you selected 'rescan_status' which has one row true and one row false for the same 'po_no' there are two 'distinct' combinations.

Remove 'rescan_status' to get only Distinct 'po_no'.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270361

Your results look fine to me. I don't see any duplicate rows.

Speculation might be that you want po_no to be unique on each row. If so, use aggregation and an aggregation function:

select po_no, max(rescan_status) as rescan_status
from T_PoHeader 
group by po_no
order by po_no;

Upvotes: 1

Related Questions