Question 22154 in Mysql Question posted in
The official MySQL documentation can be found here.

Query for finding latest record in sql – Mysql

Given a currency table I need to find the latest record of conversion rate which is less than given particular date

Input table structure given below:

id baseCur Curr rate date
1 INR USD 81 2022-11-09
2 INR USD 82 2022-11-08
3 INR USD 80 2022-11-06
4 INR CAD 56 2022-11-05
5 INR RUB .74 2022-11-04
6 INR CAD 57 2022-11-12

Problem statement:

I need to find all latest currencies rate that is less than 2022-11-09.On any given date there will be only conversation rate for any particular currency

so expected output

id baseCur Curr rate date
2 INR USD 82 2022-11-08
4 INR CAD 56 2022-11-05
5 INR RUB .74 2022-11-04

Explanantion of output :

Id 1,6 rejected : cause they are greater than 2022-11-09 date

Id 3 rejected cause we have one more record for INR to CAD in row 2 and its date is more new to Id 3

Tags:

3

Answers


  1. 0

    You can use a window function such as DENSE_RANK() if DB version is 8.0+ in order to determine the latest records by using the query below

    WITH t AS
(
 SELECT t.*, DENSE_RANK() OVER (PARTITION BY baseCur, Curr ORDER BY date DESC) AS dr
   FROM t
  WHERE date < '2022-11-09' 
)
SELECT id, baseCur, Curr, rate, date
  FROM t
 WHERE dr = 1

    But, notice that this query returns the ties(equal date values) as well if there is any.

    Demo

    Login or Signup to reply.
  2. 0

    Beside the option to use a window function for that, you could also use a subquery. In the subquery, you will catch every currency with the latest date:

    SELECT 
curr, MAX(yourdate) maxDate
FROM yourtable
WHERE yourdate < '2022-11-09' 
GROUP BY curr;

    This query will produce this outcome:

    Curr maxDate
    2 2022-11-08
    4 2022-11-05
    5 2022-11-04

    This result can be used by applying a JOIN clause or IN clause from a main query.

    This will add the other columns.

    SELECT y.id, y.baseCur, y.curr, y.rate, y.yourdate
FROM yourtable y 
JOIN (SELECT 
curr, MAX(yourdate) maxDate
FROM yourtable
WHERE yourdate < '2022-11-09' 
GROUP BY curr) maxDates
ON y.curr = maxDates.curr 
AND y.yourdate = maxDates.maxDate
ORDER BY id;

    Thus, the complete intended result will be created:

    id baseCur Curr rate date
    2 INR USD 82 2022-11-08
    4 INR CAD 56 2022-11-05
    5 INR RUB .74 2022-11-04

    To point that out: I think using a window function should be prefered if possible.

    They just have the "disadvantage" older DB’s don’t provide them and they often differ depending on the DB type.

    So, if a query is required that works always on each DB type and DB version, this way of using a subquery becomes helpful.

    Login or Signup to reply.
  3. 0

    You can fetch the desired output using subquery, as shown below, which fetches latest record from each currency.

    -- 1. Based on id column
SELECT * FROM sometable as t WHERE t.id = 
(SELECT MAX(id) FROM sometable WHERE Curr = t.Curr and date < '2022-11-09');
-- 2. Based on date column
SELECT * FROM sometable as t WHERE t.date = 
(SELECT MAX(date) FROM sometable WHERE Curr = t.Curr and date < '2022-11-09');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search