Selecting records with a MAX function

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)

Selecting records with a MAX function

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-17 : 21:25:35

Melissa writes "I have two tables that I'm trying to select data from. I need records with a particular status and the most recent date. If they are the most recent date, but don't have the right status, I don't want them at all.


ReportName 	ReportNum    ReportID             Date		     Status
____________________________________________________________________________
ReportBlah 	FLA4827402	29	2002-03-01 14:51:42.770		P	
ReportBlah 	FLA4827402	29	2002-03-08 11:35:43.890		R
ReportBlah 	FLA4827402	29	2002-03-08 11:39:25.327		P
ReportBlah 	FLA4827402	29	2002-03-08 11:47:26.040		P
ReportBlah 	FLA4827402	29	2002-03-08 11:49:14.987		R
ReportBlah 	FLA4827402	30	2002-03-01 14:57:37.640		P
ReportBlah 	FLA4827402	30	2002-03-08 13:43:20.510		R	
ReportBlah 	FLA4827402	49	2002-04-01 13:41:02.863		R	
ReportBlah 	FLA4827402	49	2002-04-11 14:33:13.760		O

It's a result of the query
(SELECT vchUserReportName, vchStateID, tblTransactions.intMasterID, MAX(datTransaction) AS 'Date', chrStatus
FROM tblTransactions, tblMasterData
WHERE tblMasterData.intMasterID = tblTransactions.intMasterID
GROUP BY vchUserReportName, vchStateID, tblTransactions.intMasterID, datTransaction, chrStatus
ORDER BY tblTransactions.intMasterID, datTransaction)

Anyway, as you can see, there are four records with an ID of 29 and a status of P and R, but the only ONE that I need is the one with the latest date. On the other hand, there are two records with an ID of 49 and I don't want either of those because the most recent record does not have a status of R.

So far I haven't been able to find the right Query that will give me the results I need. Can anyone help?

Thanks,
Melissa "

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-17 : 21:57:51

Melissa,

