January 30, 2012 at 3:24 am
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
January 30, 2012 at 3:36 am
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/61537January 30, 2012 at 4:03 am
Thanks a loooooooooooot !!!!!! Mark for the timely help :-):-):-):-):-):-):-):-):-):-):-)
January 30, 2012 at 4:16 am
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
January 30, 2012 at 4:34 am
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 helpingselect col1,col2,date from tblname order by date desc/asc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy