Select all colums from a table wher X has its Maximum

From: Björn Wächter <Bjoern.Waechter_at_p3-solutions.de>
Date: Tue, 19 Jul 2005 10:25:19 +0200
Message-ID: <3k3rotFs6e48U1@news.dfncis.de>

Hi all,

is there a way to select all columns from a table where for example comlum W has its maximum?

Table A:

W | X | Y |

 1 | 2 | 5 |
 3 | 3 | 3 |
 6 | 7 | 1 |
 1 | 8 | 2 |

What I do at the moment is:

SELECT * FROM A
WHERE W IN (SELECT MAX(W) FROM A) But is there a way with only one select statment?

Or a little bit more complex:

Table B:

W | X | Y | I |

 1 | 2 | 5 | 1 |
 3 | 3 | 3 | 2 |
 6 | 7 | 1 | 1 |
 2 | 8 | 2 | 2 |
 4 | 2 | 5 | 1 |
 5 | 3 | 3 | 1 |
 7 | 7 | 1 | 3 |
 9 | 8 | 2 | 2 |

SELECT * FROM B
WHERE
W IN (SELECT MAX(W) FROM B GROUP BY I) Where W is unique.

Björn

Dipl.-Ing. Björn Wächter
P3 Solutions GmbH
Dennewartstr. 25-27
D-52068 Aachen
Germany
http://www.p3-solutions.com

tel +49(0)241/9437-417
fax +49(0)241/9437-431 Received on Tue Jul 19 2005 - 03:25:19 CDT