CODEWITHSUNDEEP
sqloracle-database
Shivananda
Shivananda

Reputation: 1

How to get the unique employee record with min date of birth?

If Names field is duplicate then only one name should be fetched and in gender field if one name with male and another with a female then female should be fetched and If both the names having the same gender then lowest DOB should be fetched 

INPUT:
NAME      GENDER DOB
--------- ------ -------
ABC       M      20-MAR-18
ABC       M      30-MAR-18
DEF       M      20-MAR-18
DEF       F      25-MAR-18
GHI       F      20-MAR-18
GHI       M      10-MAR-18
JKL       F      20-MAR-18
JKL       F      10-MAR-18

OUTUT:
NAME      GENDER DOB
--------- ------ -------
ABC       M      20-MAR-18
DEF       F      25-MAR-18
GHI       F      20-MAR-18
JKL       F      10-MAR-18

Upvotes: 0

Views: 99

Answers (3)

Kirill Leontev
Kirill Leontev

Reputation: 10941

select 
  name,
  min(gender) keep(dense_rank first order by decode(gender, 'F', 0, 1)) gender,
  min(dob) keep(dense_rank first order by decode(gender, 'F', 0, 1)) dob                   
from 
  emp
group by 
  name

SQL Fiddle

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81950

SELECT * FROM (
    SELECT 
        p.*,
        row_number() OVER (partition by name order by gender, dob) as rn
    FROM Person p
) WHERE rn = 1

Upvotes: 1

ravioli
ravioli

Reputation: 3833

You can use a window function:

SELECT name, gender, DOB
FROM (
  SELECT name, gender, DOB, 
    ROW_NUMBER() OVER(
      PARTITION BY name -- Group by name
      ORDER BY gender, DOB -- Order by gender, DOB within each "name" group
    ) AS RowNumber
  FROM MyTable
) src
WHERE RowNumber = 1 -- Only get one row per group
;

SQL Fiddle

Upvotes: 1

Related Questions