CODEWITHSUNDEEP
sql
Iwishworldpeace
Iwishworldpeace

Reputation: 488

SQL question how do I select rows with column condition values

Say I have this table:

id  timeline   
---|--------|
 1 | BASELINE |
 1 | MIDTIME  |
 1 | ENDTIME  | 
 2 | BASELINE |
 2 | MIDTIME  | 
 3 | BASELINE | 
 4 | BASELINE | 
 5 | BASELINE | 
 5 | MIDTIME  | 
 5 | ENDTIME  |

How do I get the output so that it looks like this:

id  timeline   
---|--------|
 1 | BASELINE |
 1 | MIDTIME  |
 2 | BASELINE |
 2 | MIDTIME  | 
 5 | BASELINE | 
 5 | MIDTIME  |

How do I select the first two terms of each ID which has 2 timeline values? Many Thanks

Upvotes: 0

Views: 43

Answers (2)

Tomato32
Tomato32

Reputation: 2245

Here is a sample. I used ROW_NUMBER() function.

Hope to help, my friend :))

CREATE TABLE  TestData(id int, timeline varchar(20))

insert into TestData
values (1, 'BaseLine'), 
 (1, 'Midtime'),
 (1, 'Endtime'),
 (2, 'BaseLine'),
 (2, 'Midtime'),
 (3, 'BaseLine'),
 (4, 'BaseLine'),
 (5, 'BaseLine'),
 (5, 'Midtime'),
 (5, 'Endtime')


-----
SELECT id, timeline
FROM (SELECT id, timeline,  
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY (SELECT NULL)) AS rownum
FROM TestData 
   WHERE
      id  IN (
               SELECT id FROM TestData group by id having count(id)>=2
             )
    ) as T
WHERE rownum < 3

Upvotes: 1

ch_g
ch_g

Reputation: 1440

-- assume your table name is t
with d as (
select id
      ,timeline
      ,row_number() over(partition by id 
                             order by case when timeline = 'BASELINE' then 1
                                           when timeline = 'MIDTIME' then 2
                                           when timeline = 'ENDTIME' then 3
                                       end
                         ) seq
       ,count(0) over(partition by id) cnt
  from t
)
select id
      ,timeline
  from d
 where cnt >= 2
   and seq <= 2
 order
    by id
      ,seq

If you run the query against your test data, you will get the exact result as you listed. If you really want "select the first two terms of each ID which has more than 2 timeline values", you need to change "cnt >= 2" to "cnt > 2".

Upvotes: 0

Related Questions