Mysql Select Rows on First Occurrence of Each Unique Value

MySQL Select rows on first occurrence of each unique value

mysql has a "cheat" for this:

select *
from mytable
group by cid;

That's all you need, because in mysql it allows you to not aggregate the non-grouped-by columns (other databases would throw a syntax error), in which case it outputs only the first occurrence of each group-by value(s). Note though that this won't guarantee the way in which the "first" occurrence is determined (it will be just how the rows are read in)

If you want a particular first occurrence, sort first, then apply the group-by cheat:

select *
from (
    -- order by the "time" column descending to get the "most recent" row
    select * from mytable order by time desc
    ) x
group by cid

Query to select row with first occurrence of an element

How can I have it so that it only shows the first record of each State?

Use window functions:

SELECT sm.*
FROM (SELECT State, Month,
             SUM(Sum_Confirmed) AS Max_Sum_Confirmed,
             ROW_NUMBER() OVER (PARTITION BY State ORDER BY SUM(SUM_Confirmed) DESC) as seqnum
      FROM `covid_by_countynmonth` 
      GROUP BY State, Month 
     ) sm
WHERE seqnum = 1;
ORDER BY State, Max_Sum_Confirmed DESC ;

CountyName seems superfluous so I removed it.

Mysql select unique value + first occurrence + last occurence

Is this what you want?

SELECT address, min(timestamp), max(timestamp)
FROM ((SELECT address_from as address, timestamp 
       FROM transactions 
      )
      UNION ALL
      (SELECT address_to as address, timestamp
       FROM transactions
      )
     ) a
GROUP BY address;

This has the timestamps for the addresses, but not the ids. It is unclear which you are looking for.

SELECT only one entry of multiple occurrences

SELECT * 
  FROM table  
  WHERE id IN (SELECT MAX(id) FROM table GROUP BY fk)

How to select row with first occurrence of column value?

you can use dense_rank window function to solve this problem

you can modify your query as follows

select country, policy, date 
    from(
        SELECT country, policy, date, dense_rank() over(partition by country order by date) as rnk
        FROM date_table AS d
        JOIN country_table AS c ON d.country_id = c.country_id
        JOIN policy_table AS p ON p.policy_id = d.policy_id
        WHERE date IS NOT NULL) tmp 
 where rnk = 1
order by date;

How to get select rows that have first occurrences before column value changes in MYSQL8

You can use lag() and lead() to exhibit records whose final_state is different that in the previous or next row:

select
    event_timestamp,
    final_state
from (
    select 
        t.*, 
        lag(final_state) over(order by event_timestamp)  lag_final_state,
        lead(final_state) over(order by event_timestamp) lead_final_state
    from mytable t
) t
where final_state <> lag_final_state or final_state <> lead_final_state

---

Related Topics