aggregate queries - 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

General SQL Server Forums

New to SQL Server Programming

aggregate queries

Author

Topic

hansalas
Starting Member

14 Posts

Posted - 2006-12-06 : 21:45:05

hi guys,
here is the table structure

__id__|__boxnumber__|__createddate__|
___1__|______78_____|___5/12/06_____|
___2__|______79_____|___26/12/06____|
___3__|______78_____|___15/12/06____|

so here goes,
____________________________________
select boxnumber,max(createddate)
from table
group by boxnumber
------------------------------------
The above query has one problem as I want to display the id as well
but I cant do this obviousely..
------------------------
select boxnumber,max(createddate),id
from table
group by boxnumber
---------------------------------------
Any help possible to solve this..

Thanks fellars

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-12-06 : 22:41:16

One way to do it:

SELECT t.ID, t.boxNumber, (SELECT MAX(createDate) FROM myTable WHERE ID = t.ID and boxnumber=t.boxnumber)) as Max Date
FROM myTable t

hansalas
Starting Member

14 Posts

Posted - 2006-12-06 : 23:01:29

Hi Timmy,
Think something wrong with your queryString.
Lets take boxnumber=78

SELECT t.ID, t.boxNumber, (SELECT MAX(createDate) FROM myTable WHERE ID = t.ID and boxnumber=t.boxnumber)) as Max Date
FROM myTable t
where boxnumber=78

It should only return the tuple with id=3 but it returns both id=1 and id=3..
I wanna my query to return only id=3

THanks again.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-12-06 : 23:14:06

oops.... lets try again:
SELECT t.ID, t.boxNumber, t.CreateDate
FROM myTable t INNER JOIN (SELECT boxNumber, Max(CreateDate) As MaxCreateDate FROM myTable GROUP BY boxNumber) M ON t.BoxNumber = M.boxNumber AND t.CreateDate = M.maxCreateDate

I'm sure there's a simpler way of doing this but I can't remember it.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 01:49:16

Try this

SELECT DISTINCT	q.id,
		q.boxnumber,
		q.createdate
from		yourtablenamehere q
where		q.createdate = (select max(w.createdate) from yourtablenamehere w where w.boxnumber = q.boxnumber)

Peter Larsson
Helsingborg, Sweden

hansalas
Starting Member

14 Posts

Posted - 2006-12-07 : 02:01:49

thanks timmy, it werks..
But the query looks way complex for such a simple question.
If anyone else has a walkaround,do give me a pointer..

Thanks again to all..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 02:39:51

I did. 12 minutes prior to your last post in this thread.

Peter Larsson
Helsingborg, Sweden

hansalas
Starting Member

14 Posts

Posted - 2006-12-07 : 21:50:12

Sorry Peso.
I could swear I didnt see that post the other time.
Well, other than thanks for the solution.
Cheers mate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources