CODEWITHSUNDEEP
sqlsql-server
Sampath
Sampath

Reputation: 65860

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.

Upvotes: 1

Views: 71

Answers (2)

neer
neer

Reputation: 4082

If you want all Schooldistrictnum, use this

  SELECT DISTINCT 
    a.Schooldistricttown,
    (
        SELECT DISTINCT 
            ina.Schooldistrictnum + ', ' AS [text()]
        FROM 
            [Legacy].[dbo].[MyTables] as ina
        WHERE
            ina.Schooldistricttown =  a.Schooldistricttown
        FOR XML PATH ('')
     ) AS Schooldistrictnum 
   FROM [Legacy].[dbo].[MyTables] as a

Upvotes: 0

sagi
sagi

Reputation: 40471

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

Upvotes: 1

Related Questions