CODEWITHSUNDEEP
mysqlsql
petrasadi
petrasadi

Reputation: 11

SQL query - print the min and max from one table in the same row

I need a little help with an sql query.

I have a table with a format and data that looks like this:

id | applicant_id | application_status | status_time
1  | 1234         | received           | 2013-05-06 15:00:00
1  | 1234         | pending            | 2013-05-06 15:30:00
1  | 1234         | approved           | 2013-05-06 16:00:00

The problem that I need to solve will have to print the following:

applicant_id | initial_status | initial_time        | current_status | current_status_time
1234         | received       | 2013-05-06 15:00:00 | approved       | 2013-05-06 16:00:00

How could I go about accomplishing something like this, preferably only using joins and no nested selects?

Upvotes: 0

Views: 285

Answers (6)

Walter Mitty
Walter Mitty

Reputation: 18940

Try something like this.

select
    t1.applicant_id,
    t2.application_status initial_status,
    t1.initial_time,
    t3.application_status current_status,
    t1.current_status_time
from
    (select
          applicant_id,
          min(status_time) initial_time,
          max(status_time) current_status_time
    from
        your_table
    group by
         applicant_id) t1
    inner join your_table t2
        on (t1.applicant_id = t2.applicant_id and t1.initial_time = t2.status_time)
    inner join your_table t3
        on (t1.applicant_id = t3.applicant_id and t1.current_status_time = t3.status_time)

Upvotes: 0

nzs
nzs

Reputation: 3252

Assuming that for one applicant_id you have one line for 'received' status and also one line for 'approved' status (as you listed in the question) you can use inline views to solve your issue:

select section1.applicant_id AS applicant_id, 'received' AS initial_status, 
       section1.status_time AS initial_time, 'approved' AS current_status,
       section2.status_time AS current_status_time from    
   (select applicant_id, status_time from yourtable where application_status = 'received') section1,   
   (select applicant_id, status_time from yourtable where application_status = 'approved') section2 
   where section1.applicant_id = section2.applicant_id;

Upvotes: 0

OnoSendai
OnoSendai

Reputation: 3970

Assuming MS SQL (Transact-SQL), and that your source table is aptly named [SourceTable]. =)

SELECT DISTINCT
                [Probe].applicant_id,
                [LogMin].application_status     [initial_status], 
                [LogMin].status_time            [initial_time],
                [LogMax].application_status     [current_status], 
                [LogMax].status_time            [current_status_time]
FROM            (
                SELECT      MAX(status_time)    [MaxDate],
                            MIN(status_time)    [MinDate],
                            [applicant_id]
                FROM        [SourceTable]
                GROUP BY    [applicant_id]
                )           [Probe]
INNER JOIN      [SourceTable]                   [LogMax]
        ON      [Probe].[applicant_id]      =   [LogMax].[applicant_id]
        AND     [Probe].[MaxDate]           =   [LogMax].[status_time]
INNER JOIN      [SourceTable]                   [LogMin]
        ON      [Probe].[applicant_id]      =   [LogMin].[applicant_id]
        AND     [Probe].[MinDate]           =   [LogMin].[status_time]

Link to the SQLFiddle test is here.

Upvotes: 0

user2356311
user2356311

Reputation: 1

SELECT  a.application_id
        , a.application_status as initial_status
        , a.status_time as initial_time
        , b.application_status as current_status
        , b.status_time as current_status_time  
FROM        sample1 A
CROSS JOIN sample1  B
WHERE   A.application_status = 'received'
and b.  application_status = 'approved'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

The best way to approach this, in general, is to use the row_number() function. However, this requires a nested select:

select t.applicant_id,
       max(case when seqnum_asc = 1 then status end) as initial_status,
       max(case when seqnum_asc = 1 then status_time end) as initial_time,
       max(case when seqnum_desc = 1 then status end) as current_status,
       max(case when seqnum_desc = 1 then status_time end) as current_time
from (select t.*,
             row_number() over (partition by applicant_id order by status_time) as seqnum_asc,
             row_number() over (partition by applicant_id order by status_time desc) as seqnum_desc
      from t
     ) t
group by t.applicant_id;

If your database did not support row_number(), I would recommend correlated subqueries, for readability. But those are also nested. Here is a solution in MySQL that meets your requirements:

select t.applicant_id,
       substring_index(group_concat(status) separator ',' order by status_time), ',', 1) as initial_status,
       min(status_time) as initial_time,
       substring_index(group_concat(status) separator ',' order by status_time desc), ',', 1) as current_status,
       max(status_time) as current_time
from t
group by t.applicant_id;

Upvotes: 1

Taryn
Taryn

Reputation: 247720

You did not state your database product, but you could use something like this on any database:

select t1.id,
  t1.applicant_id,
  max(case when t1.status_time = t2.mintime then t1.application_status end) initial_status,
  max(case when t1.status_time = t2.mintime then t1.status_time end)initial_time,
  max(case when t1.status_time = t2.maxTime then t1.application_status end) current_status,
  max(case when t1.status_time = t2.maxTime then t1.status_time end) `current_time`
from yourtable t1
inner join
(
  select id, applicant_id,
    max(status_time) maxTime,
    min(status_time) mintime
  from yourtable
  group by id, applicant_id
) t2
  on t1.id = t2.id
  and t1.applicant_id = t2.applicant_id
  and 
  (
    t1.status_time = t2.mintime
    or t1.status_time = t2.maxtime
  )
group by t1.id, t1.applicant_id;

See SQL Fiddle with Demo

Upvotes: 1

Related Questions