| MAX in query for report [message #89066] |
Tue, 19 August 2003 06:40  |
bjoern
Messages: 2
Registered: July 2003
|
Junior Member |
|
|
Hello,
is there a way to use the function MAX in an query ?
I have 2 fields (version and generation) in my query from which I only want the rows with the greatest values displayed.
Thanks
Bjoern
|
|
|
|
| Re: MAX in query for report [message #89068 is a reply to message #89066] |
Tue, 19 August 2003 08:49  |
Scott Lamb
Messages: 6
Registered: August 2003
|
Junior Member |
|
|
You always use max() in a query; there's no other context to use it. It sounds like you mean to ask if you can use max in a where clause. You can't, but you can accomplish the same thing.
You want a clause that says "where there does not exist a row with a larger version" or similar. (I'm not quite sure what you mean with the two fields. You want two queries, one returning the largest version and the other returning the largest generation? Or are they related somehow...you want a single query returning a row for each generation with the largest version?)
Something like:
select version
from mytable
where not exists (
select 'x'
from mytable larger
where larger.version > mytable.version);
You can also do >=. The difference is in the case where there are two or more rows with the same version. This will return all. With >=, it will return none.
|
|
|
|
| Re: MAX in query for report [message #89139 is a reply to message #89066] |
Thu, 16 October 2003 05:15 |
Bhavin Mehta
Messages: 18
Registered: October 2003
|
Junior Member |
|
|
Bjoern,
MAX is a group function.
If you want maximum of values in your 'version' column and you want maximum of values in your 'generation' column, I don't think there should be any issue.
If you want maximum of values of version and generation for each row,use GREATEST(version,generation) in your query. I am sure, it will work.
Please send your exact query, if this does not solve your problem.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
