MAX and second MAX? - 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)

MAX and second MAX?

Author

Topic

benjamintb
Starting Member

15 Posts

Posted - 2004-09-01 : 05:50:59

I've got a problem. I have a rowset that returns a job number, date and a comment. There are lots of different job numbers and each job number may have multiple comments against each job with the date it was recorded. What I want to do is select just the 2 most recent comments added and not the rest.

I thought about giving max ago but that only returns one record.

Anyone have any ideas on how to go about doing something like this?

Thanks heaps!!

Ben

snaso
Starting Member

21 Posts

Posted - 2004-09-01 : 05:56:41

Hi Ben,
I have an idea:

SET ROWCOUNT 2
GO
select jobnumber, ...
from <tablename>
order by jobnumber desc

I think it should work fine.... let me know, regards

-snaso.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 06:09:07

but you must set the rowcount back to 0 after the query
SET ROWCOUNT 0

another way is to use top:

select top 2 *
from MyTable
order by jobnumber desc

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

snaso
Starting Member

21 Posts

Posted - 2004-09-01 : 06:17:45

stupid me, I forget top!
Its easier... is it also more efficient?

Regards,

-snaso.

benjamintb
Starting Member

15 Posts

Posted - 2004-09-01 : 06:35:14

Hi Guys,

Thanks for the quick response and its almost what I'm after... This wont work though because I have multiple job numbers each with comments. And I just want the 2 most recent comments for each job.

For example in this rowset I have 3 different job numbers and each job has 3 comments. So I just want the 2 most recent comment for each job.

9347 "Moniquer, can you please call Alan and ..." 24/08/2004 12:46:00 AM

9347 "Chase up SSL Aust. Non Root Cert. Get ...." 30/08/2004 9:11:00 AM

9347 "Awaiting information relating to which..." 31/08/2004 4:32:00 AM

9913 "The job failed with the followin g error:..." 23/02/2004 10:36:00 PM

9913 "The job failed with the followin g error: ..." 23/02/2004 10:36:00 PM

9913 "Backup successful" 24/02/2004 11:44:00 PM

11256 "Talked to Scott again. A number of the ..." 20/05/2004 8:25:00 AM

11256 "Need to create an installation package ..." 25/05/2004 9:31:00 AM

11256 "Installed and configured latest version ..." 31/05/2004 11:41:00 AM

Any other ideas???

Thanks again!

benjamintb
Starting Member

15 Posts

Posted - 2004-09-01 : 06:36:39

If I just use top 2 then all I'll get is

9347 "Moniquer, can you please call Alan and ..." 24/08/2004 12:46:00 AM

9347 "Chase up SSL Aust. Non Root Cert. Get ...." 30/08/2004 9:11:00 AM

snaso
Starting Member

21 Posts

Posted - 2004-09-01 : 06:39:06

Sorry Ben,
couldn't you order this rowset by date (descending) and catch the first two??

-snaso.

snaso
Starting Member

21 Posts

Posted - 2004-09-01 : 06:41:59

Obviously you have to order by jobnumberthen by date...

-snaso.

benjamintb
Starting Member

15 Posts

Posted - 2004-09-01 : 06:47:02

Sorry, I don't think I'm explaining myself properly. Maybe this will help...

I want the two most recent rows for each job number.

EG

What I would want returned would be this:

9347 "Chase up SSL Aust. Non Root Cert. Get ...." 30/08/2004 9:11:00 AM

9347 "Awaiting information relating to which..." 31/08/2004 4:32:00 AM

9913 "The job failed with the followin g error: ..." 23/02/2004 10:36:00 PM

9913 "Backup successful" 24/02/2004 11:44:00 PM

11256 "Need to create an installation package ..." 25/05/2004 9:31:00 AM

11256 "Installed and configured latest version ..." 31/05/2004 11:41:00 AM

Is this more clear? Thanks again!

snaso
Starting Member

21 Posts

Posted - 2004-09-01 : 06:53:27

ok, now it's clear, thank you... but I don't know a way to do what you want in a single step.
I would try with a temp table, but I don't know if it's what you're looking for....

-snaso.

benjamintb
Starting Member

15 Posts

Posted - 2004-09-01 : 07:01:48

well...thanks anyway. I've had enough of work so maybe it will come to me tomorrow! I was dreading that temp tables would be the answer....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 08:08:50

