Sampath Sampath - 5 months ago 12
SQL Question

Get only 1 occurrence for the duplicate records

select DISTINCT a.Schooldistricttown, a.Schooldistrictnum
from [Legacy].[dbo].[MyTables] as a


It returns :

enter image description here

Can you tell me how to get only one occurrence of
a.Schooldistricttown
?

I have tried with
DISTINCT
and
GROUP BY
. But it's not working.

Note : I need to show both columns also.

Answer

Two options, if it doesn't matter which value you get in Schooldistrictnum then group by with MAX()/MIN() will solve this:

SELECT a.Schooldistricttown,MAX(a.Schooldistrictnum)
from [Legacy].[dbo].[MyTables] a
GROUP BY a.Schooldistricttown

If you do care, use ROW_NUMBER() :

SELECT s.Schooldistricttown,s.Schooldistrictnum
FROM (
    SELECT a.Schooldistricttown,a.Schooldistrictnum,
           ROW_NUMBER() OVER(PARTITION BY a.Schooldistricttown ORDER BY a.<ORDER_COLUMN>) as rnk
    from [Legacy].[dbo].[MyTables]  a) s
WHERE s.rnk = 1

You need to replace <ORDER_COLUMN> with the actual column that you decide which value you want by it

Comments