CODEWITHSUNDEEP
sqlsql-server-2008t-sqlduplicates
MrProgram
MrProgram

Reputation: 5242

Can't get rid of duplicates

I have my query:

SELECT   t.nCitiesId
    ,t.tmDate
    ,t.strShortWeekdayName
    ,t.nIsHoliday
FROM    #temp AS t
WHERE   1 = 1
AND     t.nCitiesId = 104
AND     t.tmDate = '2014-06-08'
GROUP BY t.tmDate, t.nCitiesId, t.strShortWeekdayName, t.nIsHoliday

This generates following result:

nCitiesId         tmDate              strShortWeekdayName   nIsHoliday
104       2014-06-08 00:00:00.000            SUN                0
104       2014-06-08 00:00:00.000            SUN                1

My problem is when nCitiesId and tmDate is equal, I wan't the one with nIsHoliday = 0 gone.

How can I make this in a smooth way?

Upvotes: 0

Views: 42

Answers (2)

jarlh
jarlh

Reputation: 44696

Pick the t.nIsHoliday with highest value:

SELECT t.nCitiesId
      ,t.tmDate
      ,t.strShortWeekdayName
      ,max(t.nIsHoliday)
FROM   #temp AS t
WHERE  t.nCitiesId = 104
AND    t.tmDate = '2014-06-08'
GROUP BY t.nCitiesId, t.tmDate, t.strShortWeekdayName

Upvotes: 1

Jens
Jens

Reputation: 69440

Try using having clause:

SELECT   t.nCitiesId
    ,t.tmDate
    ,t.strShortWeekdayName
    ,t.nIsHoliday
FROM    #temp AS t
WHERE   1 = 1
AND     t.nCitiesId = 104
AND     t.tmDate = '2014-06-08'
GROUP BY t.tmDate, t.nCitiesId, t.strShortWeekdayName, t.nIsHoliday
having max(t.nIsHoliday)

Upvotes: 1

Related Questions