Comparing column values for a row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing column values for a row

Author

Topic

mayde78
Starting Member

2 Posts

Posted - 2005-07-07 : 10:01:38

I have a table
id ItemDesc Date1 Date2 Date 3 Date4

for each row i have to display

ItemDesc and max(Date1, Date2, Date3, Date4)

max(Date1, Date2, Date3, Date4) --> whichever date is latest

How can i do this without joins?

Milo Cold
Starting Member

14 Posts

Posted - 2005-07-07 : 11:11:03

Hello,

Hmmm...I not sure using MAX will work because it produces a summary of a column. The easiest way be to accomplish this may to use a cursor and some variables to hold the dates. This will allow you to find a row-based max.

Though this is the simplest, it may not be the most efficient method. That of course will depend on the sheer size of the table. Any who...just wanted to add a possible solution. I do hope others will chime in if they know of a more effective way to get this solved.

Plus, I don't think this can be done with joins, but I could be wrong...anybody got clues?

M. Cold

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-07-07 : 11:23:28

[code]select itemDesc,
case
when Date1 >= Date2 and Date1 >= Date3 and Date1 >= date4 then Date1
when Date2 > Date1 and Date2 >= Date3 and Date2 >= date4 then Date2
when Date3 > Date1 and Date3 > date2 and Date3 >= Date4 then Date3
else Date4
end as latestDate
from table[/code]

Milo Cold
Starting Member

14 Posts

Posted - 2005-07-07 : 11:34:11

Wow, that's much better! Me likey the CASE, thanks =)

M.Cold

mayde78
Starting Member

2 Posts

Posted - 2005-07-07 : 11:38:16

Thanks milo and mmarovic.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-07 : 12:04:36

you can also say:


select ItemDesc, Max(Date) 
from
(
 select ItemDesc, Date1 as Date from YourTable union all
 select ItemDesc, Date2 from YOurTable union all
 select ItemDesc, Date3 from YOurTable union all
 select ItemDesc, Date4 from YOurTable
) tmp
group by ItemDesc

or even consider normalizing your data if you are feeling a little crazy!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources