Finding the Max of 2 fields - SQL Server Forums

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

Transact-SQL (2000)

Finding the Max of 2 fields

Author

Topic

scullee
Posting Yak Master

103 Posts

Posted - 2003-05-11 : 23:59:47

Well its a bad day for me. 2 Questions in 1 day.

I have another one to challenge the gurus here.

I have a Log that has a Start_Date and End_date eg the date the task was started and the date it ended. We need a query that will return the user_id for the record with the highest of 2 dates so i can join this in a query to find the most recent touched log entry. This whole query is annoying me but i have everything except for this.

eg
Log_id Start Date End Date user
1 28/01/2003 5:00:00 AM 28/01/2003 5:01:00 AM TEST5
1 28/01/2003 5:01:00 AM 28/01/2003 5:02:00 AM TEST1
1 28/01/2003 5:03:00 AM 28/01/2003 5:04:00 AM TEST1
1 28/01/2003 5:05:00 AM 28/01/2003 5:06:00 AM TEST1
1 28/01/2003 5:07:00 AM 28/01/2003 5:08:00 AM TEST5
1 28/01/2003 5:58:00 AM 28/01/2003 6:00:00 AM TEST1
I need the system to return TEST1 because the end date on the last record is the highest date.

When the data looks like this
Log_id Start Date End Date user
1 28/01/2003 5:00:00 AM 28/01/2003 5:01:00 AM TEST5
1 28/01/2003 5:01:00 AM 28/01/2003 5:02:00 AM TEST1
1 28/01/2003 5:03:00 AM 28/01/2003 5:04:00 AM TEST1
1 28/01/2003 5:05:00 AM 28/01/2003 5:06:00 AM TEST1
1 28/01/2003 5:07:00 AM TEST5

I need the data to return TEST5 because the start date is the highest.

My first thoughts were a union query that got the list of users and start dates, users and end dates then sorted them together but that is going to be a killer when this table gets lots and lots of data in it. I was also thinking that if i write this as a sub query and then join to it from the main query it would limit the list it was getting and only end up re-sorting a small subset of the whole table, is this true?

I really hate this system atm!!!!

Edited by - scullee on 05/12/2003 00:01:54

Edited by - scullee on 05/12/2003 00:03:38

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-12 : 00:46:01

scullee,

CASE is your friend...


SELECT TOP 1 USERID
FROM scullee
GROUP BY UserID
ORDER BY CASE WHEN MAX(StartDate) > MAX(EndDate) THEN MAX(StartDate) ELSE MAX(EndDate) END DESC

DavidM

"SQL-3 is an abomination.."

scullee
Posting Yak Master

103 Posts

Posted - 2003-05-12 : 01:01:01

I did think about that. Maybe its because its 3:00 in the arvo and i have spent the day writing awful stored procs :(

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources