MS SQL Group by / Distinct

[dan_ord]

Hi all,

I'm currently working on a holidays database where i need to retrieve the latest offers and display them. I've managed to get that working fine.

However what i want to be able to do is kinda like a SELECT DISTINCT to only show 1 destination, and have the rest as different destinatations, for example:

Turkey, Greece, USA, Spain, Maldives

With my current query it will just pull destinations that could be the same or different, for example:

Turkey, Turkey, Turkey, Turkey, Spain.

So in a nut shell what i'm wanting to do is, if for example turkey is one of the results returned, don't show any more turkey results.

Here's a copy of what my query looks like atm:

SELECT     TOP (5) Offers.OfferId, Offers.SID, Offers.GroupId, Offers.QueryId, Offers.DepartureAirportCode, Offers.DepartureAirportName, Offers.HolidayRegion, 
                      Offers.HolidayResort, Offers.HolidayResortId, Offers.Duration, Offers.RoomType, Offers.Board, Offers.PricePerAdult, Offers.PricePerChild, 
                      Offers.QuoteBasis, Offers.Currency, Offers.DepartureDate, Offers.DeepLink, Offers.Adults, Offers.Children, Offers.HotelName, Offers.StarRating, 
                      Offers.HotelAddress, Offers.HotelPostcode, Offers.HotelTelephone, Offers.Url, Offers.BrochureInfo, Offers.OutboundDepartureAirportCode, 
                      Offers.OutboundDepartureAirportName, Offers.OutboundDepartureDateTime, Offers.OutboundArrivalDateTime, Offers.OutboundArrivalAirportCode, 
                      Offers.OutboundArrivalAirportName, Offers.OutboundFlightNumber, Offers.InboundDepartureAirportName, Offers.InboundDepartureAirportCode, 
                      Offers.InboundDepartureDateTime, Offers.InboundArrivalDateTime, Offers.InboundArrivalAirportCode, Offers.InboundArrivalAirportName, 
                      Offers.InboundFlightNumber, Offers.DateAdded, ArrivalPointGroup.Name AS CountryName
FROM         ArrivalPoint_ArrivalPointGroup INNER JOIN
                      ArrivalPoint ON ArrivalPoint_ArrivalPointGroup.ArrivalPointId = ArrivalPoint.Id INNER JOIN
                      ArrivalPointGroup ON ArrivalPoint_ArrivalPointGroup.ArrivalPointGroupId = ArrivalPointGroup.Id INNER JOIN
                      Offers ON ArrivalPoint.Code = Offers.OutboundArrivalAirportCode
WHERE      (Offers.Board = 'HB')    AND     (Offers.DepartureDate LIKE '2010-07-%')

I usually work with MySQL, however the MS SQL is a bit different and im not sure how to acheive what im after.

Any help or suggestions would be greatly appreciated!

Dan

[dan_ord]

Posted this a while back, still in need of some help if anyone can point me in the right direction

[Brillig]

A couple ways to do this, but best done with a subquery.

So the subquery would be something like (assuming offerIds are sequential and the higher is the most recent).

(select ArrivalPointGroup.Name AS CountryName, MAX(offerID)
from [whatevertable]
group by ArrivalPointGroup)

This would give you a result set of the most recent addition for each country. Then you can join to this.

Does that help or do you need more detail?