----- Original Message -----
From: "James Lockie" <[EMAIL PROTECTED]>
To: "MySQL Mailing List" <mysql@lists.mysql.com>
Sent: Friday, May 25, 2007 3:11 PM
Subject: do I need 2 selects?
Say I have x rows of 2 columns each.
For example, 2 rows: 'A 10:00pm' and 'A 11:00pm'.
I want to return one 'A' row with the latest time (eg. 'A 11:00pm).
I could do 'select distinct(col1) from table' and then 'select
col1, max( col2 ) where col1=row[0]'?
Is there a way to do it in one select?
That's hard to answer. I have a feeling that this is not your _real_
question, just the essence of a bigger problem. That means that any answer I
give to your specific question may not be very useful in handling the bigger
question.
For example, your ultimate goal may be to produce a report showing the
maximum column 2 value for umpteen different values in column 1. So, given
the following table:
Col1 Col2
A 10:00
A 9:00
A 12:00
A 14:00
A 5:00
B 23:00
B 19:00
B 22:30
C 12:00
You might want a report that says:
A 14:00
B 23:00
C 12:00
But taking your question at face value and assuming that there is only one
unique value in Column 1, you could get what you want with a subquery. I
don't know if you are familiar with them but subqueries are queries within a
query. Subqueries are written within brackets and are always executed first.
The subquery result gets plugged into the outer query and the subquery
result controls the rows obtained by the outer query, which are then
displayed.
You need a subquery that determines the largest value of column 2 for all
rows that contain A in column 1. That query is:
select max(col2)
from table
where col1 = 'A'
That subquery is only ever going to return 1 row, even if there are dozens
of rows that contain the largest col2 value. Therefore, even if the table
had several rows that said "A 11:00" and there were no rows that had an
A value that was greater than 11:00, the max() function ensures that the
value "11:00" is returned by the subquery, even if that value occurred many
times in the table.
The outer query is simply going to say:
select col1, col2
from table
where col2 = (insert subquery here)
In other words, what is the col1 and col2 value on the row that contains the
time returned by the subquery, which is the highest time for an "A" row.
Therefore, the full query will be:
select col1, col2
from table
where col2 =
(select max(col2)
from table
where col1 = 'A')
So, play with that a bit and satisfy yourself that it works. Then, see if
you can figure out the answer to your bigger question on your own, using the
principles of this answer. If not, post again and perhaps someone can help
you with your REAL question ;-)
--
Rhino
--
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
