Select Top n Query
-
Hi there - Have run out of brain power with this one !
I'm trying to do a query in SQL 2000 to return a list of the latest (i.e. most recent) 4 records based on a date (TransDate) for each job (JobNo) where the status is one of a list. I've added an Identity key (RecordID) to create uniqueness but am having a problem. I'll try and explain;
Note: I only have the 1 table and am creating a derived table using nested select.
Each Job has a Status and a Trans Date and there can be many of these sets of data but obviously each record with have it's own unique RecordID
I initially tried the following just to get the latest records but this returns the 1st 4 records (of 11) and not the latest 4 records of 11 i.e. 8,9,10 & 11 - These records have a date
Select RecordID,JobNo,TransDate
From Table T1
where (RecordId in (Select Top 4 RecordID from Table T2 where T1.JobNo=T2.JobNo))
Order by JobNo,RecordID DESC
I then want to be able to filter the query for Statuses. I was going to use an IN list i.e Status IN ('A1','A2','A3', etc...)
Any ideas how I can do this?
-
It would help if you could provide the DDL for your table, some sample data, and what the returned result set should look like.
-
Thanks Lynn
Relevant field defs for the table;
JobNo - dec (6,0)
Status - Char (2)
TransDate - Datetime
RecordID - Identity (BigInt)
Job Status TransDate RecordID
1234 AB 01/07/07 1
1234 AB 02/07/07 2
1234 AB 03/07/07 3
1234 AB 03/07/07 4
1234 AB 07/07/07 5
1234 AB 08/07/07 6
5432 ZZ 02/07/07 7
5432 ZZ 05/07/07 8
5432 ZZ 05/07/07 9
5432 ZZ 06/07/07 10
5432 ZZ 06/07/07 11
6666 YY 02/07/07 12
I only want to return the 4 most recent records for each job. Therefore, the example data above should ONLY return 4 recs for Jobno 1234 (Ids: 6,5,4,&3), 4 recs for Jobno 5432 (Ids:11,10, 9 & 8) and 1 rec for Jobno 6666 (Id:12)
Hope thats a bit clearer
Thanks, Matt
-
Give this a try:
create table dbo.MyTable (
JobNo decimal(6,0),
Status char(2),
TransDate datetime,
RecordId bigint identity(1,1)
)
go
insert into dbo.MyTable values (1234,'AB','01/07/07')
insert into dbo.MyTable values (1234,'AB','02/07/07')
insert into dbo.MyTable values (1234,'AB','03/07/07')
insert into dbo.MyTable values (1234,'AB','03/07/07')
insert into dbo.MyTable values (1234,'AB','07/07/07')
insert into dbo.MyTable values (1234,'AB','08/07/07')
insert into dbo.MyTable values (5432,'ZZ','02/07/07')
insert into dbo.MyTable values (5432,'ZZ','05/07/07')
insert into dbo.MyTable values (5432,'ZZ','05/07/07')
insert into dbo.MyTable values (5432,'ZZ','06/07/07')
insert into dbo.MyTable values (5432,'ZZ','06/07/07')
insert into dbo.MyTable values (6666,'YY','02/07/07')
go
select * from dbo.MyTable
go
select
mt2.JobNo,
mt2.Status,
mt2.TransDate
from
dbo.MyTable mt2
where
mt2.RecordId in
(SELECT TOP 4
mt1.RecordId
FROM
dbo.MyTable mt1
WHERE
mt1.JobNo = mt2.JobNo
ORDER BY
mt1.JobNo,
mt1.TransDate desc
)
order by
mt2.JobNo,
mt2.TransDate desc
go
drop table dbo.MyTable
go
-
Lynn
Great !!!! Thats exactly what I needed to do - I can see where I was going wrong now. Many thanks for your help!
Matt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply