Hi I am newbie in sql so need help in finding the query on sql table. 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

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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');