CODEWITHSUNDEEP
sql-server
user1882705
user1882705

Reputation: 1081

select top 1 from a group by and order by in a query

I have a table with data like:

ID    Field_name   change_date  prev_value  current_value

1      USER_NAME    01/01/2013     test         test11
1      USER_NAME    04/02/2013     test11       test12
2      USER_GENDER  06/01/2013     M             F
2     USER_GENDER   02/01/2013     F             M

When i group by field_name and order by change_date i will get something like 

ID    Field_name   change_date  prev_value  current_value
2      USER_GENDER  06/01/2013     M             F
2     USER_GENDER   02/01/2013     F             M 
1      USER_NAME    04/02/2013     test11       test12
1      USER_NAME    01/01/2013     test         test11

Now i need to select top 1 row for each field_name. Please suggest

Thanks

EDIT:

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Field_name) 
                            from 
                            (
                                select Field_name,change_date,prev_value,current_value
                                from
                                (select *,
                                 row_number() over (partition by Field_name order by change_date desc) as 
                                ranker from tbl_User
                                )Z
                                where ranker = 1 and ID = '1' 
                            )
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')

Expected output:

ID     USER_GENDER  USER_NAME 
2         M            
1                     test11

Upvotes: 2

Views: 5877

Answers (1)

iruvar
iruvar

Reputation: 23364

Windowing functions should be able to do the trick with any recent version of SQL Server

select ID, Field_name,  change_date,  prev_value,  current_value
from
(select *,
 row_number() over (partition by Field_name order by change_date desc) as 
ranker from table
)Z
where ranker = 1

Upvotes: 6

Related Questions