Max over multiply columns

Question

I've got a little problem:

In my table I have these rows:

PersHist:

ID    Date         Histroy
1     01.01.2008   0
1     01.01.2008   1
1     01.01.2008   2
1     02.01.2008   0
1     02.01.2008   1

Now when I do a select like:

SELECT max(date), max(Histroy) 
FROM PersHist
WHERE ID = 1

I'm getting this output:

ID    Date         Histroy
1     02.01.2008   2

This is false because there is no 02.01.2008/2 (Date/Histroy)

Is it possible to write a simple SQL that will get me 02.01.2008/1 without writing subqueries?

Answer 1

Try this. It will take all entries with ID=1, order them by Date (latest to oldest) and then by Histroy (descending) and then return to you the first entry (i.e. with latest Date and highest Histroy within this Date).

SELECT * FROM 
(SELECT ID, Date, Histroy
 FROM PersHist
 WHERE ID = 1
 ORDER BY Date DESC, Histroy DESC)
WHERE ROWNUM = 1

Unfortunately, it does not work without a subquery. Oracle first attributes the ROWNUM and then ORDERs