Click here to Skip to main content
15,074,377 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi,

I'm trying to write a query which will distinguish records from this table

No Date Flag
1 2016-10-31 Y
1 2016-11-30 Y
2 2014-10-31 Y
1 2015-10-31 Y
2 2013-10-31 Y
1 2012-10-31 Y
2 2011-10-31 Y
3 2016-08-31 Y
3 2016-01-31 Y
1 2016-03-31 Y
2 2015-10-31 Y
3 2014-10-31 Y

to this

No Date Flag
1 2016-11-30 Y
2 2015-10-31 Y
3 2016-08-31 Y

The only problem here is no primary key exists; should I fetch this record in a temp table and then try? Please suggest.

Thanks in advance.

What I have tried:

the above records I've shifted into a temp table; but no idea after that.
Posted
Updated 15-Feb-17 22:39pm
v2
Comments
Karthik_Mahalingam 16-Feb-17 4:38am
   
not clear

1 solution

Hi,

Try using Row_number here.

Like this :

SQL
;WITH CTE AS (
SELECT [NO],[DATE],FLAG
 ,ROW_NUMBER() OVER(PARTITION BY [NO] ORDER BY [DATE] DESC) AS ROW_NUM
FROM TABLE_NAME
)

SELECT [NO],[DATE],FLAG FROM CTE WHERE ROW_NUM = 1;
   
Comments
sahabiswarup 16-Feb-17 5:50am
   
Thanks for your valuable comment.
[no name] 16-Feb-17 6:01am
   
Happy to help

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900