CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Amjath
Amjath

Reputation: 55

Sql Server - Get first record from a group

I have a table like this

http://i.imgur.com/2uLQBT0.jpg

I tried below code to get first record from "AdID" group 

;with cte as
(
  select
    ADID,
    PhotoName,
    PhotoPath,
    rank() over(partition by AdID order by PhotoName) as rn
  from AdPhoto
)  
select ADID,
    PhotoName,
    PhotoPath
from cte
where rn = 1

But it turns out the output like this 

AD1001  temptemp_1.jpeg ~/UserUploads/temptemp/
AD1002  temptemp_10.jpg ~/UserUploads/temptemp/

I want a output like this

AD1001  temptemp_1.jpeg ~/UserUploads/temptemp/
AD1002  temptemp_5.jpg  ~/UserUploads/temptemp/

Please help

Upvotes: 1

Views: 235

Answers (2)

dean
dean

Reputation: 10098

This will help for this particular set of data:

;with cte as
(
  select
    ADID,
    PhotoName,
    PhotoPath,
    rank() over(partition by AdID order by len(PhotoName) asc, PhotoName) as rn
  from AdPhoto
)  
select ADID,
    PhotoName,
    PhotoPath
from cte
where rn = 1

But I'd suggest to either change naming scheme for the photoname, or find some other ordering algoritm.

Upvotes: 0

TechDo
TechDo

Reputation: 18629

Please try order by ID instead of order by PhotoName:

;with cte as
(
  select
    ADID,
    PhotoName,
    PhotoPath,
    rank() over(partition by AdID order by ID) as rn
  from AdPhoto
)  
select ADID,
    PhotoName,
    PhotoPath
from cte
where rn = 1

Upvotes: 2

Related Questions