In a recent question, http://www.experts-exchange.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
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!