Select 1 of 6 per job - 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

SQL Server Development (2000)

Select 1 of 6 per job

Author

Topic

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-08-05 : 11:44:46

Table 1 contains "jobs"
Table 2 contains job status entries (currently 6 per job)

Job status is ranked by priority with a value indicating if thie status has been achieved.

e.g.

job # : status :rank : value (True/False)
1 : logged : 1 : t
1 : stage 1 complete : 2 : t
1 : stage 2 complete : 3 : t
1 : stage 3 complete : 4 : F
1 : cancelled : 6 : t
1 : completed : 5 : F

What I need to do is select all rows from table 1 with the highest ranked status for each job from table 2.

e.g.
job #, - job details from table 1 -, highest ranked status from table 2

1, job details, cancelled

How can I achieve this?

Any help appreciated.

-dw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-05 : 12:21:27

select * from Table1 t1 inner join (select JobId, max(rank) as rank from Table2 group by JobId) as t2 on t1.JobId = t2.JobId

Go with the flow & have fun! Else fight the flow :)

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-08-09 : 06:51:53

Thanks for the reply spirit1.

I have been working on your suggestion but can't quite get what I want. My original post showed a sample of what I was trying to achieve. The code below is as near as I can get to what I want:

SELECT t1.JobNumber, t1.StatusID AS JobStatusID, t2.StatusDescription, t2.Priority
FROM jobHeaders t1 INNER JOIN
(SELECT jobStatus.JobNumber, jobStatus.StatusID AS StatusID, jobStatusList.StatusDescription, jobStatusList.Priority
FROM jobStatus INNER JOIN
jobStatusList ON jobStatus.StatusID = jobStatusList.StatusID
WHERE (jobStatus.[Value] = 1)) t2 ON t1.JobNumber = t2.JobNumber

The result I get is

JobNumber...JobStatusID...StatusDescription...Priority...
04G01157....1.............Awaiting Check......9..........
04G01157....1.............Job Printed.........8..........
04G01157....1.............Parts Printed.......7..........
04G01158....1.............Job Printed.........8..........
04G01158....1.............Sending.............5..........

My first problem is that I need to sort by jobNumber, Priority in order to get the lowest priority value for each job. The second problem is that I only want the item with the lowest Priority value, the rows with priority value of 7 and 5 in my sample above.

The result I want is:

JobNumber...JobStatusID...StatusDescription...Priority...
04G01157....1.............Parts Printed.......7..........
04G01158....1.............Sending.............5..........

The lowest priority value is the highest stage of processing that the job has reached. (the current status at a given point in time)

Is this possible or do I need to rethink my approach?

Thanks for your help.

-dw

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-08-09 : 07:27:08

Getting closer to what I want. The code below shows JobNumber and Priority.

How can I extend this to show StatusDescription as well?

Is there a better way to achieve the same result?

Recommend a good book?

Thanks,
-dw

SELECT JobNumber, MIN(Priority) AS Priority
FROM (SELECT t1.JobNumber, t1.StatusID AS JobStatusID, t2.StatusDescription, t2.Priority
FROM jobHeaders t1 INNER JOIN
(SELECT jobStatus.JobNumber, jobStatus.StatusID AS StatusID, jobStatusList.StatusDescription, jobStatusList.Priority
FROM jobStatus INNER JOIN
jobStatusList ON jobStatus.StatusID = jobStatusList.StatusID
WHERE (jobStatus.[Value] = 1)) t2 ON t1.JobNumber = t2.JobNumber) DERIVEDTBL
GROUP BY JobNumber

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-09 : 08:08:25

and this doesn't work for you?

SELECT JobNumber, MIN(Priority) AS Priority, StatusDescription
FROM (SELECT t1.JobNumber, t1.StatusID AS JobStatusID, t2.StatusDescription, t2.Priority
FROM jobHeaders t1 INNER JOIN
(SELECT jobStatus.JobNumber, jobStatus.StatusID AS StatusID, jobStatusList.StatusDescription, jobStatusList.Priority
FROM jobStatus INNER JOIN
jobStatusList ON jobStatus.StatusID = jobStatusList.StatusID
WHERE (jobStatus.[Value] = 1)) t2 ON t1.JobNumber = t2.JobNumber) DERIVEDTBL
GROUP BY JobNumber, StatusDescription

books: http://www.sqlteam.com/store.asp

Go with the flow & have fun! Else fight the flow :)

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-08-09 : 09:09:41

Yes it shows the StatusDescription but returns rows for each Status not just the highest (lowest Priority value)

The following returns 1 row as expected:

SELECT JobNumber, MIN(Priority) AS Priority
FROM (SELECT t1.JobNumber, t1.StatusID AS JobStatusID, t2.StatusDescription, t2.Priority
FROM jobHeaders t1 INNER JOIN
(SELECT jobStatus.JobNumber, jobStatus.StatusID AS StatusID, jobStatusList.StatusDescription, jobStatusList.Priority
FROM jobStatus INNER JOIN
jobStatusList ON jobStatus.StatusID = jobStatusList.StatusID
WHERE (jobStatus.[Value] = 1)) t2 ON t1.JobNumber = t2.JobNumber) DERIVEDTBL
where jobnumber='04G01157'
GROUP BY JobNumber
order by jobnumber

Adding StatusDescription returns 4 rows - one for each status. In otherwords it overrides the "MIN" function:

SELECT JobNumber, MIN(Priority) AS Priority, StatusDescription
FROM (SELECT t1.JobNumber, t1.StatusID AS JobStatusID, t2.StatusDescription, t2.Priority
FROM jobHeaders t1 INNER JOIN
(SELECT jobStatus.JobNumber, jobStatus.StatusID AS StatusID, jobStatusList.StatusDescription, jobStatusList.Priority
FROM jobStatus INNER JOIN
jobStatusList ON jobStatus.StatusID = jobStatusList.StatusID
WHERE (jobStatus.[Value] = 1)) t2 ON t1.JobNumber = t2.JobNumber) DERIVEDTBL
where jobnumber='04G01157'
GROUP BY JobNumber, StatusDescription
order by jobnumber

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-09 : 09:32:46

well without your table structure i can only guess, but it seems you only need one more join on the whole query...
it would be useful if you post the table schem and sample data so we can simply run them through the QA.

Go with the flow & have fun! Else fight the flow :)

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-08-09 : 12:20:35

OK here we go:

SQL Scripts to create 3 tables (I have only included relevant fields):

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobHeadersTest]') and OBJECTPROPERTY(id,

N'IsUserTable') = 1)
drop table [dbo].[jobHeadersTest]
GO

CREATE TABLE [dbo].[jobHeadersTest] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[JobNumber] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatusID] [int] NULL

) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobStatusListTest]') and OBJECTPROPERTY(id,

N'IsUserTable') = 1)
drop table [dbo].[jobStatusListTest]
GO

CREATE TABLE [dbo].[jobStatusListTest] (
[StatusID] [int] IDENTITY (1, 1) NOT NULL ,
[StatusDescription] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Priority] [int] NULL ,
[Timestamp] [timestamp] NULL ,
[ListOrder] [int] NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobStatusTest]') and OBJECTPROPERTY(id,

N'IsUserTable') = 1)
drop table [dbo].[jobStatusTest]
GO

CREATE TABLE [dbo].[jobStatusTest] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[JobNumber] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatusID] [int] NOT NULL ,
[Initials] [char] (4) COLLATE Latin1_General_CI_AS NULL ,
[TimeStamp] [timestamp] NULL ,
[Value] [bit] NOT NULL
) ON [PRIMARY]
GO

Queries:

-- This does not give me what I want
-- I only want one row returned with the lowest priority value

SELECT JobNumber, MIN(Priority) AS Priority, StatusDescription
FROM (SELECT t1.JobNumber, t1.StatusID AS JobStatusID, t2.StatusDescription, t2.Priority
FROM jobHeadersTest t1 INNER JOIN
(SELECT jobStatusTest.JobNumber, jobStatusTest.StatusID AS StatusID,

jobStatusListTest.StatusDescription, jobStatusListTest.Priority
FROM jobStatusTest INNER JOIN
jobStatusListTest ON jobStatusTest.StatusID = jobStatusListTest.StatusID
WHERE (jobStatusTest.[Value] = 1)) t2 ON t1.JobNumber = t2.JobNumber) DERIVEDTBL
where jobnumber='04G01157'
GROUP BY JobNumber, StatusDescription
order by jobnumber

Result
04G01157 9 Awaiting Check
04G01157 7 Parts Printed
04G01157 5 Sending
04G01157 8 Yellow Printed

-- This gives me what I want without the description!!!

SELECT JobNumber, MIN(Priority) AS Priority
FROM (SELECT t1.JobNumber, t1.StatusID AS JobStatusID, t2.StatusDescription, t2.Priority
FROM jobHeadersTest t1 INNER JOIN
(SELECT jobStatusTest.JobNumber, jobStatusTest.StatusID AS StatusID,

jobStatusListTest.StatusDescription, jobStatusListTest.Priority
FROM jobStatusTest INNER JOIN
jobStatusListTest ON jobStatusTest.StatusID = jobStatusListTest.StatusID
WHERE (jobStatusTest.[Value] = 1)) t2 ON t1.JobNumber = t2.JobNumber) DERIVEDTBL
where jobnumber='04G01157'
GROUP BY JobNumber
order by jobnumber

Result
04G01157 5

What I want:
04G01157 5 Sending

Test Data:

JobHeadersTest

"id","JobNumber","StatusID"
492,"04G01157",1

JobStatusTest

"id","JobNumber","StatusID","Initials","Value"
2953,"04G01157",1,"EM ",True
2954,"04G01157",2,"",False
2955,"04G01157",3,"AB ",True
2956,"04G01157",4,"3 ",True
2957,"04G01157",5,"",False
2958,"04G01157",6,"WEB ",True
2959,"04G01157",7,"",False
2960,"04G01157",8,"",False

JobStatusList

"StatusID","StatusDescription","Priority","ListOrder"
1,"Awaiting Check",9,1
2,"On Hold",2,2
3,"Yellow Printed",8,3
4,"Parts Printed",7,4
5,"All Results Received",6,5
6,"Sending",5,6
7,"Sent to Accounts",3,7
8,"Cancelled",1,8

JobHeadersTest links to JobStatus by JobNumber
JobStatus links to JobStatusList by StatusID

Thanks for all your help. I looked at the book list. Do you recommend any in particular for this type of work?

-dw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-10 : 11:27:06

try this
select JST.JobNumber, JSLT.StatusDescription, JSLT.Priority
from @JobStatusTest JST
inner join @jobStatusListTest JSLT on (JST.StatusID = JSLT.StatusID) and (JST.Value=1)
inner join (select t1.JobNumber, min(Priority) as Priority
from @JobStatusTest t1
inner join @jobStatusListTest t2 on (t1.StatusID = t2.StatusID) and (t1.Value=1)
group by t1.JobNumber) JSLT2 on (JST.JobNumber = JSLT2.JobNumber) and (JSLT.Priority = JSLT2.Priority)
where JST.jobnumber='04G01157'

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-10 : 11:53:07

Joe Celko's books are very good.

Go with the flow & have fun! Else fight the flow :)

liffey
Yak Posting Veteran

58 Posts

Posted - 2004-08-10 : 12:26:51

Brilliant!

Thanks for all your time and effort.

Just one question. What is the significance of the @ before the table names in your query? I had to remove them to get it to run.

-dw

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-10 : 12:35:28

i didn't want to create tables in my db so i used table variables. i just forgot to delete them.
i would have posted the solution before but i didn't have the time...
hope it works ok, and glad i could help.

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources