Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Select all colums from a table wher X has its Maximum
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
tel +49(0)241/9437-417
fax +49(0)241/9437-431
Received on Tue Jul 19 2005 - 03:25:19 CDT