Maximum column overshoot

Question

I have a little problem:

My table has the following rows:

Persist

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 make a choice like:

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

I get this output:

ID    Date         Histroy
1     02.01.2008   2

This is not true because no 02.01.2008/2(Date / Histroy)

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

Answer 1

Try it. It will take all records with ID = 1, order them by date (from last to oldest), then by Histroy (descending), and then return to you the first record (that is, with the most recent date and the highest history in that 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 attributes first ROWNUMand then ORDERs

Answer 2

keep (dense_rank first... "idiom":

create table tq84_pershist (
  id      number,
  dt      date,
  histroy number
);

insert into tq84_pershist values (1, date '2008-01-01', 0);
insert into tq84_pershist values (1, date '2008-01-01', 1);
insert into tq84_pershist values (1, date '2008-01-01', 2);
insert into tq84_pershist values (1, date '2008-01-02', 0);
insert into tq84_pershist values (1, date '2008-01-02', 1);

"" :

select max(dt     ) keep (dense_rank first order by dt desc, histroy desc) dt,
       max(histroy) keep (dense_rank first order by dt desc, histroy desc) histroy
from 
tq84_PersHist;

DT          HISTROY
-------- ----------
02.01.08          1

Answer 3

At first, your query works correctly (i.e., in accordance with the SQL standard), since it returns the largest value from each field. Just because it is not what you want does not mean that it is wrong ...

To do this without a subquery, you need to use analytic functions:

SELECT distinct first_value(date) over (order by date desc, history desc),
                first_value(history) over  (order by date desc, history desc) 
FROM PersHist

However, the subquery method is usually faster, especially for large tables.