Max over multiply columns

问题:

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?

回答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

回答2:

Such cases can be solved without subqueries with the 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);

With this "setup", the query then reads:

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;

resulting in

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

回答3:

First, your query is working correctly (i.e. in accordance with the SQL standard), in that it's returning the largest value from each field. Just because it's not what you want doesn't mean it's wrong...

To do this without a sub-query, 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 sub-query method is usually faster, particularly for large tables.