MAX Values (Query)

Hi,

I have a problem to get the max values. I have table like this...

STATUS CUSTOMER NUMBER OTHER
0 000001 1 DATA1
0 000001 2 DATA2
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 1 DATA1
0 000003 2 DATA2

I need to get this

STATUS CUSTOMER NUMBER OTHER
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 2 DATA2

Please advice

Thanks

Try:

;with cte

as

(

select *, row_number() over(partition by customer order by number DESC) as rn

from dbo.t1

)

select *

from cte

where rn = 1;

AMB

|||

Here it is,

Code Snippet

Create Table #data (

[STATUS] int ,

[CUSTOMER] Varchar(100) ,

[NUMBER] int ,

[OTHER] Varchar(100)

);

Insert Into #data Values('0','000001','1','DATA1');

Insert Into #data Values('0','000001','2','DATA2');

Insert Into #data Values('0','000001','3','DATA3');

Insert Into #data Values('0','000002','1','DATA1');

Insert Into #data Values('0','000003','1','DATA1');

Insert Into #data Values('0','000003','2','DATA2');

go

select status,customer,max(number),max(Other) from #data

group by status,customer

|||
Ismael try this,

SELECT MainTable.*

FROM MainTable

JOIN (

SELECT CUSTOMER

, MAX(NUMBER) MaxNUMBER

FROM MainTable

GROUP BY CUSTOMER

) SQ

ON MainTable.CUSTOMER = SQ.CUSTOMER

AND MainTable.NUMBER = SQ.MaxNUMBER