Retrieve Repeating data Date wise

Question

Post reply

Retrieve Repeating data Date wise

Hakuna Matata

SSCertifiable

Points: 5577
More actions
January 30, 2012 at 3:24 am

#250416

Hi,
I have a table as shown in the excel attachment.
The table has 3 columns col1, col2 & date.
If for a particular date col2 has repeating data i need to retrieve data associated to the repeating values in col2 .
For example , in my table for the date 1/3/2011 lon in col2 occurs thrice, so I need to retrieve data associated to lon. Similarly i need to retrieve data for mel in col2 for the date 1/5/2011.
if I am not clear please refer the excel attached.
Regards,
Nithin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

WITH CTE AS (

SELECT col1,col2,[date],

COUNT(*) OVER(PARTITION BY col2,[date]) AS num

FROM mytable)

SELECT col1,col2,[date]

FROM CTE

WHERE num>1;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 2

Thanks a loooooooooooot !!!!!! Mark for the timely help :-):-):-):-):-):-):-):-):-):-):-)

crazy4sql SSCoach Points: 19590 More actions · Answer 3

Not sure if I understood your requirement correctly, but is this query not helping

select col1,col2,date from tblname order by date desc/asc

----------
Ashish

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 4

Hi,

I need to get only the data that repeats date wise.

crazy4sql (1/30/2012)
Not sure if I understood your requirement correctly, but is this query not helping
select col1,col2,date from tblname order by date desc/asc