SQL Server Query Tips

Question

I want to improve this query, which I wrote for a small web application in ASP.NET 4.0, using SQL-Server 2005. This application will allow the user to search by product ID and return the following information:

• Highest purchase price + latest purchase date @ this price
• Lowest purchase price + latest purchase date @ this price
• Last purchase price + date
• Average purchase price (optional, I thought this could improve the utility of the application)

Here is the structure of the Products table (I include only the corresponding columns, this is an already created DB, and these are non-pk columns)

• product_id (nvarchar (20))
• price (decimal (19,2))
• pDate (datetime)

Before I put the request, I still want to say that I can easily get this information through several requests, so if this is best practice, ignore the improvement of the request, but I tried to minimize the number of requests needed to get all the necessary information.

What I still have: (Note: There are lines with price = 0, so I ignored those who select MIN price search at the bottom)

SELECT price, MAX(pDate) FROM Products WHERE product_id = @product_id AND (price = (SELECT MAX(price) FROM Products WHERE product_id =@product _id) OR price = (SELECT MIN(price) FROM Products WHERE product_id = @product_id AND price > 0)) GROUP BY price 

Now this returns 2 rows:

• first = lowest price + date
• second row = high price + date

Ideally, I would like the query to return 1 row with all the necessary information mentioned above, if possible, as this would simplify the display of information in ASP for me. And, as I said earlier, if multiple queries are an approach, then there is no need to rewrite the complex query here.

Edit

Here are some sample data.

Desired query results: (ignore the format when I entered it in excel)

Here is a query that I will use thanks to Ken Benson:

 SELECT TOP 1 prod.product_id, minp.price AS minprice, minp.pDate as minlastdate, maxp.price AS maxprice, maxp.pDate as maxlastdate, ag.price AS averageprice FROM products AS prod LEFT JOIN (SELECT lmd.product_id,max(lmd.pDate) as pDate,mn.price FROM products as lmd INNER JOIN (SELECT product_id, min(price) AS price from products WHERE price > 0 group by product_id) as mn ON lmd.product_id=mn.product_id AND lmd.price=mn.price group by lmd.product_id,mn.price ) AS minp ON minp.product_id=prod.product_id LEFT JOIN (SELECT lxd.product_id,max(lxd.pDate) as pDate,mx.price FROM products as lxd INNER JOIN (SELECT product_id, max(price) AS price from products group by product_id) as mx ON lxd.product_id=mx.product_id AND lxd.price=mx.price group by lxd.product_id,mx.price ) AS maxp ON maxp.product_id=prod.product_id LEFT JOIN (SELECT product_id,avg(price) as price FROM products WHERE price > 0 GROUP BY product_id) AS ag ON ag.product_id=prod.product_id WHERE prod.product_id=@product _id 

Answer 1

I think you can make a couple of connections back to the table ...

 Select product_id, min.price, min.pDate, max.price, max.pDate FROM products as p LEFT JOIN (Select Min(price), pDate, product_id FROM products GROUP BY product_id) as min on min.product_id=p.product_id LEFT JOIN (Select max(price), pDate, product_id FROM products GROUP BY product_id) as max on max.product_id=p.product_id Where p.product_id = @product_id 

This second bit of code should give the desired results ....

 SELECT prod.product_id, minp.price AS minprice, minp.pDate as minlastdate, maxp.price AS maxprice, maxp.pDate as maxlastdate, ag.price AS averageprice FROM products AS prod LEFT JOIN (SELECT lmd.product_id,max(lmd.pDate) as pDate,mn.price FROM products as lmd INNER JOIN (SELECT product_id, min(price) AS price from products group by product_id) as mn ON lmd.product_id=mn.product_id group by lmd.product_id,mn.price ) AS minp ON minp.product_id=prod.product_id LEFT JOIN (SELECT lxd.product_id,max(lxd.pDate) as pDate,mx.price FROM products as lxd INNER JOIN (SELECT product_id, max(price) AS price from products group by product_id) as mx ON lxd.product_id=mx.product_id group by lxd.product_id,mx.price ) AS maxp ON maxp.product_id=prod.product_id LEFT JOIN (SELECT product_id,avg(price) as price FROM products GROUP BY product_id) AS ag ON ag.product_id=prod.product_id WHERE prod.product_id=1 LIMIT 1 

Yep - the condition 'and' is excluded:

 SELECT TOP 1 prod.product_id, minp.price AS minprice, minp.pDate as minlastdate, maxp.price AS maxprice, maxp.pDate as maxlastdate, ag.price AS averageprice FROM products AS prod LEFT JOIN (SELECT lmd.product_id,max(lmd.pDate) as pDate,mn.price FROM products as lmd INNER JOIN (SELECT product_id, min(price) AS price from products group by product_id) as mn ON lmd.product_id=mn.product_id **AND lmd.price=mn.price** group by lmd.product_id,mn.price ) AS minp ON minp.product_id=prod.product_id LEFT JOIN (SELECT lxd.product_id,max(lxd.pDate) as pDate,mx.price FROM products as lxd INNER JOIN (SELECT product_id, max(price) AS price from products group by product_id) as mx ON lxd.product_id=mx.product_id AND **lxd.price=mx.price** group by lxd.product_id,mx.price ) AS maxp ON maxp.product_id=prod.product_id LEFT JOIN (SELECT product_id,avg(price) as price FROM products GROUP BY product_id) AS ag ON ag.product_id=prod.product_id WHERE prod.product_id=@product _id 

