Selecting last record added - 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 2008 Forums

Transact-SQL (2008)

Selecting last record added

Author

Topic

Wannabe67
Starting Member

14 Posts

Posted - 2012-05-14 : 10:59:45

In the data below, StudentId 1 added DocumentTypeId 1 twice. I only want to show StudentId 1, and other students, the latest time they added each different type of document.

CREATE TABLE [DocumentTypes](
	[DocumentTypeId] [int] IDENTITY(1,1) NOT NULL,
	[DocumentType] [varchar](25) NOT NULL
 CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED 
(
	[DocumentTypeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [Documents](
	[DocumentId] [int] IDENTITY(1,1) NOT NULL,
	[DocumentTypeId] [int] NOT NULL,
	[StudentId] [int] NOT NULL,
	[Title] [varchar](100) NOT NULL,
	[DateAdded] [date] NOT NULL,
 CONSTRAINT [PK_Documents_1] PRIMARY KEY CLUSTERED 
(
	[DocumentId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Data in my DocumentTypes table looks like this:
DocumentTypeId DocumentType
1 SomeForm1
2 SomeForm2
3 SomeForm3

Documents Table Data:
DocumentId DocumentTypeId StudentId Title DateAdded
1 1 1 Document One 2012-04-01
2 2 1 Document Two 2012-04-01
3 3 2 Document Three 2012-04-15
4 2 2 Document Four 2012-04-15
7 1 1 Document Five 2012-05-11
8 3 2 Document Six 2012-05-05

How I want the data to return:
DocumentId DocumentTypeId StudentId Title DateAdded
2 2 1 Document Two 2012-04-01
4 2 2 Document Four 2012-04-15
7 1 1 Document Five 2012-05-11
8 3 2 Document Six 2012-05-05

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-05-14 : 19:14:26

Untested:[CODE]select *
a.DocumentID,
a.DocumentTypeID,
a.StudentID,
a.Title,
a.DateAdded
from (
select
DocumentID,
DocumentTypeID,
StudentID,
Title,
DateAdded,
row_number() over (partition by StudentID, DocumentTypeID order by DateAdded DESC) rn
from
Documents
) a
where
a.rn = 1
order by
DocumentID,
StudentID[/CODE]BTW, why are you showing us the DocumentTypes table since it doesn't appear to be used?

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 22:40:01

[code]
SELECT d.*
FROM Documents d
OUTER APPLY(SELECT TOP 1 StudentId
FROM Documents
WHERE DocumentId > d.DocumentId
AND StudentId= d.StudentId
AND DocumentTypeId <> d.DocumentTypeId
ORDER BY DocumentId
)d1
WHERE d1.StudentId IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Wannabe67
Starting Member

14 Posts

Posted - 2012-05-15 : 09:44:22

I will be using it, but I guess I accidentally left it out. Thanks for the reply. I will run this and see how it does.

quote:
Originally posted by Bustaz Kool

Untested:[CODE]select *
a.DocumentID,
a.DocumentTypeID,
a.StudentID,
a.Title,
a.DateAdded
from (
select
DocumentID,
DocumentTypeID,
StudentID,
Title,
DateAdded,
row_number() over (partition by StudentID, DocumentTypeID order by DateAdded DESC) rn
from
Documents
) a
where
a.rn = 1
order by
DocumentID,
StudentID[/CODE]BTW, why are you showing us the DocumentTypes table since it doesn't appear to be used?

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Wannabe67
Starting Member

14 Posts

Posted - 2012-05-15 : 11:20:07

Worked great...thanks.

quote:
Originally posted by Wannabe67

I will be using it, but I guess I accidentally left it out. Thanks for the reply. I will run this and see how it does.

quote:
Originally posted by Bustaz Kool

Untested:[CODE]select *
a.DocumentID,
a.DocumentTypeID,
a.StudentID,
a.Title,
a.DateAdded
from (
select
DocumentID,
DocumentTypeID,
StudentID,
Title,
DateAdded,
row_number() over (partition by StudentID, DocumentTypeID order by DateAdded DESC) rn
from
Documents
) a
where
a.rn = 1
order by
DocumentID,
StudentID[/CODE]BTW, why are you showing us the DocumentTypes table since it doesn't appear to be used?

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources