GROUP BY 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 2000 Forums

Transact-SQL (2000)

GROUP BY Query

Author

Topic

ChetShah
Starting Member

37 Posts

Posted - 2002-01-07 : 04:24:40

I'm running the following GroupBY query:

Select LiabilityID,MAX(DateTimeChanged)
from tblliabilitystatuses
where convert(varchar(10),LiabilityID) like '32%'
GROUP BY LiabilityID

The results i get are following:

LiabilityID
----------- ---------------------------
32000 Nov 15 2001 11:05AM
32001 Nov 3 2001 12:00AM
32002 Nov 29 2001 9:17AM
32003 Dec 28 2001 11:13AM
32004 Dec 6 2001 10:44AM
32005 Dec 10 2001 3:07PM
32006 Nov 3 2001 12:00AM
32007 Jan 3 2002 1:36PM
32008 Nov 3 2001 12:00AM
32009 Nov 3 2001 12:00AM
32010 Dec 10 2001 10:55AM

However when you add another column to the list :

Select LiabilityID,MAX(DateTimeChanged),StatusIDTo
from tblliabilitystatuses
where convert(varchar(10),LiabilityID) like '32%'
GROUP BY LiabilityID,StatusIDTo

The following results are shown:

LiabilityID StatusIDTo
----------- --------------------------- -----------
32000 Jul 16 2001 12:00AM 1
32000 Jul 10 2001 12:00AM 2
32000 Jul 9 2001 12:00AM 3
32000 Nov 15 2001 11:05AM 21

The problem here is that the Date column is ungrouped. The result i require is the Maximum Date hence

LiabilityID StatusIDTo
----------- --------------------------- -----------
32000 Nov 15 2001 11:05AM 21

Rafiq
Starting Member

25 Posts

Posted - 2002-01-07 : 05:24:04

quote:

However when you add another column to the list :

Select LiabilityID,MAX(DateTimeChanged),StatusIDTo
from tblliabilitystatuses
where convert(varchar(10),LiabilityID) like '32%'
GROUP BY LiabilityID,StatusIDTo

The following results are shown:

LiabilityID StatusIDTo
----------- --------------------------- -----------
32000 Jul 16 2001 12:00AM 1
32000 Jul 10 2001 12:00AM 2
32000 Jul 9 2001 12:00AM 3
32000 Nov 15 2001 11:05AM 21

The problem here is that the Date column is ungrouped. The result i require is the Maximum Date hence

LiabilityID StatusIDTo
----------- --------------------------- -----------
32000 Nov 15 2001 11:05AM 21

Hi,

When you change the query like this,

Select LiabilityID, MAX(DateTimeChanged), MAX(StatusIDTo)
From tblliabilitystatuses
Where convert(varchar(10),LiabilityID) like '32%'
GROUP BY LiabilityID

I think it will work as per your expectation.

Regards,

Rafiq
------------------------------------
If you think, you can do anything...

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-01-07 : 05:56:00

If you want the StatusIdTo for the max DataTimeChanged record then Rafiq's solution will only work if the max StatusIDTo is on the same row as the max DateTimeChanged.

If this cannot be guaranteed then you need to find the max date and then pick up the StatusIDTo for that date,using a correlated sub-query...

create table test (LiabilityID varchar(6),DateTimeChanged datetime,StatusIdTo varchar(2))

insert into test values
(32000,'7/16/2001',1) 
insert into test values
(32000,'7/10/2001',2) 
insert into test values
(32000,'7/9/2001',3) 
insert into test values
(32000,'6/15/2001',21)
insert into test values
(32002,'11/29/2001',2) 
insert into test values
(32003,'12/28/2001',1)
insert into test values
(32004,'12/6/2001',3)
insert into test values
(32005,'12/10/2001',2)
insert into test values
(32006,'11/3/2001',1)
insert into test values
(32007,'1/3/2002',1)

