Home » SQL & PL/SQL » SQL & PL/SQL » One record per id (for max(value) and min(date))
One record per id [message #284384] Thu, 29 November 2007 08:32 Go to next message
DiaEcho
Messages: 6
Registered: November 2007
Junior Member
Hi,

my table looks like:
http://home.arcor.de/hiddendelta/pub/SikwxgmhfjhGnsFOyrJqeMrwU/db.png

The result should contain only the id and oid.
And builded by the following rules:
- Each id have to be unique
- The greatest value is the crucial factor
- By equal values the smallest date decides

So the result have to be:
http://home.arcor.de/hiddendelta/pub/SikwxgmhfjhGnsFOyrJqeMrwU/db_res.png

Thanks in advance, DiaEcho.
Re: One record per id [message #284389 is a reply to message #284384] Thu, 29 November 2007 08:55 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
There are several ways to do this. I chose the analytic function ROW_NUMBER (look it up in the manuals):
SQL> WITH yourtable AS
  2  (
  3    SELECT 1 id,10 oid, 1 value, TO_DATE('01/01/2007', 'dd/mm/yyyy') thedate

  4    FROM dual
  5    UNION ALL
  6    SELECT 1 id,20 oid, 2 value, TO_DATE('02/01/2007', 'dd/mm/yyyy') thedate

  7    FROM dual
  8    UNION ALL
  9    SELECT 2 id,10 oid, 1 value, TO_DATE('03/01/2007', 'dd/mm/yyyy') thedate

 10    FROM dual
 11    UNION ALL
 12    SELECT 2 id,20 oid, 1 value, TO_DATE('04/01/2007', 'dd/mm/yyyy') thedate

 13    FROM dual
 14    UNION ALL
 15    SELECT 3 id,10 oid, 3 value, TO_DATE('05/01/2007', 'dd/mm/yyyy') thedate

 16    FROM dual
 17  )
 18  SELECT id
 19       , oid
 20  FROM   ( SELECT id
 21                , oid
 22                , row_number() over ( partition by id
 23                                      order by value desc, thedate asc) rn
 24           FROM yourtable
 25           order by value desc, thedate asc
 26         )
 27  where  rn= 1
 28  order by id
 29  /

        ID        OID
---------- ----------
         1         20
         2         10
         3         10

MHE
Re: One record per id [message #284401 is a reply to message #284384] Thu, 29 November 2007 09:26 Go to previous messageGo to next message
DiaEcho
Messages: 6
Registered: November 2007
Junior Member
Simply delightful Shocked

With best thanks, DiaEcho.
Re: One record per id [message #288516 is a reply to message #284384] Mon, 17 December 2007 22:14 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
This works too ...

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 17 23:09:25 2007

WITH yourtable AS
(
  SELECT 1 id,10 oid, 1 value, TO_DATE('01/01/2007', 'dd/mm/yyyy') thedate
  FROM dual
  UNION ALL
  SELECT 1 id,20 oid, 2 value, TO_DATE('02/01/2007', 'dd/mm/yyyy') thedate
  FROM dual
  UNION ALL
  SELECT 2 id,10 oid, 1 value, TO_DATE('03/01/2007', 'dd/mm/yyyy') thedate
  FROM dual
  UNION ALL
  SELECT 2 id,20 oid, 1 value, TO_DATE('04/01/2007', 'dd/mm/yyyy') thedate
  FROM dual
  UNION ALL
  SELECT 3 id,10 oid, 3 value, TO_DATE('05/01/2007', 'dd/mm/yyyy') thedate
  FROM dual
)
select
  id ,
  min(oid) keep (dense_rank first order by value desc, thedate asc ) as oid
from yourtable
group by id ;

        ID        OID
---------- ----------
         1         20
         2         10
         3         10

3 rows selected.


--
Joe Fuda
SQL Snippets
Previous Topic: to_number built in
Next Topic: How to define and use procedures
Goto Forum:
  


Current Time: Tue Dec 06 14:17:51 CST 2016

Total time taken to generate the page: 0.06498 seconds