[SOLVED] Query about a query

[haku]

Sample data:

 

id1 id2 somecolumn
1    2        text
1    3        text
2    4        text
2    5        text
2    6        text

 

I want to subtract one row for each id1, and the row I want to select is for the highest value of id2. So for the above table, I want rows {1, 3, text} and {2, 6, text}.

 

id1 is non-unique, and will occur many times, but id2 will never occur more than once, even in the id1 table.

 

Can someone give me a hand with this? I've been pondering it for a while now, and haven't been able to come up with anything.

[Maq]

Something like this:

 

mysql> select id1, max(id2), somecolumn from testing group by id1;
+------+----------+------------+
| id1  | max(id2) | somecolumn |
+------+----------+------------+
|    1 |        3 | text       | 
|    2 |        6 | text       | 
+------+----------+------------+
2 rows in set (0.00 sec)

 

Although I'm not sure what you mean by:

 

I want to subtract one row for each id1

[PFMaBiSmAd]

To select the actual row with a specific value in the group - http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

[haku]

Maq - I meant that I wanted to select one row for each distinct value in id1, and the row needed to be the row with the highest value of id2. The link PFMaBiSmAd  gave me does exactly that.

 

Thanks PFMaBiSmAd  - that's what I was looking for.