well you could use this: -- saw this solution here on sqlteam somewhere...

Select id, comment, dt
From myTable as t1
Where (Select count(1) from myTable Where id = t1.id and dt >= t1.dt)<=2
Order By id, dt desc

it has only one limitation. dates must not be completly the same.

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

benjamintb
Starting Member

15 Posts

Posted - 2004-09-01 : 21:26:45

Thanks alot Spirit. I can't believe that works. I'm not sure I understand how it works but it definately does. I would be really interested to know how that works if you're in the mood for some explaining...

Thanks again!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 05:36:41

well i searched here to see who came up with this, and it was corey (seventhnight) so if he can explain it better (probably can) he's welcome

well it works like this:

(Select count(1) from myTable Where id = t1.id and dt >= t1.dt)
gets all the data from myTable where id's match and dt is >= then the one in parent select.
Count(1) gets the count of data returned for each id in parent query. 1 is because of faster returns (no need to return the field, but u could).
<=2 stops the execution when it reaches 2. in other words for each record in parent query the count is evaluated and if it's 2 or less the row is selected else it is not. if u wanted the two min rows you should do (dt <= t1.dt)

so if you have 3 records for the same id and different dates it will get the first two.

i hope this makes sense.

[edit]: i had to draw it on the piece of paper to get it myself :)

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-02 : 08:49:53

Spirit - I think you did a very good job of explaining...

Just my own experience to add:


/*When I start something like this, it usually begins like the following query so that I can see what its going to do.*/

Select 
	id, 
	comment, 
	dt,
	aboveCnt = (Select count(1) from myTable Where id = t1.id and dt >= t1.dt)
From myTable as t1
Order By id, dt desc

/*From there it is a simple logical step to contain the whole thing in a subquery and limit the column*/

Select
	id, 
	comment, 
	dt
From
	(
	Select 
		id, 
		comment, 
		dt,
		aboveCnt = (Select count(1) from myTable Where id = t1.id and dt >= t1.dt)
	From myTable as t1
	Order By id, dt desc
	) as A
Where aboveCnt <= 2

/*But to save the effort of the subquery, we don't even create the column, but use the lookup in the where clause, thus your solution*/

Select 
	id, 
	comment, 
	dt
From myTable as t1
Where (Select count(1) from myTable Where id = t1.id and dt >= t1.dt)<=2
Order By id, dt desc

EDIT: was missing a paren

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 09:00:22

well all i can say is: logic at its most beautiful

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

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2004-09-03 : 12:11:20

If there will be ids with the same exact dates, the query returns just one row for that id, like id = 9913 in the posted sample.
I think this query solves the problem.

SELECT id, comment, dt
From #MyTable t1
WHERE dt IN (SELECT TOP 2 dt FROM #myTable WHERE id = t1.id ORDER BY dt DESC)
ORDER BY id, dt DESC

the problem with this query is that if we want to get just two rows (if any) and we have ids with 3 or more same dates (as first or second MAX) it will return all of them. I'm sure with a little work on it, it will be solved.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-03 : 14:38:09

I think to fix that you can do:


Select 
	id, 
	comment, 
	dt
From myTable as t1
Where (Select count(distinct dt) from myTable Where id = t1.id and dt >= t1.dt)<=2
Order By id, dt desc

Corey

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2004-09-03 : 16:25:12

Corey, It doesnt fix it, because if you have 3 or more equal dt as max with the same id, the query returns all of them.

mervens
Starting Member

6 Posts

Posted - 2004-09-24 : 12:14:34

Hi!
I'm new in this forum and tried your solution for solving the max and max+1 problem.
At first glance and try I thought your solution works but....

I encountered a littlt ugly problem (so far for my used SQL Version only, perhaps!):

If there are more than 2 entrys in the table the comparison (select count(1)...)<=2 fails and
you will not get the two records for the id

Am I right or just totally wrong (and too stupid for SQL

)

Please help me understand because I have an analogous problem that I thought could hav been solved with your approach!

Thanx Michael

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 14:28:02

quote:
If there are more than 2 entrys in the table the comparison (select count(1)...)<=2 fails and you will not get the two records for the id

What do you mean by 'more than 2 entrys'?? You mean that there are duplicates? or more than 2 records? or what?

Show some sample data that demonstrates your question if you can...

Corey

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources