May 13, 2015 at 10:16 am
Hi,
¿Is there any more efficient way for example to implement the next query?
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
WHERE dealer = 'dealer sample'
¿What indexes I should create for this query?
Many thanks in advance.
May 13, 2015 at 10:44 am
This sql will often be more efficient, although it's not guaranteed to be:
SELECT *
FROM (
SELECT article, dealer, price,
DENSE_RANK() OVER(PARTITION BY article ORDER BY price DESC) AS dense_rank
FROM shop
WHERE dealer = 'dealer sample'
) AS derived
WHERE
dense_rank = 1
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 13, 2015 at 10:47 am
Indexing is an interesting q.
In theory I guess best would be an index on:
( dealer, article ) include ( price ) or even
( dealer, article, price )
but it also depends on what % of rows will match that dealer condition and how many other columns are in the main table (how "wide" is that table).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply