Getting most recent entries - 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

Transact-SQL (2000)

Getting most recent entries

Author

Topic

tinkerman
Starting Member

13 Posts

Posted - 2005-06-01 : 12:05:23

Samples
SampleId int
samplename varchar(25)

Reading
Id sampleId Reading date
1 2 43 03/01/2005
1 5 3 03/01/2005
1 2 12 02/24/2005
1 2 7 03/05/2005
....
I have a transaction table with a listing of readings. I want to get the last 5 readings per sample. Any thoughts?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2005-06-01 : 12:09:47

select top 5 sampleid, samplename from samples order by sampleid desc

or use SET ROWCOUNT

tinkerman
Starting Member

13 Posts

Posted - 2005-06-01 : 12:47:42

I want the most recent 5 readings from the readings tbl per sampleid.
Ex.

sampleid reading date
2 4 03/05/2005
2 8 03/04/2005
2 2 03/02/2005
2 15 02/26/2005
2 0 02/22/2005
5 13 03/01/2005
5 18 02/04/2005
5 12 02/02/2005
5 31 01/31/2005
5 13 01/30/2005
...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 03:16:03

Select top 5 reading from readingstbl order by reading Desc

Madhivanan

Failing to plan is Planning to fail

tinkerman
Starting Member

13 Posts

Posted - 2005-06-02 : 09:23:35

I am not looking for the top 5 rows from either table. I want 5 rows per sample. So with 5 samples, thats 25 rows, with 10 samples thats 50 rows, not just the top 5. The output should look like my last post above.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-02 : 09:25:00

can a single sample have more than 1 reading on a given day?

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

tinkerman
Starting Member

13 Posts

Posted - 2005-06-02 : 09:29:03

nope, one reading per sample, per day.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-02 : 10:04:06

By the way, when asking a question... you should include the part in blue.


Create Table #myTable (sampleId int, reading int, date datetime)
Insert Into #myTable
Select 1, 21, '3/1/2005' Union
Select 1, 22, '3/2/2005' Union
Select 1, 23, '3/3/2005' Union
Select 1, 24, '3/4/2005' Union
Select 1, 25, '3/5/2005' Union
Select 1, 26, '3/6/2005' Union
Select 2, 27, '3/1/2005' Union
Select 2, 28, '3/2/2005' Union
Select 2, 29, '3/3/2005' Union
Select 2, 20, '3/4/2005' Union
Select 2, 21, '3/5/2005' Union
Select 2, 22, '3/6/2005' Union
Select 3, 23, '3/1/2005' Union
Select 3, 24, '3/2/2005' Union
Select 3, 25, '3/3/2005' Union
Select 3, 26, '3/4/2005' Union
Select 3, 27, '3/5/2005' Union
Select 3, 28, '3/6/2005' 

Declare @recordsPerSample int

Set @recordsPerSample = 3

Select A.* 
From #myTable A
Inner Join #myTable B
On A.sampleId = B.sampleId
and A.date <= B.date
Group By A.sampleId, A.reading, A.date
Having count(*)<=@recordsPerSample

Drop Table #myTable

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

tinkerman
Starting Member

13 Posts

Posted - 2005-06-02 : 10:38:14

Self join with having...Figures, I was messing with a tally and a cross join.

Thanks Corey.

tinkerman
Starting Member

13 Posts

Posted - 2005-06-02 : 12:05:34

Is there a faster way to do this? The self join is 5 seconds on 32K rows and this is a test tbl.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-02 : 13:16:33

The second method is more expensive on the smaller data set, but it might run faster on 32k rows self-joined. Not sure though.


Create Table #myTable (sampleId int, reading int, date datetime)
Create index ix_main On #myTable (sampleId, date)

Insert Into #myTable
Select 1, 21, '3/1/2005' Union
Select 1, 22, '3/2/2005' Union
Select 1, 23, '3/3/2005' Union
Select 1, 24, '3/4/2005' Union
Select 1, 25, '3/5/2005' Union
Select 1, 26, '3/6/2005' Union
Select 2, 27, '3/1/2005' Union
Select 2, 28, '3/2/2005' Union
Select 2, 29, '3/3/2005' Union
Select 2, 20, '3/4/2005' Union
Select 2, 21, '3/5/2005' Union
Select 2, 22, '3/6/2005' Union
Select 3, 23, '3/1/2005' Union
Select 3, 24, '3/2/2005' Union
Select 3, 25, '3/3/2005' Union
Select 3, 26, '3/4/2005' Union
Select 3, 27, '3/5/2005' Union
Select 3, 28, '3/6/2005' 

Declare @recordsPerSample int

Set @recordsPerSample = 3

Select A.* 
From #myTable A
Inner Join #myTable B
On A.sampleId = B.sampleId
and A.date <= B.date
Group By A.sampleId, A.reading, A.date
Having count(*)<=@recordsPerSample

Create Table #results (sampleId int, reading int, date datetime)
Create index ix_main On #results (sampleId, date)

While (@recordsPerSample>0)
Begin
	Insert Into #results
	Select Z.* 
	From #myTable Z
	Inner Join 
		(Select A.sampleId, Date=max(A.Date) 
		From #myTable A
		Left Join #results B
		On A.sampleId = B.sampleId
		and A.date = B.date
		Where B.sampleId is null
		Group By A.sampleId) Y
	On Z.sampleId = Y.sampleId
	and Z.date = Y.date

	Set @recordsPerSample = @recordsPerSample-1
End

Select * From #results

Drop Table #results
Drop Table #myTable

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-02 : 13:21:46

How about

http://weblogs.sqlteam.com/brettk/archive/2005/02/10/4153.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-02 : 13:31:06

brett - are you trying to boost your post count?? b/c you've already got me squashed

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-02 : 14:01:56

Old standard answer:
"Search this forum for ..."
New and improved answer:
"Look in Brett's weblog"

rockmoose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-03 : 00:44:42

quote:
Originally posted by rockmoose

Old standard answer:
"Search this forum for ..."
New and improved answer:
"Look in Brett's weblog"

rockmoose

Thats true.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources