CODEWITHSUNDEEP
sql-server
NewbieCoder
NewbieCoder

Reputation: 706

SQL Server : to get Max date value

I have the following data in the table:

enter image description here

I have tried using the below query to get the latest CMBR_MBST with its respective CMBR_CCMP

SELECT CMBR_CCMP, MAX(CMBR_MBST) AS CMBR_MBST
FROM CMBR 
GROUP BY CMBR_CCMP

enter image description here

The result is close to what I need. I just need the extra 2 columns, CMBR_MMBR & CMBR_MBED to be displayed.

For example:

enter image description here

Can someone please help me with the query? Your help would be very much appreciated. Thanks

Upvotes: 0

Views: 65

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

Here is one way using ROW_NUMBER

SELECT TOP 1 with ties *
FROM CMBR 
Order by row_number()over(parition by CMBR_CCMP order by CMBR_MBST desc)

Another way (commonly used technique)

select * from
(
SELECT *,row_number()over(parition by CMBR_CCMP order by CMBR_MBST desc) Rn
FROM CMBR
) A
Where Rn =1

Upvotes: 1

Related Questions