CODEWITHSUNDEEP
mysql
anInputName
anInputName

Reputation: 449

How to select row with first occurrence of column value?

I have 3 tables:

date_table(date, country_id, policy_id, ...)
country_table(country_id, country, ...)
policy_table(policy_id, policy, ...)

I want to select the country and policy for the earliest date in the table.

So if I have:

UK | Taxation| 01/01/2021
UK | Brexit  | 02/01/2021
FR | Travel  | 05/01/2021

I want to return this:

UK | Taxation| 01/01/2021
FR | Travel  | 05/01/2021

However, if I try to use GROUP BY I get an error since I don't include policy. If I do include it, I get the earliest date for country AND policy, which is not what I want. This is what I have so far:

SELECT country
     , policy
     , MIN(date)
  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
 GROUP 
    BY country, policy
 ORDER 
    BY date;

Upvotes: 0

Views: 153

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

try a query like this its not tested!

SELECT c.country, p.policy, d.date
FROM (
  SELECT country, MIN(date) AS mdate
  FROM date_table
  GROUP BY date,country
) as grouped
LEFT JOIN date_table AS d ON d.country = grouped.country AND d.date = grouped.mdate
JOIN country_table AS c  ON d.country_id = c.country_id
JOIN policy_table AS p  ON p.policy_id = d.policy_id
ORDER BY d.date;

Upvotes: 0

Anand Vidvat
Anand Vidvat

Reputation: 1059

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;

Upvotes: 1

Related Questions