Removing duplicate records

问题:

I am using the following query

SELECT SS.sightseeingId AS 'sID'
     , SS.SightseeingName
     , SS.displayPrice AS 'Price'
     , SST.fromDate 
FROM tblSightseeings SS INNER JOIN 
     tblSightseeingTours SST ON SS.sightseeingId =  SST.sightseeingId
WHERE SS.isActive = 1 AND SS.isDisplayOnMainPage = 1 

and getting result like this

sID | SightseeingName                        | Price | fromDate 
------------------------------------------------------------------------------
  2 | Dinner Cruise Bateaux London (Premier) |    40 | 2009-04-01 00:00:00.000
  2 | Dinner Cruise Bateaux London (Premier) |    40 | 2009-12-29 00:00:00.000
 30 | Jack The Ripper, Ghosts and Sinister   |  35.1 | 2009-04-01 00:00:00.000
 30 | Jack The Ripper, Ghosts and Sinister   |  35.1 | 2009-10-01 00:00:00.000
 40 | Grand Tour of London                   |     0 | 2009-05-01 00:00:00.000
 40 | Grand Tour of London                   |     0 | 2010-05-01 00:00:00.000
 87 | Warwick, Stratford, Oxford and The     |    25 | 2009-04-01 00:00:00.000
 87 | Warwick, Stratford, Oxford and The     |    25 | 2009-11-01 00:00:00.000

I want to display the unique records 2 one time 30 one time 40 one time. The duplicate records are due to SST.fromDate.

How do I correct my query??

回答1:

You can try next query:

select  SS.sightseeingId, SS.SightseeingName, SS.displayPrice,  MAX(SST.fromDate)
from      tblSightseeings SS inner join 
              tblSightseeingTours SST on SS.sightseeingId =  SST.sightseeingId
where    SS.isActive = 1 and SS.isDisplayOnMainPage = 1
GROUP by SS.sightseeingId, SS.SightseeingName, SS.displayPrice

回答2:

Well, the records aren't actually duplicated, because the dates are different. You could do something like:

select  SS.sightseeingId, SS.SightseeingName, SS.displayPrice,  MIN(SST.fromDate) AS FromDate 
from      tblSightseeings SS inner join 
              tblSightseeingTours SST on SS.sightseeingId =  SST.sightseeingId
where    SS.isActive = 1 and SS.isDisplayOnMainPage = 1
GROUP BY ss.sightseeingid, ss.sightseeingname, ss.displayprice

回答3:

try this (example will return the highest date in the group):

SELECT   SS.sightseeingId,
         SS.SightseeingName,
         SS.displayPrice,
         MAX(SST.fromDate) 
FROM     tblSightseeings SS
INNER JOIN  tblSightseeingTours SST 
         ON SS.sightseeingId = SST.sightseeingId
WHERE    SS.isActive = 1 and SS.isDisplayOnMainPage = 1
GROUP BY SS.sightseeingId,
         SS.SightseeingName,
         SS.displayPrice

Depending on what date you want to show you can select the highest using MAX or the lowest using MIN. If you have other criteria you may need to do a subquery.

回答4:

Wouldn't it enough to just exclude the

SST.fromDate

from the select?