Here I will show some results I got while using Vincent Malgrat's approach.
First I learned, that when using more than 1 such top 1 sub query based on different tables or order, I have to use the RANK() function and not the ROW_NUMBER() function.
Second when using rank() there is the problem of ties. SQL Server's top 1 arbitrarily selects one of the rows with rank = 1 while using rank() can return more than 1 row.
I think it is bad design to use SQL Server top 1 in these cases. To make the design correct some unique constraints (e.g. unique indexes ) have to be found, which prevent this ambiguity.
Here is a SQL Server example if you want to try it by yourself.
Comparing the execution plans of the last two select statements below shows that Vincent Malgrat's approach is better than the top 1 solution.
SET NOCOUNT ON
begin try drop table fruit end try begin catch end catch;
begin try drop table want_to_eat end try begin catch end catch;
begin try drop table drink end try begin catch end catch;
create table fruit (
fruit_code char(1),
fruit_name varchar(20),
date datetime,
fruit_price money
);
go
create table drink (
drink_code char(1),
drink_name varchar(20),
date datetime,
drink_price money
);
go
create table want_to_eat (
code char(1),
qty integer,
);
go
insert into want_to_eat values ( 'A', 1);
insert into want_to_eat values ( 'B', 2);
insert into want_to_eat values ( 'B', 1);
insert into want_to_eat values ( 'C', 1);
insert into fruit values ( 'A', 'apple', '20100101', '2.20');
insert into fruit values ( 'A', 'apple', '20110101', '2.40');
insert into fruit values ( 'B', 'banana', '20100101', '1.40');
insert into fruit values ( 'B', 'banana', '20110101', '1.30');
insert into fruit values ( 'B', 'banana', '20110101', '1.35');
insert into drink values ( 'A', 'aperol', '20100101', '5.20');
insert into drink values ( 'A', 'aperol', '20110101', '5.40');
insert into drink values ( 'B', 'bear', '20100101', '4.40');
insert into drink values ( 'B', 'bear', '20110101', '4.30');
create unique index iu_drink on drink(drink_code, date);
-- create unique index iu_fruit on fruit(fruit_code, date); -- Error
Select top 1 fruit_price from fruit where fruit_code = 'A' order by date desc;
Select top 1 fruit_price from fruit where fruit_code = 'B' order by date desc;
Select top 1 fruit_price from fruit where fruit_code = 'C' order by date desc;
SELECT
qty,
(Select top 1 fruit_price from fruit where fruit_code = code order by date desc) fruit_price,
(Select top 1 fruit_name from fruit where fruit_code = code order by date desc) fruit_name,
(Select top 1 drink_price from drink where drink_code = code order by date desc) drink_price,
(Select top 1 drink_name from drink where drink_code = code order by date desc) fruit_name
FROM want_to_eat
WHERE qty = 1;
SELECT qty, fruit_price, fruit_name , drink_price,drink_name from (
-- SELECT * FROM (
SELECT
RANK() OVER (PARTITION BY o.CODE ORDER BY f.date desc) f_lfd,
RANK() OVER (PARTITION BY o.CODE ORDER BY d.date desc) d_lfd,
f.date f_date,
code,
qty,
fruit_price,
fruit_name,
drink_price,
drink_name
from want_to_eat o
left join fruit f on o.code = fruit_code
left join drink d on o.code = drink_code
WHERE qty = 1
) t
where f_lfd = 1
and d_lfd = 1;
Answer to Leigh Riffel:
In the following rows with id 1 and 2 both have rank() and dense_rank of 1.
create table rank_test (
id int,
grp int,
val varchar(10)
);
insert into rank_test values (1, 1, 'a');
insert into rank_test values (2, 1, 'a');
insert into rank_test values (3, 1, 'b');
insert into rank_test values (4, 2, 'b');
select * from rank_test;
select r.*,
RANK() OVER (PARTITION BY grp ORDER BY val) rank,
DENSE_RANK() OVER (PARTITION BY grp ORDER BY val) d_rank
from rank_test r
order by id;
Result:
id grp val rank d_rank
----------- ----------- ---------- -------------------- --------------------
1 1 a 1 1
2 1 a 1 1
3 1 b 3 2
4 2 b 1 1
New answer to Leigh Riffel:
Here an example, I can't transform with your pattern. I have a table with articles an prices and a second table with the history of price changes. Some authority wants to know the old price, when the price has changed.
create table artikel (id int, price int);
create table price_history (id int,price int,v_date datetime);
insert into artikel values (1, 11), (2, 22);
insert into price_history values (1, 11, '20110101'), (2, 20,'20110101');
insert into price_history values (1, 11, '20110201'), (2, 20,'20110201'); -- the true table has more columns, which values might change while price stays the same
insert into price_history values (1, 11, '20110301'), (2, 22,'20110301');
Select id,
(SELECT TOP 1 price_history.price FROM price_history WHERE price_history.id = artikel.id AND artikel.price <> price_history.price ORDER by v_date DESC ) priceOld
from artikel
ORDER BY id;
Select artikel.id, y.price priceOld
from artikel
LEFT JOIN (
SELECT Row_Number() OVER (PARTITION BY id ORDER BY v_date desc)
yRow
, id, price
FROM price_history
) y ON y.id = artikel.id AND y.yRow = 1 and artikel.price <> y.price
ORDER BY id;
I want to get
id priceOld
----------- -----------
1 NULL
2 20
but the second gives
id priceOld
----------- -----------
1 NULL
2 NULL
Vincent Malgrat's approch yields the correct result
Select id, priceOld from (
Select
Row_Number() OVER (PARTITION BY a.id ORDER BY v_date desc) vRow,
a.id, h.price priceOld
from artikel a
LEFT JOIN
price_history h ON a.id = h.id and a.price <> h.price
) t
where vRow = 1
ORDER BY id;
