SQL*Plus (MOSC)

MOSC Banner

remove duplicate record from sql

edited Dec 28, 2012 12:33AM in SQL*Plus (MOSC) 4 commentsAnswered ✓
select
    T.operator_id,
    T.platform_serial,
    pvs.time_stamp
from (
                select
                    vc.hardware_id,
                    MAX(decode(vc.data_key, 'operator-id', vc.data_value)) operator_id,
                    MAX(decode(vc.data_key, 'Platform Serial', vc.data_value)) platform_serial
                from
                    verifier_capability vc
                group by
                    vc.hardware_id
) T,
physical_verifier pv,
physical_verifier_status pvs
where
    pvs.physical_id = pv.physical_id
and pv.hardware_id  = T.hardware_id
and T.platform_serial is not null
and T.operator_id is not null
order by pvs.time_stamp desc;

current result:

operator_id, platform_serial, timestamp
3725 569488 1355989538000000000
3725 573592 1354193106000000000
3227 557317 1353561342000000000
3325 000448347 1353490966000000000
3227 555777 1352367525000000000
3511 573595 1350376799000000000



requirement

want to display single operator_id with max timestemp, need to remove minimum timestamp value from duplicate operator_id

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center