SQL Query to get top 1 values for each element in the list

I need help in writing a query which returns me all columns

select id, city, road1, road2, lat, long 
from tbltest 
group by id, city, lat, long. 

The final 开发者_如何学JAVAresult should have something like this

Thanks, Pawan

Although it is weird, that the table has duplicate id columns here is the query that would show the result you need:

select id, city, road1, road2, lat, long
from
(
    select *, row_number() over(partition by id, city, lat, long order by road1, road2) RowNumber
    from tbltest 
) tt
where RowNumber = 1

Using Common Table Expressions

;with tbl as
(
    select *, row_number() over(partition by id, city order by road1, road2) RowNumber
    from tbltest 
)

select * from tbl
where RowNumber = 1