For a start there is no WHERE clasue for the "Status".. Add one.
Remove the "datTransaction" from the Group By Clause..(You don't need to group the field if it is used in a aggregate function)

And be particularly picking.. try and use the JOIN syntax instead of the CROSS JOIN - WHERE..

So your query should look something like this..


SELECT vchUserReportName, vchStateID, T.intMasterID, MAX(datTransaction) AS 'Date', chrStatus 
FROM tblTransactions T
INNER JOIN tblMasterData M ON M.intMasterID = T.intMasterID 
WHERE chrStatus = 'R'
GROUP BY vchUserReportName, vchStateID, T.intMasterID, chrStatus 
ORDER BY T.intMasterID, datTransaction

DavidM

Tomorrow is the same day as Today was the day before.

ozonic
Starting Member

10 Posts

Posted - 2002-04-25 : 07:53:31

SELECT TOP 100 PERCENT AA.client_mastid AS Expr1, AA.User_ID, AA.eppaccount, AA.name AS client_name, BB.LastName, BB.FirstName AS Expr7, BB.Cell,
BB.Tel_Number, BB.Email AS Expr10, BB.Tel_Code AS Expr11, CC.clientid, CC.CommentDate, CC.Comment, MAX(CC.Logid) AS logid
FROM epp_client.dbo.client_mast AA INNER JOIN
dbo.Webuser_mast BB ON AA.User_ID = BB.User_id LEFT OUTER JOIN
epp_client.dbo.visitlog CC ON AA.client_mastid = CC.clientid
GROUP BY AA.client_mastid, CC.Logid, CC.Comment, AA.User_ID, AA.eppaccount, AA.name, BB.LastName, BB.FirstName, BB.Cell, BB.Tel_Number, BB.Email,
BB.Tel_Code, CC.clientid, CC.CommentDate
ORDER BY AA.client_mastid, CC.CommentDate DESC

ozonic
Starting Member

10 Posts

Posted - 2002-04-25 : 07:55:09

In the above post I posted my sql .

I have the same quirk. Yet tell me theres something fishy going on here . I also get duplicates back. Even after specifying I want MAX.

It has no effect at all. Please help

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-25 : 08:25:57

If you group by the column you want aggregated, then each MAX will be the aggregate of one row. So take CC.Logid out of the GROUP BY!

ozonic
Starting Member

10 Posts

Posted - 2002-04-26 : 03:00:01

SELECT TOP 100 PERCENT AA.client_mastid, AA.User_ID, AA.eppaccount, AA.name AS client_name, BB.LastName, BB.FirstName AS Expr7, BB.Cell,
BB.Tel_Number, BB.Email AS Expr10, BB.Tel_Code AS Expr11, CC.clientid, CC.CommentDate, CC.Comment, MAX(CC.Logid) AS Expr1
FROM epp_client.dbo.client_mast AA INNER JOIN
dbo.Webuser_mast BB ON AA.User_ID = BB.User_id LEFT OUTER JOIN
epp_client.dbo.visitlog CC ON AA.client_mastid = CC.clientid
GROUP BY AA.client_mastid, AA.User_ID, AA.eppaccount, AA.name, AA.name, BB.LastName, BB.FirstName, BB.Cell, BB.Tel_Number, BB.Email, BB.Tel_Code,
CC.clientid, CC.CommentDate, CC.Comment
ORDER BY AA.client_mastid, CC.CommentDate DESC

logid taken out of the group_by and still returning multiple logs per client. As a matter of fact , each return per client , is differant , not the max per row even.

I must be doing something wrong , can you spot the error?

ozonic
Starting Member

10 Posts

Posted - 2002-04-26 : 03:04:28

Here is the result set (just a few fields)

----------------------------------------------------------------------
client_mastid | User_id | eppaccount | Expr1(logid)
----------------------------------------------------------------------
16 62 NEW000 2
16 62 NEW000 3

As you can see not even the max(logid) is being returned. Please help

ozonic
Starting Member

10 Posts

Posted - 2002-04-26 : 07:18:05

Sorted this out thanks.

Special thanks to YELLOWBUG who had the correct solution.

stevep
Starting Member

17 Posts

Posted - 2003-01-13 : 19:27:40

Is yellowbug still around coz I've got the same problem and am running out of hair to pull out.

I have a table of deliverable versions, multiple versions for the same deliverable. My query to return the most recent version of each deliverable:

SELECT dvDeliverable, dvVersion, Max(dvSubmittedDate)
FROM deliverableVersions
GROUP BY dvDeliverable, dvVersion;

Definitely do not have the MAXed item in the GROUP BY clause. But still get multiple values for each version. I'd guess it's because the GROUP BY contains the dvVersion field, which changes for each version (hence always end up grouping in sets of 1). But when I remove that from the GROUP BY the statement becomes invalid.

Steve

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-14 : 03:32:07

quote:

Is yellowbug still around coz I've got the same problem and am running out of hair to pull out.

I have a table of deliverable versions, multiple versions for the same deliverable. My query to return the most recent version of each deliverable:

SELECT dvDeliverable, dvVersion, Max(dvSubmittedDate)
FROM deliverableVersions
GROUP BY dvDeliverable, dvVersion;

Definitely do not have the MAXed item in the GROUP BY clause. But still get multiple values for each version. I'd guess it's because the GROUP BY contains the dvVersion field, which changes for each version (hence always end up grouping in sets of 1). But when I remove that from the GROUP BY the statement becomes invalid.

Steve

I think that your query doesn't quite do enough. You need something like

SELECT
dvDeliverable, dvVersion, max (dvsubmitteddate)
from
deliverableVersions v
inner join
(
SELECT dvDeliverable, Max(dvSubmittedDate) as lastdate
FROM deliverableVersions
GROUP BY dvDeliverable
) latest
on
latest.dvdeliverable = v.dvdeliverable and
latest.lastdate = v.dvsubmitteddate

group by dvDeliverable, dvVersion

But I'm sure that there's a more efficient way of doing this. I just don't know what it is.

-------
Moo.

stevep
Starting Member

17 Posts

Posted - 2003-01-15 : 01:37:48

Moo,

Ta for input. Your use of "latest" got me wondering and I've come up with a slightly tangential solution that seems to work. Because MAX got into trouble with GROUP BY on all the other fields, I changed them all the LAST. Since the most recent update will always be the last record(?) this seems to work very well:

SELECT LAST(dvDeliverable), LAST(dvVersion), LAST(dvSubmittedDate)
FROM deliverableVersions
GROUP BY dvDeliverable

Now unless I'm missing something about LAST these should all point to the same, last, record. Which is what I want.

I'll save the battle with MAX for when I need it.

Steve

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-01-15 : 02:24:56

LAST()? Am I the only guy who doesn't have an entry in BOL for the LAST() function? I'm not familiar with this. Are you perhaps not using SQL Server?

If I were you, I would do some MAJOR investigation into what this function does EXACTLY because the concept of the LAST record is a bit bizarre. You can have a last record in a query resultset (preferably sorted). And you can have a LAST inserted record based on a MAX create date (if you're tracking one), or MAX on the IDENTITY field. But none of these suggests that LAST would apply to the "most recently updated" record (unless you're doing a MAX on an "updated datetime" field that you're manually maintaining).

This idea of a LAST function in a SQL statement just sounds to me either really dangerous, or some variation of a MAX command.

------------------------------------------------------
The more you know, the more you know you don't know.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-15 : 02:29:21

Last() and First() are functions in access.

Edited by - ValterBorges on 01/15/2003 02:29:36

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-01-15 : 02:34:41

quote:
SELECT dvDeliverable, dvVersion, Max(dvSubmittedDate)
FROM deliverableVersions
GROUP BY dvDeliverable, dvVersion;

To address your original question, please define what dvDeliverable and dvVersion are. Perhaps you could provide us with some sample data (preferably in the form of CREATE TABLE and INSERT statements) for us to test our solutions with. And please also provide the corresponding desired resultset.

Your SQL Statement here appears to be just fine, but you say that you are getting multipl values for each "version". Do you mean that for each combination of dvDeliverable and dvVersion you are getting multiple output rows? That seems impossible. Do you mean that you are getting duplicate occurrences of dvVersion? Such as you would expect for every distinct occurrence of dvDeliverable that is paired with it?

Or, as your "LAST" post may have been hinting at, are you trying to get the dvDeliverable and dvVersion of just the one record that has the highest Submitted Date? In which case the answer would look something like:

SELECT dvDeliverable, dvVersion
FROM DeliverableVersions
WHERE dvSubmittedDate = (SELECT max(dvSubmittedDate) FROM DeliverableVersions)

------------------------------------------------------
The more you know, the more you know you don't know.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-01-15 : 02:36:58

quote:

Last() and First() are functions in access.

Valter, I suspected that was the case, but haven't used Access in quite a while. How is Last() defined?

------------------------------------------------------
The more you know, the more you know you don't know.

stevep
Starting Member

17 Posts

Posted - 2003-01-15 : 03:54:47

All,

dang! spotted as the closet access user. Basically I'm in prototyping mode (in access) before moving to SQL - honest!

Sample data to see whether MAX can do it:


deliverables:
ID           Name            Description
--------------------------------------------------
1            user spec       blah blah
2            admin spec      blah blah
3            upload API      blah blah

deliverableVersions:
dvID         dvDdeliverable  dvVersion      dvSubmittedDate
----------------------------------------------------------------
1            1               1.0            12 january 2003
2            1               1.1            13 january 2003
3            2               0.5            14 january 2003
4            1               2.0            15 january 2003

[dvVersion is a text field]

What I'm after is details of each deliverable, and its most recent version.
But if the deliverable has no version I still want it listed. Hence with the above data, the required result set is:


ID    Name          Version       Submitted Date
-------------------------------------------------
1     user spec       2.0            15 january 2003
2     admin spec      0.5            14 january 2003
3     upload API      [null]         [null]

I can manage the query to get all deliverables listed even without a version IF I can join to a table that simply lists the latest versions of each deliverable.


SELECT dvDeliverable, dvVersion, Max(dvSubmittedDate) 
FROM deliverableVersions 
GROUP BY dvDeliverable, dvVersion

returns all versions of all deliverables. My guess because of the GROUP BY on every field making each group a group of one - the only field that is consistent across versions is dvDeliverable.


SELECT last(dvDeliverable), last(dvVersion), last(dvSubmittedDate) 
FROM deliverableVersions 
GROUP BY dvDeliverable

works fine since it allows me to group on only the field that makes sense.

I did not know LAST was non-standard SQL. So if there is a way to do this 'properly' I'm keen to know about it since I will be moving to SQL eventually. I can post the full query using LAST if interested.

tia

Steve

Edited by - stevep on 01/15/2003 03:56:49

Edited by - stevep on 01/15/2003 04:02:28

Edited by - stevep on 01/15/2003 04:03:56

Edited by - stevep on 01/15/2003 04:05:29

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-01-15 : 04:38:41

quote:


SELECT last(dvDeliverable), last(dvVersion), last(dvSubmittedDate) 
FROM deliverableVersions 
GROUP BY dvDeliverable

Well, as it says in the help file, if you want LAST to return values from a particular record* rather than an arbitrary one, you really need an ORDER BY clause.

* I'm allowed to say 'record' because it's Access

mr_mist
Grunnio

1870 Posts

Posted - 2003-01-15 : 04:57:57

This is the output you were after without the last function...

set nocount on
create table #deliv
([id] int null, [name] varchar (20) null, [Description] varchar (20) null)
create table #versions
(dvid int null, dvdeliverable int null, dvversion varchar (10) null, dvdate datetime)
insert into #deliv values (1,'user spec','blah blah')
insert into #deliv values (2,'admin spec','blah blah')
insert into #deliv values (3,'upload API','blah blah')

insert into #versions values (1,1,'1.0','20030112')
insert into #versions values (2,1,'1.1','20030113')
insert into #versions values (3,2,'0.5','20030114')
insert into #versions values (4,1,'2.0','20030115')

Select [id],[name], [description], dvversion, dvdate from #deliv
left outer join
(
select #versions.dvdeliverable, dvversion, dvdate from #versions
inner join
(select dvdeliverable, max (dvdate) as latest from #versions group by dvdeliverable) j
on j.dvdeliverable = #versions.dvdeliverable and j.latest = #versions.dvdate
) vers
on vers.dvdeliverable = #deliv.[id]
order by [id]

drop table #deliv
drop table #versions

P.S. Moo isn't my name, it's the noise cows make. I have it in my sig because I have a slight cow fixation.

-------
Moo.

Edited by - mr_mist on 01/15/2003 05:04:43

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources