Max date brings two values -- choosing results based off of other field value

Question

Max date brings two values -- choosing results based off of other field value

themangoagent

SSC Eights!

Points: 968
More actions
June 27, 2012 at 9:27 am

#100482

So I have a table with ID's, a pseudo-date field, a score field, and a code. I'm trying to pull the score for the max date. Problem is there could be two scores for the same date, but with a different code.
The last 4 records returned show my problem.
What I'm trying to accomplish is getting the max date with the score and choosing Code = 'C' over Code = 'E'.
Any ideas would be greatly appreciated!
This is the code I have so far:
create table #mytable(
ID varchar(4),
PDate varchar(6),
score float,
code varchar(1)
);
GO
Insert into #mytable values ( '6855','201111','0.584','C ');
Insert into #mytable values ( '6855','201110','0.584','C ');
Insert into #mytable values ( '6855','201112','0.584','C ');
Insert into #mytable values ( '6855','201109','0.584','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','1.698','C ');
Insert into #mytable values ( '6855','201108','0.584','C ');
Insert into #mytable values ( '6855','201107','0.584','C ');
Insert into #mytable values ( '6855','201107','0.584','E ');
Insert into #mytable values ( '6855','201107','0.584','C ');
Insert into #mytable values ( '6855','201107','0.584','C ');
Insert into #mytable values ( '6855','201107','0.584','C ');
Insert into #mytable values ( '6855','201107','0.584','C ');
Insert into #mytable values ( '6855','201107','0.584','C ');
Insert into #mytable values ( '6855','201112','.777','E');
Insert into #mytable values ( '4955','201107','1.283','E ');
Insert into #mytable values ( '4955','201105','1.283','E ');
Insert into #mytable values ( '4955','201101','1.283','E ');
Insert into #mytable values ( '4955','201104','1.283','E ');
Insert into #mytable values ( '4955','201103','1.283','E ');
Insert into #mytable values ( '4955','201108','1.283','E ');
Insert into #mytable values ( '4955','201109','1.283','E ');
Insert into #mytable values ( '4955','201111','1.283','E ');
Insert into #mytable values ( '4955','201112','1.283','E ');
Insert into #mytable values ( '4955','201110','1.283','E ');
Insert into #mytable values ( '4955','201106','1.283','E ');
Insert into #mytable values ( '4955','201102','1.283','E ');
Insert into #mytable values ( '5095','201103','0.623','C ');
Insert into #mytable values ( '5095','201102','0.623','C ');
Insert into #mytable values ( '5095','201101','0.623','C ');
Insert into #mytable values ( '5095','201107','0.623','C ');
Insert into #mytable values ( '5095','201105','0.623','C ');
Insert into #mytable values ( '5095','201106','0.623','C ');
Insert into #mytable values ( '5095','201109','0.623','C ');
Insert into #mytable values ( '5095','201112','0.623','E ');
Insert into #mytable values ( '5095','201111','0.623','C ');
Insert into #mytable values ( '5095','201110','0.623','C ');
Insert into #mytable values ( '5095','201108','0.623','C ');
Insert into #mytable values ( '5095','201104','0.623','C ');
Insert into #mytable values ( '5109','201110','1.396','C ');
Insert into #mytable values ( '5109','201112','1.396','C ');
Insert into #mytable values ( '5109','201110','1.396','C ');
Insert into #mytable values ( '5109','201111','1.396','C ');
Insert into #mytable values ( '5342','201111','0.979','C ');
Insert into #mytable values ( '5342','201109','0.979','C ');
Insert into #mytable values ( '5342','201108','0.979','C ');
Insert into #mytable values ( '5342','201112','0.979','C ');
Insert into #mytable values ( '5342','201110','0.979','C ');
Insert into #mytable values ( '5342','201107','0.979','C ');
Insert into #mytable values ( '5342','201107','0.979','C ');
Insert into #mytable values ( '5342','201107','0.979','C ');
Insert into #mytable values ( '5342','201107','0.979','E ');
Insert into #mytable values ( '5342','201107','0.979','E ');
Insert into #mytable values ( '5342','201107','0.979','C ');
Insert into #mytable values ( '5342','201107','0.979','C ');
Insert into #mytable values ( '5561','201106','1.186','C ');
Insert into #mytable values ( '5561','201104','1.186','C ');
Insert into #mytable values ( '5561','201103','1.186','C ');
Insert into #mytable values ( '5561','201101','1.186','C ');
Insert into #mytable values ( '5561','201108','1.186','C ');
Insert into #mytable values ( '5561','201111','1.186','C ');
Insert into #mytable values ( '5561','201110','1.186','E ');
Insert into #mytable values ( '5561','201109','1.186','C ');
Insert into #mytable values ( '5561','201112','1.186','C ');
Insert into #mytable values ( '5561','201105','1.186','C ');
Insert into #mytable values ( '5561','201107','1.186','C ');
Insert into #mytable values ( '5561','201102','1.186','C ');
Insert into #mytable values ( '6033','201101','0.425','C ');
Insert into #mytable values ( '6033','201104','0.425','C ');
Insert into #mytable values ( '6033','201102','0.425','C ');
Insert into #mytable values ( '6033','201101','.555','E ');
Insert into #mytable values ( '6170','201104','0.65','C ');
Insert into #mytable values ( '6171','201103','0.65','C ');
Insert into #mytable values ( '6172','201101','0.65','C ');
Insert into #mytable values ( '6173','201105','0.65','C ');
Insert into #mytable values ( '6174','201107','0.65','C ');
Insert into #mytable values ( '6175','201102','0.65','C ');
Insert into #mytable values ( '6285','201102','0.18','C ');
Insert into #mytable values ( '6285','201103','0.18','C ');
Insert into #mytable values ( '6285','201107','0.18','C ');
Insert into #mytable values ( '6285','201105','0.18','C ');
Insert into #mytable values ( '6285','201108','0.18','C ');
Insert into #mytable values ( '6285','201109','0.18','C ');
Insert into #mytable values ( '6285','201104','0.18','C ');
Insert into #mytable values ( '6285','201106','0.18','C ');
Insert into #mytable values ( '6285','201101','0.18','C ');
Insert into #mytable values ( '6285','201110','0.18','C ');
Insert into #mytable values ( '6285','201111','0.18','C ');
Insert into #mytable values ( '6285','201112','0.18','C ');
Insert into #mytable values ( '6301','201109','0.519','C ');
Insert into #mytable values ( '6301','201108','0.519','C ');
Insert into #mytable values ( '6301','201107','0.519','C ');
Insert into #mytable values ( '6301','201109','.777','E ');
SELECT s.ID, s.PDate, score, code
FROM #mytable S
WHERE s.PDate =(
SELECT MAX(PDate)
FROM #mytable
WHERE ID = S.ID )
group by s.ID, s.PDate, score, code
drop table #mytable

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

Are you really using SQL Server 2000? If so, this should work but for SQL Server 2005 upwards there are better solutions.

SELECT s.ID, s.PDate, score, code

FROM #mytable S

WHERE NOT EXISTS (SELECT *

FROM #mytable x

WHERE x.ID = S.ID

AND (

x.PDate>S.PDate

OR (x.PDate=S.PDate AND x.code<S.code))

)

____________________________________________________

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

themangoagent SSC Eights! Points: 968 More actions · Answer 2

Mark-101232 (6/27/2012)
Are you really using SQL Server 2000? If so, this should work but for SQL Server 2005 upwards there are better solutions.
SELECT s.ID, s.PDate, score, code
FROM #mytable S
WHERE NOT EXISTS (SELECT *
FROM #mytable x
WHERE x.ID = S.ID
AND (
x.PDate>S.PDate
OR (x.PDate=S.PDate AND x.code<S.code))
)

Yes, I really am.

We are in the process of migrating to 2008, but it won't be complete for at least 6-8 months. Until then, I have to get my hands dirty.

I'll check it out, thanks!