Karim Benfaida Karim Benfaida - 7 months ago 10
Vb.net Question

how to select the first record in every liste with a condition in sqlserver

my table

How can I select the first record in every list that has the column

siege = false
, order by age?

In this example I'd like to have :

enter image description here

Answer

If you want select the first record in every list that has the column siege = false order by age, use this query :

select elu_liste,elu_ordre
from (
select elu_liste,elu_ordre,row_number() over(partition by elu_liste order by age) as roworder
from MyTableName where siege = 'False'
) temp
where roworder = 1

But if you want that result :

 elu_liste | elu_ordre 
         1 |         6 
         2 |         3 
         3 |         3

You should use :

    select elu_liste,elu_ordre
    from (
    select elu_liste,elu_ordre,row_number() over(partition by 
           elu_liste order by elu_liste,elu_ordre) as roworder
    from MyTableName where siege = 'False'
    ) temp
    where roworder = 1