Want data from table in SQL server

Question

I have table data like this

id id1  name
1  1    test1
1  1    test1
1  2    test2
2  1    test1
2  2    test2
3  1    test1
3  2    test2
3  2    test2

now from table i want the data as below like for id = 1 order by id1 asc the first name = test1 so i want the first two row

id id1  name
1  1    test1
1  1    test1

not third row

For id=2 order by id1 asc the first name = test1 so i want first row as test1 has assign only ones for id=2

id id1  name
2  1    test1

And for id=3 same as id=2

Please suggest me how can get the perticlur value for ID , because the scenerio is differnt for ID=1

Answer 1

Sounds to me like you just want select * from [tablename] where id1 = 1, but I might be wrong. I find the question a bit, well, vague...

Answer 2

Perhaps this:

select * from [table] where id = 1 order by id1

I think the point is that you can use one column in the where clause, and a different column in the order by clause. that's no problem.

But I'm not sure you could actually have the data table you describe, because the first two rows are identical (how can SQL tell them apart? Or more technically, there'd be a primary key violation)?

Answer 3

select * from [table] where id = 1 and name like "test1" order by id1

Answer 4

Use RANK() or DENSE_RANK() to get the first ranked rows, including duplicates, for each id.

select * from (
    select *, dense_rank() over (partition by [id] order by [id2]) as ranking
    from [table]
) as t
where ranking = 1