Show only max in query

[mutiger83]

I know this is a simple fix, but I just can't seem to get it work this AM

I need a Query to show only the highest (max) record. So I have records as follows for example in a talbe I am querying...

TABLE1
Descript ID
test1 5
test1 6
test2 7
test1 8
test3 9

QUERY NEEDS TO SHOW:
Descript ID
test3 9

Right now it keeps doing something like this:
Descript ID
test1 8
test2 7
test3 9

Any thoughts on how to get what I am looking for?

Thanks in advance for your help!

 

[belovedcej]

Check out your sql - you don't want the group by clause.

 

[lars7]

See if this works for you:

SELECT TABLE1.Descript, Max(TABLE1.ID) AS MaxOfID
FROM TABLE1
GROUP BY TABLE1.Descript
HAVING (((TABLE1.Descript)>"max"));

 

[mutiger83]

When I remove all the group to in the SQL I get an error that says "You tried to execute a query that does not include the specified [field 1 in query] as part of an aggregate function." Any thougts on what is causing this?

 

[mutiger83]

lars7...

I have the following SQL now...

SELECT CO.PODescrip, Max(CO.Revision) AS MaxOfRevision
FROM CO
GROUP BY CO.PODescrip
HAVING (((CO.PODescrip)>"max"));

However it returns nothing? :-/

My Data in the table is as follows:
PODescrip Revison
test1 1
test2 2
test3 3

I need to see:
test3 3

Thanks very much for your help!!!!!

 

[PHV]

SELECT A.PODescrip, A.Revision
FROM CO AS A INNER JOIN (
SELECT PODescrip, Max(Revision) AS MaxOfRevision FROM CO GROUP BY PODescrip
) AS B ON A.PODescrip = B.PODescrip AND A.Revision = B.MaxOfRevision

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi mutiger83,

It was ok at work, I'm at home now and I created a table like your's and tried it but your right it does nothing, sorry. :~/

 

[mutiger83]

Thanks for your help! The SQL your provided returns the highest revison tied to each different different PODescrip. So it shows...given my example above...

test1 1
test2 2
test3 3

I just want it to show the record tied to the highest revision number...so...

test3 3

since 3 is the highest revision number in the table

Thanks for your help!

 

[PHV]

Either:
SELECT A.PODescrip, A.Revision
FROM CO AS A INNER JOIN (
SELECT Max(Revision) AS MaxOfRevision FROM CO
) AS B ON A.Revision = B.MaxOfRevision

Or:
SELECT PODescrip, Revision
FROM CO
WHERE Revision = (SELECT Max(Revision) FROM CO)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mutiger83]

You are awesome!!!! The first one worked perfectly -- thank you so much for your help!

Best Regards!