need help with query - 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 2005 Forums

Transact-SQL (2005)

need help with query

Author

Topic

cakewalkr7
Starting Member

11 Posts

Posted - 2009-03-31 : 08:30:45

I have a page where a user can select from a drop down what type of file they want to upload. So a user can upload 3 resumes, 2 w4 files, etc. In my table I have a field with a numeric id that identifies what type of file they uploaded. What I need to do is get a resultset of the most recent record for each type of file. Getting the most recent records is easy enough but I'm not sure how to select only the latest 1 of each type. Does anyone know how to do this in a query? Thanks.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 08:33:43

Post table structures, sample data and output to be clear. Theres a link here which tells you exactly how you need to post a problem, just can't find it right now.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-31 : 08:36:26

First link in my signature

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-31 : 08:39:48

handy signature, thankyou. :)

cakewalkr7
Starting Member

11 Posts

Posted - 2009-03-31 : 08:52:51

Sorry about that. Here you go.

First the structure.


CREATE TABLE [dbo].[ContractorUploadedPaperwork](
	[UploadedPaperworkID] [int] IDENTITY(1,1) NOT NULL,
	[ContractJobID] [numeric](18, 0) NOT NULL,
	[PaperworkID] [int] NOT NULL,
	[PeopleID] [numeric](18, 0) NOT NULL,
	[FileName] [varchar](100) NOT NULL,
	[MimeType] [varchar](75) NOT NULL,
	[UploadedDate] [datetime] NOT NULL,
	[Status] [int] NOT NULL,
	[UpdatedDate] [datetime] NULL,
	[RejectReason] [varchar](50) NULL,
 CONSTRAINT [PK_ContractorUploadedPaperwork] PRIMARY KEY CLUSTERED 
(
	[UploadedPaperworkID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ContractorUploadedPaperwork]  WITH CHECK ADD  CONSTRAINT [FK_ContractorUploadedPaperwork_ContractJob] FOREIGN KEY([ContractJobID])
REFERENCES [dbo].[ContractJob] ([ContractJobID])
GO
ALTER TABLE [dbo].[ContractorUploadedPaperwork] CHECK CONSTRAINT [FK_ContractorUploadedPaperwork_ContractJob]
GO
ALTER TABLE [dbo].[ContractorUploadedPaperwork]  WITH CHECK ADD  CONSTRAINT [FK_ContractorUploadedPaperwork_Paperwork] FOREIGN KEY([PaperworkID])
REFERENCES [dbo].[Paperwork] ([PaperworkID])
GO
ALTER TABLE [dbo].[ContractorUploadedPaperwork] CHECK CONSTRAINT [FK_ContractorUploadedPaperwork_Paperwork]
GO
ALTER TABLE [dbo].[ContractorUploadedPaperwork]  WITH CHECK ADD  CONSTRAINT [FK_ContractorUploadedPaperwork_PeopleContracting] FOREIGN KEY([PeopleID])
REFERENCES [dbo].[PeopleContracting] ([PeopleID])
GO
ALTER TABLE [dbo].[ContractorUploadedPaperwork] CHECK CONSTRAINT [FK_ContractorUploadedPaperwork_PeopleContracting]
GO
ALTER TABLE [dbo].[ContractorUploadedPaperwork]  WITH CHECK ADD  CONSTRAINT [FK_ContractorUploadedPaperwork_TypeContractorUploadedPaperworkStatus] FOREIGN KEY([Status])
REFERENCES [dbo].[TypeContractorUploadedPaperworkStatus] ([TypeID])
GO
ALTER TABLE [dbo].[ContractorUploadedPaperwork] CHECK CONSTRAINT [FK_ContractorUploadedPaperwork_TypeContractorUploadedPaperworkStatus]

Sample insert


insert into ContractorUploadedPaperwork (ContractJobId, PaperworkId, PeopleId, FileName, Mimetype, UploadedDate, Status, UpdatedDate, RejectReason) values 
(9009, 33, 15780, 'test1.txt', 'text/plain', getdate(), 1, null, null)

sample data


9009	125	15780		780\15780\test.txt	text/plain	3/30/2009 1:07:35 PM
9009	33	15780		test0.txt		text/plain	3/31/2009 8:26:26 AM
9009	33	15780		test1.txt		text/plain	3/31/2009 8:26:57 AM

In the sample data, the last 2 rows are of the same doc type (33). I want to return row 1 (125) and the 3rd (33) as the most recent of each doc type.

Thanks.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-31 : 09:09:56

[code]declare @ContractorUploadedPaperwork TABLE
([UploadedPaperworkID] [int] IDENTITY(1,1) NOT NULL,
[ContractJobID] [numeric](18, 0) NOT NULL,
[PaperworkID] [int] NOT NULL,
[PeopleID] [numeric](18, 0) NOT NULL,
[FileName] [varchar](100) NOT NULL,
[MimeType] [varchar](75) NOT NULL,
[UploadedDate] [datetime] NOT NULL,
[Status] [int] NOT NULL,
[UpdatedDate] [datetime] NULL,
[RejectReason] [varchar](50) NULL
)

insert into @ContractorUploadedPaperwork
(ContractJobId, PaperworkId, PeopleId, [FileName], Mimetype, UploadedDate, [Status], UpdatedDate, RejectReason)

select 9009, 33, 15780, 'test1.txt', 'text/plain', '3/31/2009 8:26:57 AM', 1, null, null
union all select 9009,125,15780,'780\15780\test.txt','text/plain','3/30/2009 1:07:35 PM',1,null,null
union all select 9009,33,15780,'test0.txt','text/plain','3/31/2009 8:26:26 AM',1,null,null

select
ContractJobId, PaperworkId, PeopleId, [FileName], Mimetype, UploadedDate, [Status], UpdatedDate, RejectReason
from (
select
c.ContractJobId, c.PaperworkId, c.PeopleId, c.[FileName], c.Mimetype, c.UploadedDate, c.[Status], c.UpdatedDate, c.RejectReason
,row_number() OVER (partition by c.PaperworkId order by c.UploadedDate) as FID
from
@ContractorUploadedPaperwork c
) x
where
FID = 1

[/code]

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

cakewalkr7
Starting Member

11 Posts

Posted - 2009-03-31 : 09:17:17

I don't quite understand all of what you're doing but hey, it works! Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources