Clicky

In a recent question, http://www.qa.downappz.com/Microsoft/Development/MS-SQL-Server/Q_27489223.html, 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))
ORDER BY SortQuality DESC

Thanks for input on this!

 
EE-EXAMPLE3.txt
  • 427 bytes
  • Example of duplication in HotelID
EE-EXAMPLE3.txt

    asked 12/13/2011 09:35

    pld51's gravatar image

    pld51 ♦♦


    4 Answers:
    please check again this article :
    http://www.qa.downappz.com/A_3203.html

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

    answered

    angelIII's gravatar image

    angelIII

    try this

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

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

    penyCuicas's gravatar image

    penyCuicas

    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!
    link

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

    pld51's gravatar image

    pld51

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

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

    pld51's gravatar image

    pld51

    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

    Answers and Comments

    Tags:

    ×3
    ×5

    Asked: 12/13/2011 09:35

    Seen: 374 times

    Last updated: 12/16/2011 09:44