Answer 2

I would do this with a combination of ranking functions and conditional aggregations:

 select product_id, max(case when seqnum_hi = 1 then price end) as highPrice, max(case when seqnum_hi = 1 then pdate end) as highPrice_date max(case when seqnum_low = 1 then price end) as lowPrice, max(case when seqnum_low = 1 then pdate end) as lowPrice_date, max(case when seqnum_rec = 1 then price end) as recentPrice, max(case when seqnum_rec = 1 then pdate end) as recentPrice_date, avg(price) as avg_price from (select p.*, row_number() over (partition by product_id order by price asc) as seqnum_low, row_number() over (partition by product_id order by price desc) as seqnum_hi, row_number() over (partition by product_id order by pdate desc) as seqnum_rec from price where product_id = @product_id group by product_id 

The seguence numbers identify strings with specific attributes that interest you (high price, low price, most recent). Then the conditional max simply selects the information from these rows.

Answer 3

The following should get what you want. It is quite long, but readable, so it should be easily modified by anyone who needs:

 ;WITH CTE_MaxPrice AS ( SELECT product_id, MAX(P.price) AS MaxPrice FROM Products P GROUP BY product_id HAVING product_id = @product_id ), CTE_MinPrice AS ( SELECT product_id, MIN(P.price) AS MinPrice FROM Products P GROUP BY product_id HAVING product_id = @product_id ), CTE_MaxPriceDate AS ( SELECT P.product_id, MAX(P.pDate) AS MaxDate FROM Products P INNER JOIN CTE_MaxPrice MaxP ON P.product_id = MaxP.product_id AND P.price = MaxP.MaxPrice GROUP BY P.product_id ), CTE_MinPriceDate AS ( SELECT P.product_id, MAX(P.pDate) AS MinDate FROM Products P INNER JOIN CTE_MinPrice MinP ON P.product_id = MinP.product_id AND P.price = MinP.MinPrice GROUP BY P.product_id ) SELECT MaxP.MaxPrice, MaxPD.MaxDate, MinP.MinPrice, MinPD.MinDate, RP.price AS RecentPrice, MAX(RP.pDate) AS RecentDate, AVG(AP.price) AS AveragePrice FROM Products P INNER JOIN CTE_MaxPrice MaxP ON P.product_id = MaxP.product_id INNER JOIN CTE_MinPrice MinP ON P.product_id = MinP.product_id AND MinP.MinPrice > 0 INNER JOIN CTE_MaxPriceDate MaxPD ON P.product_id = MaxPD.product_id INNER JOIN CTE_MinPriceDate MinPD ON P.product_id = MinPD.product_id INNER JOIN Products RP ON P.product_id = RP.product_id INNER JOIN Products AP ON P.product_id = AP.product_id GROUP BY MaxP.MaxPrice, MaxPD.MaxDate, MinP.MinPrice, MinPD.MinDate, RP.price HAVING P.product_id = @product_id 

Answer 4

Well, since there were three attempts to answer, and no one worked the way you want, I will tell you how I will do it - and this assumes that you can use the stored procedure, and also assumes that the product table is not so huge, that a problem with several separate requests would be a problem:

 CREATE PROCEDURE myproc AS DECLARE @Price1 money DECLARE @Date1 smalldatetime DECLARE @Price2 money DECLARE @Date2 smalldatetime DECLARE @Price3 money DECLARE @Date3 smalldatetime DECLARE @Price4 money SELECT @Price1 = MAX(Price) FROM Products SELECT @Date1 = MAX(pDate) FROM Products WHERE Price=@Price1 SELECT @Price2 = Min(Price) FROM Products WHERE Price >0 SELECT @Date2 = MAX(pDate) FROM Products WHERE Price=@Price2 SELECT @Date3 = Max(pDate) FROM Products SELECT @Price3 = MAX(Price) FROM Products WHERE pDate=@Date3 --max in case there are more than one purchases with the same date. SELECT @Price4 = AVG(Price) FROM Products WHERE Price>0 SELECT @Price1 As MaxPrice, @Date1 As MaxPriceDate, @Price2 As LowPrice, @Date2 As LowPriceDate, @Price4 As AveragePrice, @Price3 As RecentPrice, @Price3 As RecentPriceDate GO 

Forgive any typographical errors, I have not tested this, but if you can use stored procedures, this will work.

Thus, this is not much different from executing several requests from the client, but it is better to execute them in one SP. You can also slightly reduce the number of queries using some of the code from your other answers, but I left it that way for clarity.