CODEWITHSUNDEEP
mysql
Alberto
Alberto

Reputation: 441

group by first and last occurrence

I have a table like the following one:

    +-------------+--------------+
    | current_day | browser      |
    +-------------+--------------+
    | 2016-05-02  | Safari       |
    | 2016-05-03  | Safari       |
    | 2016-05-04  | Safari       |
    | 2016-05-05  | Safari       |
    | 2016-05-06  | Safari       |
    | 2016-05-07  | Safari       |
    | 2016-05-08  | Safari       |
    | 2016-05-09  | Opera        |
    | 2016-05-10  | Opera        |
    | 2016-05-11  | Opera        |
    | 2016-05-12  | Opera        |
    | 2016-05-13  | Opera        |
    | 2016-05-14  | Opera        |
    | 2016-05-15  | Chrome       |
    | 2016-05-16  | Firefox      |
    | 2016-05-17  | Firefox      |
    | 2016-05-18  | Firefox      |
    | 2016-05-19  | Firefox      |
    | 2016-05-20  | Firefox      |
    | 2016-05-21  | Firefox      |
    | 2016-05-22  | Firefox      |
    | 2016-05-23  | Safari       |
    | 2016-05-24  | Safari       |
    | 2016-05-25  | Safari       |
    | 2016-05-26  | Safari       |
    | 2016-05-27  | Safari       |
    | 2016-05-28  | Safari       |
    | 2016-05-29  | Safari       |
    | 2016-05-30  | Opera        |
    | 2016-05-31  | Opera        |
    | 2016-06-01  | Opera        |
    | 2016-06-02  | Firefox      |
    | 2016-06-03  | Firefox      |
    | 2016-06-04  | Firefox      |
    | 2016-06-05  | Firefox      |
    | 2016-06-06  | Firefox      |
    | 2016-06-07  | Firefox      |
    | 2016-06-08  | Firefox      |
    | 2016-06-09  | Chrome       |
    | 2016-06-10  | Chrome       |
    | 2016-06-11  | Chrome       |
    | 2016-06-12  | Chrome       |
    | 2016-06-13  | Chrome       |
    | 2016-06-14  | Chrome       |
    | 2016-06-15  | Chrome       |
    | 2016-06-16  | Chrome       |
    +-------------+--------------+

I would like to extract the first and last occurrence of each sequence in the 'browser' column, in other words the following result:

    +-------------+--------------+
    | current_day | browser      |
    +-------------+--------------+
    | 2016-05-02  | Safari       |
    | 2016-05-08  | Safari       |
    | 2016-05-09  | Opera        |
    | 2016-05-14  | Opera        |
    | 2016-05-15  | Chrome       |
    | 2016-05-16  | Firefox      |
    | 2016-05-22  | Firefox      |
    | 2016-05-23  | Safari       |
    | 2016-05-29  | Safari       |
    | 2016-05-30  | Opera        |
    | 2016-06-01  | Opera        |
    | 2016-06-02  | Firefox      |
    | 2016-06-08  | Firefox      |
    | 2016-06-09  | Chrome       |
    | 2016-06-16  | Chrome       |
    +-------------+--------------+

Is it possible/convenient to create a MYSQL query? Or is it better to extract all results and perform some post-processing with php?

Upvotes: 3

Views: 1978

Answers (4)

Vivek
Vivek

Reputation: 43

Try this

SELECT r.* FROM (SELECT t.* FROM (SELECT k.* FROM (SELECT * FROM `tbl_test` ORDER BY `current_day` DESC) as k GROUP BY k.browser) as t  UNION SELECT h.* FROM (SELECT q.* FROM (SELECT * FROM `tbl_test` ORDER BY `current_day` ASC) as q GROUP BY k.browser) as h ) as r ORDER BY r.current_day

Upvotes: 0

dheiberg
dheiberg

Reputation: 1914

try this: 

SELECT current_day, browser
FROM table_name AS a
WHERE current_day = (
    SELECT MAX(current_day)
    FROM table_name AS b
    WHERE a.browser = b.browser
) 
OR current_day = (
    SELECT MIN(current_day)
    FROM table_name AS c
    WHERE a.browser = c.browser
)

Slightly simpler method: 

select min(current_day), max(current_day), browser from test group by browser;

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

here are 2 samples for one and two lines per browser

one line

SELECT
  min(current_day) AS  start_date
  , max(current_day) AS end_date
  , browser
FROM your_table
GROUP BY browser;

two lines

SELECT * FROM (
  SELECT
    min(current_day)
    , browser
  FROM your_table
  UNION ALL
  SELECT
    max(current_day)
    , browser
  FROM your_table
)
ORDER BY broser,current_day;

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the following query:

SELECT MIN(current_day) AS start_day, 
       MAX(current_day) AS stop_day, 
       browser
FROM (
   SELECT current_day, browser,
          @grp := IF(@br = browser, @grp,
                     IF(@br := browser, @grp+1, @grp+1)) AS grp
   FROM mytable 
   CROSS JOIN (SELECT @grp := 0, @br := '') AS vars
   ORDER BY current_day) AS t
GROUP BY browser, grp

The above query uses variables, in order to identify islands of consecutive records having the same browser value. It returns a single row per browser. You have to repeat the same subquery twice and use UNION if you want to get two separate lines for each of the min/max dates.

Demo here

Upvotes: 1

Related Questions