select inner_table.LiabilityID,maxDateTimeChanged,inner_table.StatusIDTo
from 
test inner_table,
(select LiabilityID,max(DateTimeChanged) as maxDateTimeChanged 
from test
group by LiabilityID) outer_table 
where inner_table.LiabilityID=outer_table.LiabilityID 
and inner_table.DateTimeChanged=outer_table.maxDateTimeChanged

============
The Dabbler!

Edited by - davidpardoe on 01/07/2002 08:19:44

Rafiq
Starting Member

25 Posts

Posted - 2002-01-07 : 06:41:25

quote:

If you wan the StatusIdTo for the max DataTimeChnaged record then Rafiq's solution will only work if the max StatusIDTo is on the same row as the max DateTimeChanged.

Hi David,

Your statements is wrong. check it again.

regards,

Rafiq
------------------------------------
If you think, you can do anything...

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-07 : 06:49:11

Depends if the requirement is for the max DataTimeChanged and max StatusIDTo for the group or the StatusIDTo for the rec with the max DataTimeChanged for the group.

The question sounds like it wants the two max values, if so Rafiq was correct.
otherwise could be just

select LiabilityID,DateTimeChanged,StatusIDTo
from test t1
where DateTimeChanged = (select max(t2.DateTimeChanged) from test t2 where t1.LiabilityID = t2.LiabilityID)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-01-07 : 06:49:56

OK - just to double check what I think the requirement is:-

quote:

LiabilityID StatusIDTo
----------- --------------------------- -----------
32000 Jul 16 2001 12:00AM 1
32000 Jul 10 2001 12:00AM 2
32000 Jul 9 2001 12:00AM 3
32000 Nov 15 2001 11:05AM 21

The problem here is that the Date column is ungrouped. The result i require is the Maximum Date hence

LiabilityID StatusIDTo
----------- --------------------------- -----------
32000 Nov 15 2001 11:05AM 21

Rafiq - your solution would return the max date (ie. Nov 15) and the max status (ie. 21) as specified above.

If, however, the data was...

LiabilityID StatusIDTo
----------- --------------------------- -----------
32000 Jul 16 2001 12:00AM 21
32000 Jul 10 2001 12:00AM 2
32000 Jul 9 2001 12:00AM 3
32000 Nov 15 2001 11:05AM 1

...then your query would return the same result.

If the requirement in this situation is to return the status for the max date (ie. 1) then my solution will work.

============
The Dabbler!

PS. I don't think my statement was wrong rather not well explained!

Edited by - davidpardoe on 01/07/2002 06:52:48

Nazim
A custom title

1408 Posts

Posted - 2002-01-07 : 07:38:21

I fully endorse David's point.

i think Chetshah is looking for a query which will give the value of each LiabilityId with StatusID where the Row contains the Maximum DateTimeChanged.

Follow this link too

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11707

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/08/2002 02:56:03

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-07 : 17:50:35

OR, does ChetShah really want this? (no correlation, simple subquery)

Select LiabilityID, DateTimeChanged, StatusIDTo
from tblliabilitystatuses
where convert(varchar(10),LiabilityID) like '32%'
AND DateTimeChanged = (SELECT max(DateTimeChanged) FROM tblliabilitystatuses)

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

Edited by - AjarnMark on 01/07/2002 17:51:31

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-01-08 : 04:47:35

I guess we will all have to wait and see if ChetShah replies.

We'll find out who has the valuable skill of deciphering client requirements without all the detail!!

============
The Dabbler!

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-01-08 : 05:11:17

quote:

We'll find out who has the valuable skill of deciphering client requirements without all the detail!!

Yes, then we will find the Holy Grail and the woman that can read street maps

Damian

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-08 : 06:09:55

Looking at the data I would guess that this is a series of status changes and he wants the latest.
'I would guess' is the obvious danger here - but then we don't get paid for this and don't get the sack (the questioner gets all of that).

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-01-08 : 06:29:11

We could go round this for ages but would the status necessarily get higher and higher as it changes???

Please oh please ChetShah tell us the truth