Awkward Group By statement - 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

General SQL Server Forums

New to SQL Server Programming

Awkward Group By statement

Author

Topic

Simon13579
Starting Member

10 Posts

Posted - 2008-10-30 : 05:11:20

Hello,

I have the following table:


StatusTbl(ID_PK int identity, JobNumber varchar(255), UserId varchar(255), Status varchar(255), DateTimeOccurred datetime)

Each Job has a resource and will, through the course of its lifecycle, have various statuses. Each time a job's status changes, a new row is entered into the table, so it is possible to have the following:


ID_PK     JobNumber    UserId       Status      DateTimeOccurred
10        Job1         User1        sent        30/10/2008 10:30:00
11        Job1         User1        working     30/10/2008 10:35:00
12        Job1         User1        finished    30/10/2008 10:40:00
13        Job2         User1        sent        30/10/2008 10:45:00

What I would like to do is run a query to, for each JobId, show its latest Status. Because the data can be put into the table out of order, the only field I can use to determine the most recent event is the DateTimeOccurred.

Here is what I have:


SELECT UserId, JobNumber, max(DateTimeOccurred)
FROM StatusTbl
GROUP BY UserId, JobNumber

This works perfectly except for one problem -- it doesn't display the actual Status string. In principle it's pretty simple; get the Status which matches the PK_ID of the associated DateTimeOccurred, but I can't find any way to manage this.

Is there a simple way to solve this?

Much appreciated,

Simon

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-30 : 05:15:18

Try

SELECT t2.UserId, t2.JobNumber,t1.status,t2.DateTimeAdded FROM StatusTbl as t1
INNER JOIN 
(
	SELECT UserId, JobNumber, max(DateTimeAdded) as DateTimeAdded
	FROM StatusTbl
	GROUP BY UserId, JobNumber
) as t2 on t1.UserId=t2.UserId and t1.JobNumber=t2.JobNumber

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 05:20:52

or if sql 2005

select ID_PK,     
JobNumber,    
UserId,       
Status,      
DateTimeOccurred
from
(
select row_number() over(partition by JobNumber order by DateTimeOccurred desc) as seq,*
from table
)t
where t.seq=1

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-30 : 05:21:42

quote:

Originally posted by madhivanan

Try

SELECT t2.UserId, t2.JobNumber,t1.status,t2.DateTimeAdded FROM StatusTbl as t1
INNER JOIN 
(
	SELECT UserId, JobNumber, max(DateTimeAdded) as DateTimeAdded
	FROM StatusTbl
	GROUP BY UserId, JobNumber
) as t2 on t1.UserId=t2.UserId and t1.JobNumber=t2.JobNumber

Madhivanan

Failing to plan is Planning to fail

I think this may not give the desired results and in fact provide duplicates. I don't have SQL server to validate. The outer and inner tables must be joined on (Job and date) or PK.

Simon13579
Starting Member

10 Posts

Posted - 2008-10-30 : 05:33:27

cvraghu, you are correct; Madhivanan's query unfortunately does return duplicates.

visakh16, this does work as desired; thanks very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 05:36:25

quote:
Originally posted by Simon13579

cvraghu, you are correct; Madhivanan's query unfortunately does return duplicates.

visakh16, this does work as desired; thanks very much!

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources