March 25, 2014 at 11:48 am
I have the following sample data:
create table #test (ID varchar(20), someDate datetime)
insert into #test(ID, someDate)
select '1', '17 Mar 2014' union all
select '1', '17 Mar 2014' union all
select '1', '6 Mar 2014' union all
select '2', '2 Mar 2014' union all
select '2', '2 Mar 2014' union all
select '2', '20 Mar 2014'
basically what I want is to write a query that only brings back records where the someDate is duplicated for the ID and the duplicated date is the latest. So from the above sample data, only ID 1 would be returned.
I do not want to use a RANK or ROW_NUMBER function for this.
Any ideas?
Thanks.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
March 25, 2014 at 12:21 pm
I could probably condense this down but I had to do it real quick. I believe this will work for you and you can change it just to return ID's.
WITH date1
AS (
SELECT ID
, somedate
, COUNT(*) AS date_count
FROM #test
GROUP BY id
, somedate
HAVING COUNT(*) > 1
)
SELECT *
FROM date1
WHERE (
SELECT MAX(somedate)
FROM date1
) = somedate
ORDER BY somedate DESC
March 25, 2014 at 1:30 pm
Why do you NOT want to use RANK or ROW_NUMBER functions?
March 25, 2014 at 1:43 pm
The truth is I'm no longer using SQL server.
Using Sybase which doesn't have these functions in ASE.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
March 25, 2014 at 2:19 pm
Abu Dina (3/25/2014)
The truth is I'm no longer using SQL server.Using Sybase which doesn't have these functions in ASE.
Okay. You might want to mention that up front when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply