CODEWITHSUNDEEP
sqloracle-database
Mark
Mark

Reputation: 53

How to select data in a particular order in oracle

I am looking for a way to select a row of data over other rows. For instance, if I have the following data: 

FIRSTNAME    LASTNAME  STARTDATE    ENDDATE       FLG_DT
JIM          SMITH     01-MAR-1987  09-OCT-2001    BEFORE
JIM          SMITH     05-MAY-2003  07-DEC-2007    MATCH
JIM          SMITH     01-APR-2009  01-DEC-2015    AFTER
JIM          SMITH     01-APR-2016  01-APR-2016    NONE
JOHN         SMITH     01-MAY-2002  01-MAR-2016    MATCH
LORI         SMITH     20-JAN-2009  20-JAN-2010    BEFORE
LORI         SMITH     21-JAN-2010  01-MAR-2016    AFTER
ADAM         TAYLOR    01-APR-2016  01-APR-2016    NONE

basically, for a distinct FIRSTNAME, LASTNAME, STARTDATE I want to pick the row that has a FLG_DT = 'MATCH' first and only. If 'MATCH' doesnt exist I want pick the row with FLG_DT = 'BEFORE' next and only. IF neither of those exist then I want to pick FLG_DT = 'AFTER'and if that doesnt exist then I'll take 'NONE. I can use a ROW_NUMBER() OVER PARTITION BY and ORDER BY the flg_dt field but not sure how to select it in this hierarchical order and leave the others out. The results should be: 

FIRSTNAME    LASTNAME  STARTDATE    ENDDATE       FLG_DT
JIM          SMITH     05-MAY-2003  07-DEC-2007    MATCH
JOHN         SMITH     01-MAY-2002  01-MAR-2016    MATCH
LORI         SMITH     20-JAN-2009  20-JAN-2010    BEFORE
ADAM         TAYLOR    01-APR-2016  01-APR-2016    NONE

Your help is appreciated. I feel like this should be obvious to me but Im drawing a blank!

Upvotes: 0

Views: 93

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50027

The data you show indicates that you really don't want it on DISTINCT FIRSTNAME, LASTNAME, STARTDATE - you only want the distinct FIRSTNAME, LASTNAME. Here's a modified version of @GordonLinoff's query which incorporates this change:

select t.*
from (select your_table.*,
             row_number() over (partition by firstname, lastname
                                order by (case when flg_dt = 'MATCH' then 1
                                               when flg_dt = 'BEFORE' then 2
                                               when flg_dt = 'AFTER' then 3
                                               else 4
                                          end)
                                ) as seqnum
      from your_table
     ) t
where seqnum = 1;

and produces

FIRSTNAME   LASTNAME    STARTDATE           ENDDATE             FLG_DT  SEQNUM
ADAM        TAYLOR      April, 01 2016      April, 01 2016      NONE    1
JIM         SMITH       May, 05 2003        December, 07 2007   MATCH   1
JOHN        SMITH       May, 01 2002        March, 01 2016      MATCH   1
LORI        SMITH       January, 20 2009    January, 20 2010    BEFORE  1

SQLFiddle here

Best of luck.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

This is a type of prioritization. The simplest way is to use row_number():

select t.*
from (select t.*,
             row_number() over (partition by firstname, lastname, startdate
                                order by (case when flg_dt = 'MATCH' then 1
                                               when flg_dt = 'BEFORE' then 2
                                               when flg_dt = 'AFTER' then 3
                                               else 4
                                          end)
                                ) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 4

Related Questions