An annoying GROUP BY problem

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

May 5th, 2004, 09:21 AM

flipflops

Contributing User

Join Date: Jul 2003

Posts: 39

Time spent in forums: 1 h 27 m 2 sec
Reputation Power: 6

An annoying GROUP BY problem

Hi

I am trying to use this littel bit of SQL and can't get it to give me the results I need.

SELECT DISTINCT ward_id, MAX(DISTINCT date_modified) AS date_modified, modified_by
FROM dbo.HOURS
GROUP BY ward_id, modified_by

This spits out the following:

Code:


ward_id   |  date_modified					  |   modified_by 
________|___________________________|__________________  
1		   |  05/05/2004 14:40:00			  |   3
4 		  |  05/05/2004 12:00:00			  |   3
1		   |  05/05/2004 12:36:00			  |   12
3		   |  05/05/2004 12:33:00			  |   12
4		   |  05/05/2004 12:35:00			  |   12

The trouble is that I want it to spit out this:

Code:


ward_id   |  date_modified					|   modified_by 
_________|_________________________|__________________  
1			|  05/05/2004 14:40:00		   |   3
3			|  05/05/2004 12:33:00		   |   12
4			|  05/05/2004 12:35:00		   |   12

The problem is it that this SQL is pulling out extra values for the modified_by. What I am after is the modified_by value that corresponds to to the MAX(date_modified) for each DISTINCT ward_id.

I hope somebody can help, and thanks to everyone who has a look.

Flipflops.

May 5th, 2004, 09:42 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,766

Time spent in forums: 2 Weeks 4 Days 6 h 24 m 8 sec
Reputation Power: 37

Code:

select * from dbo.HOURS as h
where date_modified in (
select max(date_modified) 
from dbo.hours 
where wardid = h.wardid)

May 5th, 2004, 10:07 AM

flipflops

Contributing User

Join Date: Jul 2003

Posts: 39

Time spent in forums: 1 h 27 m 2 sec
Reputation Power: 6

Thanks swampBoogie for replying,

But i'm not quite sure what you are getting at with that... i've been fidling around with what you just posted, but i don't think its what i'm after.

To reiterate: (but hopefully more clearly)

For each DISTINCT ward_id value (refering to a list of names in a lookup table), I want to get the most recent date_modified value (datetime) and the modified_by value (refering to a list of users in a lookup table)

There are multiple instances of any particular ward_id, but I'm just after the most recent.

(Why is it so easy to visulise what you want and so hard to get it ?)

Cheers.

May 5th, 2004, 04:05 PM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,343

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 5 m 22 sec
Reputation Power: 891

i don't know why you would want to fiddle with it

swampBoogie's query is exactly what you need

__________________
r937.com | rudy.ca

May 6th, 2004, 04:30 AM

flipflops

Contributing User

Join Date: Jul 2003

Posts: 39

Time spent in forums: 1 h 27 m 2 sec
Reputation Power: 6

Apologies swampBoogie and thanks r937,

I just looked at swampBooogies post again and I just needed to stick in a Distinct and replace * with the fields I was after... absolutely perfect.

Cheers.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: