I have a table of ads and another of prices, relative to these ads. When the user modifies a price in the application, a new row is added to the prices table, which includes the price and the date the price was modified.
I need to get a list with all the ads and, for each ad, the first price that was registered, and also the last. I have used a double subquery on the same price table.
Tables
ads
id int | ad_title varchar |
|---|---|
| 1 | Potatoes |
| 2 | Tomatoes |
My Answer:
_prices_____id int_ad_id int_price decimal_date_modified date_____1_1_2.50_2022-01-01_____2_1_2.75_2022-01-15_____3_2_1.50_2022-01-01_____4_2_1.75_2022-01-20
Here is the SQL query to get the original and current prices for each ad:
sql
SELECT
a.ad_title,
(SELECT price FROM prices p1 WHERE p1.ad_id = a.id ORDER BY date_modified ASC LIMIT 1) as original_price,
(SELECT price FROM prices p2 WHERE p2.ad_id = a.id ORDER BY date_modified DESC LIMIT 1) as current_price
FROM ads a;
This query will return a list of all ads with their original and current prices. The subqueries in the SELECT statement are used to get the first and last prices for each ad from the prices table.
Rate this post
3 of 5 based on 1648 votesComments