CODEWITHSUNDEEP
sqloracle-databaseunique
nutella_eater
nutella_eater

Reputation: 3582

Find last record in DB for each repeated field(Oracle query)

Hy guys. I have a query:

select *
  from stat.cause_code_descriptions 
 INNER JOIN stat.stat_dial 
    on stat.cause_code_descriptions.CODE = stat.stat_dial.cause
 where called like '%3623.1348' 
   and begdt > sysdate-500 
   and rownum <=100 
 order by begdt desc

This query returns something like that 

login     code     called        begdtu(unix timestamp)

oeanwel     4   VLAN:3623.1348  1336383493
oe192034    0   VLAN:3623.1348  1336382883
oe192034    2   VLAN:3623.1348  1336382640
oe192034    45  VLAN:3623.1348  1336380257
oeanwel     4   VLAN:3623.1348  1336379883
oe220850    20  VLAN:3623.1348  1336378666
oe194752    4   VLAN:3623.1348  1336378507
oeanna2510  45  VLAN:3623.1348  1336377516
oeanwel     4   VLAN:3623.1348  1336376273
oe237185    45  VLAN:3623.1348  1336374506
oe237185    4   VLAN:3623.1348  1336372662
oe237185    3   VLAN:3623.1348  1336370819
oe239364    3   VLAN:3623.1348  1336367329
oeanna2510  45  VLAN:3623.1348  1336366115

What I'm looking for is to return last(freshest) record of any login. For not repeated records my query works well, but for oe192034 and oe237185 logins it shows all records. I tried group by and distinct but it doesn't work. Please help

Upvotes: 1

Views: 105

Answers (3)

dnoeth
dnoeth

Reputation: 60482

You need a window function, ROW_NUMBER:

select * 
from 
 (
   select ccd.*, sd.*, row_number() over (partition by login order by begdtu desc) rn
   from stat.cause_code_descriptions ccd
   INNER JOIN stat.stat_dial sd
      on ccd.CODE = sd.cause
   where called like '%3623.1348' 
 ) dt
where rn = 1 
order by begdt desc

Upvotes: 1

Srini V
Srini V

Reputation: 11375

Try this:

SELECT
      LOGIN,
      CODE,
      CALLED,
      BEGDTU
FROM
      (SELECT
            LOGIN,
            CODE,
            CALLED,
            BEGDTU,
            FIRST_VALUE ( LOGIN )
                OVER (PARTITION BY CODE, CALLED ORDER BY BEGDTU DESC)
                AS FIRST_ROW_ID
       FROM
                STAT.CAUSE_CODE_DESCRIPTIONS
            INNER JOIN
                STAT.STAT_DIAL
            ON STAT.CAUSE_CODE_DESCRIPTIONS.CODE = STAT.STAT_DIAL.CAUSE
       WHERE
            CALLED LIKE '%3623.1348'
            AND FIRST_ROW_ID = 1)
WHERE
      BEGDT > SYSDATE
            - 500
      AND ROWNUM <= 100
ORDER BY
      BEGDT DESC

Upvotes: 0

Chamal
Chamal

Reputation: 1449

SELECT *
FROM (
select *,ROW_NUMBER() OVER (PARTITION BY login ORDER BY begdt DESC) RN
from stat.cause_code_descriptions 
INNER JOIN stat.stat_dial on stat.cause_code_descriptions.CODE = stat.stat_dial.cause 
where called like '%3623.1348' and begdt > sysdate-500 and rownum <=100 )
WHERE RN=1;

Upvotes: 0

Related Questions