0

I am a newbie in the world of SQL query. I need to eliminate the duplicate Staff # and retrieve only the highlighted row. Any help is highly appreciated.

Staff#      Pay_DT      Due_DT      loan_flag   housing 
------------------------------------------------------------------------
123-45-6789     14-Feb-14   3-Jan-14    Y       null 
123-45-6789     14-Feb-14   3-Jan-14    Y       Annual 
123-45-6789     14-Feb-14   13-Jan-14   Y       null 
**123-45-6789   14-Feb-14   13-Jan-14   Y       Annual** 
123-45-6789     null        null        Y       null 
123-45-6789     null        null        Y       Annual
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
user3482634
  • 1
  • 2
    Why that row? Your subject talks about the "most recent date". You don't say which date that is. There are 4 rows with the same maximum `pay_dt`. There are two rows with the same maximum `pay_dt` and `due_dt`. Why do you pick one over the other? – Justin Cave Mar 31 '14 at 20:18
  • you want to return just that one row? – Caffeinated Mar 31 '14 at 20:18
  • This is a historical data. I need to know the Pay DT on which the the housing was set as annual and the payment was made. Thank you for your prompt responses. – user3482634 Mar 31 '14 at 20:23
  • @ Coffee - Yes. That row is the desired result. – user3482634 Mar 31 '14 at 20:28
  • 1
    Hey, side note. You seem to be using SSN's as identifiers in your database. I'd suggest you change that ASAP, it's a big and expensive deal if you lose them. And it's less efficient because you need to use strings instead of numbers as identifiers. Generally not the best idea. – Dylan Brams Mar 31 '14 at 20:44

1 Answers1

0

Perhaps you want

SELECT *
  FROM (SELECT a.*,
               rank() over (partition by staff# order by pay_dt desc) rnk
          FROM table_name a
         WHERE housing = 'Annual')
 WHERE rnk = 1

Alternately

SELECT *
  FROM (SELECT a.*,
               max(pay_dt) over (partition by staff#) max_pay_dt
          FROM table_name a
         WHERE housing = 'Annual')
 WHERE pay_dt = max_pay_dt

If you can have ties (two rows with the same pay_dt where the housing column has a value of Annual for a particular Staff# value, both of these queries would return all the rows with that condition. If you want to break the tie arbitrarily, you could use row_number rather than rank. Otherwise, you'd need to tell us what logic you'd want to use to break the ties.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384