MAX

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am looking for a query that will return all the entries where a certain
field has its max value.
For example:
I am interested in all the values where version is at its MAX for a certain id

so
if my table is:
id idea status version
1 Apple open 1
1 Banana close 2
2 Chair close 1
3 Table open 3
3 Flower open 4
4 Keyboard open 1

I would expect to get:
1 Banana close 2
2 Chair close 1
3 Flower open 4
4 Keyboard open 1

Any idea?
Thanks,
Dan
 
Create a query that selects the id and max(version)

Create a second query that joins the table and the query above.
Select the items that you need.

Cheers,
Jason Lepack
 
Another option would be:

SELECT ID, Idea, Status, Version
FROM yourTable T
WHERE Version = (SELECT MAX(Version)
FROM yourTable
WHERE ID = T.ID)

HTH
Dale
 
I need to use subqueries more often...

Another option would be:

SELECT ID, Idea, Status, Version
FROM yourTable T
WHERE Version = (SELECT MAX(Version)
FROM yourTable
WHERE ID = T.ID)

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.








- Show quoted text -
Click to expand...
 
Thats funny Jason. In the past, I've almost always done it the way you
described, and recently decided that I needed to try doing it this way
because I'm constantly seeing Steele and Spencer do it this way. I still
have not tested it in most of my applications. Probably should one may have
an advantage over the other in certain circumstances.

;-)
 
The stacked query will usually be faster then the coordinated subquery
especially for large recordsets.

This query runs the subquery ONE time, since the subquery does not reference
the main query
SELECT ID, Idea, Status, Version
FROM yourTable T
WHERE Version = (SELECT MAX(Version)
FROM yourTable)

This query runs the subquery one time for each record in the main query
since it does reference the main query.
SELECT ID, Idea, Status, Version
FROM yourTable T
WHERE Version = (SELECT MAX(Version)
FROM yourTable
WHERE ID = T.ID)

This query uses the subquery in a join and runs the subquery one time
SELECT ID, Idea, Status, Version
FROM yourTable T INNER JOIN
(SELECT ID, MAX(Version) as TheMax
FROM yourTable
GROUP BY ID) as T2
ON T.ID = T2.ID and T.Version = T2.Version)

Version 1 and 2 are updatable. Version 3 will probably not be updatable.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John,

That confirms my thoughts on the subquery execution.

Another little bit of knowledge to tuck under my hat (the updateable
bit), much appreciated John.

Cheers,
Jason Lepack
 
John,

The updateable note is really important, thanks for that tidbit

Can you define "stacked query" and "coordinated subquery" for me? Which is
which?

Versions 2 and 3 should get you the same result set. Which of those would
be faster (#3)?
 
Stacked = Subquery in From Clause
Coordinated (I believe this should be Correlated) = subquery in where
clause.
 
Number 3 is faster. If correctly built it will run at the same speed as
using a saved query inside another query.

Coordinated subquery is a query within a query where the subquery refers to
data in the main query (that is it coordinates what it is looking for with
the main query). This always (almost always) involves a where clause in the
subquery looking at a field value in the main query.

Stacked query is my nomenclature for using a saved query inside another
query.

Version 4 (stacked query)

qGetMax (saved query)
SELECT ID, MAX(Version) as TheMax
FROM yourTable
GROUP BY ID

SELECT ID, Idea, Status, Version
FROM yourTable T INNER JOIN qGetMax as T2
ON T.ID = T2.ID and T.Version = T2.Version

The advantage of a "stacked" query is that you can use field and table names
that have spaces and special characters in them and parameters. My query
example #3 would not be able to use any field, table, or parameter reference
that required square brackets (at least with JET SQL).

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Yes, you are right I did mean correlated. I have a bad habit of saying
coordinated when I mean to say correlated.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top