Removing duplicate records.

Question

Hi,

I am using the following query

select  SS.sightseeingId, SS.SightseeingName, SS.displayPrice,  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

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 this SST.fromDate. How do i correct my query??

Any help please....

Answer 1

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

Answer 2

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

Answer 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.

Answer 4

Wouldn't it enough to just exclude the

SST.fromDate

from the select?