1 Reply Latest reply on Jul 15, 2013 8:17 PM by pollywog

    How do I return results based on 2 columns?

    bertalot17

      I've been having an issue and cannot figure it out for the life. First, here's an example set of the data I'm using so you can see exactly what I'm asking.

       

      Emplid             Effdt                 Effseq

      10001           '01-JAN-99'                 0

      10001           '01-JUL-11'                  0

      10001           '01-JUL-11'                  1

      10001           '01-JUL-11'                  2

      10001           '01-JUL-12'                  3

       

      What I need to do here, is obtain 3 rows. The 3 rows I need are rows 1, 4, and 5. I need row 1 because its a completely different date. I need row 5 for the same reason: it's a different date. The issue arises with how I can obtain row 4. The problem is that because rows 2, 3, and 4 all have the same effective date(effdt), SQL Developer just returns one of those rows. Because those 3 rows all have the same effective date(effdt), the tie breaker becomes the effective sequence(effseq) number. When the effective date(effdt) is the same, you need to grab the maximum effective sequence(effseq) number and return that whole row's results such as the emplid, effdt, and effseq. It seems so straight forward and something you can use a subquery for, but its not that simple. Note, that you can specifically use the emplid = 10001 in any specific form because there's many employee id's. Also, the rows will not be in a specific order so you cannot just always grab rows 1, 4, and 5. Some employees may only have a single row in the database, and some may have 50 rows. Everything solely depends on the combination of employee id(emplid), effective date(effdt), and effective sequence(effseq) as the tie breaker. Any help in solving this problem would be greatly appreciated. Thank you in advance.

        • 1. Re: How do I return results based on 2 columns?
          pollywog
          WITH t
               AS (SELECT 10001 emplid,
                          TO_DATE ('01-JAN-1999', 'dd-mon-yy') effdt,
                          0 effseq
                     FROM DUAL
                   UNION ALL
                   SELECT 10001, TO_DATE ('01-JUL-2011', 'dd-mon-yy'), 0 FROM DUAL
                   UNION ALL
                   SELECT 10001, TO_DATE ('01-JUL-2011', 'dd-mon-yy'), 1 FROM DUAL
                   UNION ALL
                   SELECT 10001, TO_DATE ('01-JUL-2011', 'dd-mon-yy'), 2 FROM DUAL
                   UNION ALL
                   SELECT 10001, TO_DATE ('01-JUL-2012', 'dd-mon-yy'), 3 FROM DUAL)
          SELECT emplid, effdt, effseq
            FROM (SELECT t.*,
                         ROW_NUMBER ()
                            OVER (PARTITION BY emplid, effdt ORDER BY effseq DESC)
                            rn
                    FROM t)
           WHERE rn = 1
          
          EMPLIDEFFDTEFFSEQ
          10001
          1/1/1999
          0
          10001
          7/1/2011
          2
          10001
          7/1/2012
          3