In a recent question,, a solution was provided that worked for most cases. But on further study I find that one simple case still gives duplicate answers.

This was the problem for MS SQL 2000:-

In this query, I want the field 'HotelID' to be distinct, but the other fields to be normal. How to do?
SELECT     SupServID, Title, EndDate, HotelID, Quality, SortQuality
FROM         qryPrices
WHERE     (SupServID = 2193) AND (GETDATE() < EndDate)
ORDER BY EndDate, SortQuality DESC

The solution that was accepted in the previous question is given is below, but in the attached file is a case where distinct values for the field Quality generate duplicates for HotelID. The desired output would be that ONLY distinct values of HotelID appear.  The first line of the solution below looks for distinct values of all.

SELECT DISTINCT SupServID, FromDate, Title, EndDate, HotelID, Quality, SortQuality
FROM         qryWebTourPrices po
WHERE     (EndDate =
                          (SELECT     TOP 1 pi.enddate
                            FROM          qryWebTourPrices pi
                            WHERE      po.hotelid = pi.hotelid AND (po.SupServID = 2403) AND (GETDATE() < EndDate)
                            ORDER BY pi.fromdate DESC))

Thanks for input on this!

  • 427 bytes
  • Example of duplication in HotelID

    asked 12/13/2011 09:35

    pld51's gravatar image

    pld51 ♦♦

    4 Answers:
    please check again this article :

    the TOP 1 will not solve the issue if you have several rows with the same enddate.
    check out the ROW_NUMBER() tricks ...


    angelIII's gravatar image


    try this

    SELECT   SupServID
                ,MAX(FromDate) AS FromDate
                ,MAX(Title) AS Title             
                ,MAX(EndDate) AS EndDate
                ,MAX(Quality) AS Quality
                ,MAX(SortQuality) AS SortQuality
    FROM dbo.QryPrices(NOLOCK)
    GROUP BY HotelID ,SupServID

    answered 2011-12-14 at 06:24:29

    penyCuicas's gravatar image


    Thanks for these.

    angellll: thanks again, I checked again, the ROW_NUMBER tricks unfortunately only applies to MS SQL 2005, not 2000.

    penyCuicas: I've tested this and it works on the sample given and a few other test cases. It looks promising, please give me a day to test exhaustively and ensure it will works always. Thanks!

    answered 2011-12-14 at 08:40:49

    pld51's gravatar image


    Thanks for patience. This worked perfectly on all the tests we ran. It's a solution I would never ever have thought of! Thanks for helping.

    answered 2011-12-14 at 11:43:23

    pld51's gravatar image


    Your answer
    [hide preview]

    Follow this question

    By Email:

    Once you sign in you will be able to subscribe for any updates here

    By RSS:


    Answers and Comments



    Asked: 12/13/2011 09:35

    Seen: 396 times

    Last updated: 12/16/2011 09:44