SELECT DISTINCT on one field, but get other fields

Hi,

You can do using group by

SELECT     SupServID, Title, EndDate, max(HotelID)HotelId, Quality, SortQuality
FROM         qryPrices
WHERE     (SupServID = 2193) AND (GETDATE() < EndDate)
group by SupServID, Title, EndDate, Quality, SortQuality
ORDER BY EndDate, SortQuality DESC

Select allOpen in new window

SELECT     distinct SupServID, Title, EndDate, HotelID, Quality, SortQuality
FROM         qryPrices
WHERE     (SupServID = 2193) AND (GETDATE() < EndDate)
ORDER BY EndDate, SortQuality DESC

can you provide sample data and expected output?

Thanks for the very fast answers.

Brichsoft: thanks, I tried this on one and it seemed to work. I need to check on others but I will only be back in the office in a couple of hours.

Murali1984: thanks, but I think this seeks distinct values for all 6 fields SupServID, Title, EndDate, HotelID, Quality, SortQuality and this won't work.

sdstuber and angelIIII: your answers just arrived as I write this, again thanks, need to check when in the office.

In that case you need mention the distinct for each field.... just try..

I have tested the 2 solutions for a range of situations. Both Brichsoft's and Murali's proposals work where FromDate does not change. But when it changes, there remain duplicates of HotelID.

I attach sample data for this, and the output desired, that has no duplication in the HotelID column. Hope the text doc shows columns OK.

Can proposals be modified for this?

angellll: the article is highly complex. I am hoping somebody can help much faster than I can try and understand this. EE-EXAMPLE.txt

SELECT supservid,
       fromdate,
       title,
       enddate,
       hotelid,
       quality,
       sortquality
  FROM (SELECT p.*, ROW_NUMBER() OVER (PARTITION BY hotelid ORDER BY fromdate DESC) rn
          FROM qryprices  as p) as p
 WHERE rn = 1


note, this is covered in angeliii's article, I do recommend it

Thanks sdstuber, this looks like it is getting close. I tried it in sql2000, as you can see in the attachment it added an extra row from an earlier date, because the SQL code no longer has the date condition which only considers values that are still valid (ie EndDate > today's date).

How to add this condition in, since the SupServID condition I added in becomes an order by (oddly), not WHERE SupServID = 3004 (as I would have expected).

Here was the amended version I tried:-
SELECT DISTINCT SupServID, FromDate, Title, EndDate, HotelID, Quality, SortQuality
FROM         qryWebTourPrices po
WHERE     (FromDate =
                          (SELECT     TOP 1 pi.fromdate
                            FROM          qryWebTourPrices pi
                            WHERE      po.hotelid = pi.hotelid
                            ORDER BY pi.fromdate DESC)) AND (SupServID = 3004)
EE-EXAMPLE2.txt

I have adjusted for this problem, and because I noticed fromdate should be enddate. The latter is important because we have many cases where fromdate is null but there is an enddate.

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 (SupServID = 2191) AND (GETDATE() < EndDate)
                            ORDER BY pi.SortQuality DESC))

The only apprent problem with this is that it does not sort by SortQuality, despite the last line. I tried also ORDER BY po.SortQuality DESC)) but made no difference.

We need this sorting so that the results show on the web page with quality categores sorted correctly from Deluxe down to Standard.

you need to put the order by of the final results on the outer query.


the order by of the inner query is only to find the end date

Thanks, that worked. Here is final query:-
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 (SupServID = 2191) AND (GETDATE() < EndDate)
                            ORDER BY pi.fromdate DESC))
ORDER BY SortQuality DESC

Thanks for excellent help