CODEWITHSUNDEEP
sql-server
user3191485
user3191485

Reputation: 1

How to write a SQL Server query to get a group

I have a table rsPhotos that has 4 columns: picID, huntID, photo, D_hunt.

The photo column has many different jpgs, the huntID column is relative to the photos but there are many different huntID's.

I just want ONE photo for each huntID.

I have tried the min function with groups but get errors mixing text with nums. Tried TOP, doesn't work for me.

Can someone please figure this out? Thank you

Sample Data

picID    huntID     photo       D_hunt  
-----------------------------------------
72096    20785   DSC_0605.jpg  2015-04-04  
72098    20785   DSC_0586.jpg  2015-04-04  
72100    20775   DSC_1555.jpg  2015-01-02  
72098    20745   DSC_5828.jpg  2014-12-15  
72096    20745   DSC_4632.jpg  2014-12-15

Results should be something like this...

huntID       photo  
--------------------------
20785       DSC_0605.jpg  
20775       DSC_1555.jpg  
20745       DSC_5828.jpg

Upvotes: 0

Views: 28

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do this using the window function, ROW_NUMBER:

SELECT
    huntID,
    photo
FROM(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY huntID ORDER BY D_hunt DESC, picID DESC)
    FROM rsPhotos
)t
WHERE RN = 1
ORDER BY huntID DESC

Upvotes: 1

Related Questions