How to get row for every first occurrence of a column value

Question

I have a table named lists having below columns and rows.


   type     | time | alt_id       
------------+------+---------
 invest_fees| t1   |  5601
 invest_fees| t2   |  5601
 invest_fees| t3   |  5601
 countries  | t4   |  5601
 invest_fees| t5   |  5601
 invest_fees| t6   |  5601
 countries  | t7   |  5601
 countries  | t8   |  5601
 invest_fees| t9   |  5602
 countries  | t10  |  5602

I want to get this:

      type     | time | alt_id       
------------+------+---------
 invest_fees| t1   |  5601
 countries  | t4   |  5601
 invest_fees| t5   |  5601
 countries  | t7   |  5601
 invest_fees| t9   |  5602
 countries  | t10  |  5602

i.e. first occurrence of type every time it is found. Later, I want to find out the diff: t4-t1, t7-t5, t10-t9. Any ideas on how can I achieve this using sql? Thanks in advance.

How do you decide the order? Do you have a timestamp or sequence order column as well? — jarlh, Sep 23 '19 at 12:33
You need a column to order the rows. Otherwise there is no way to tell if `d3 | invest_fees` is before or after ` f1 | countries`. — Serg, Sep 23 '19 at 12:34
SQL tables represent *unordered* sets. There is no "first" row unless a column specifies the ordering. — Gordon Linoff, Sep 23 '19 at 12:39

forpas · Accepted Answer · 2019-09-23T13:21:07.050

For MySQL 8.0+ you can use LAG():

SELECT t.type, t.time, t.alt_id
FROM (SELECT *, LAG(type) OVER() prev FROM tablename) t
WHERE t.prev IS NULL OR t.prev <> t.type

See the demo.
For previous versions use variables:

SET @rn := 0;
SET @type := null;
SELECT t.type, t.time, t.alt_id 
FROM (
  SELECT @rn := case 
    WHEN @type = type then @rn + 1 
    ELSE 1
  END AS rn, type, time, alt_id,
  @type := type  
  FROM tablename 
) t
WHERE t.rn = 1

See the demo.
Results:

| type        | time | alt_id |
| ----------- | ---- | ------ |
| invest_fees | t1   | 5601   |
| countries   | t4   | 5601   |
| invest_fees | t5   | 5601   |
| countries   | t7   | 5601   |
| invest_fees | t9   | 5602   |
| countries   | t10  | 5602   |

How to get row for every first occurrence of a column value

1 Answers1