Advice on SQL-Server Query

Question

I'm looking to improve this query 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 have it return the following information:

• Highest Purchase Price + Most Recent Date of purchase @ this price
• Lowest Purchase Price + Most Recent Date of purchase @ this price
• Most Recent Purchase Price + Date
• Average Purchase Price (optional, i thought this might improve the usefulness of the app)

Here is the structure of the Products table (I'm only including relevant columns, this is a DB already in production and these are non-pk columns)

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

Before I put down the query I have so far I just want to say that I can get this information easily through multiple queries, so if this is the best practice then disregard improving the query, but I was aiming to minimize the number of queries needed to get all needed information.

What I have so far: (Note: There are rows with price = 0 so I ignored those in the bottom select looking for the MIN price)

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 is returning 2 rows:

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

What I would like ideally is to have a query return 1 row with all the needed information stated above if possible, as it would simplify displaying the information in ASP for me. And like I said earlier, if multiple queries is the be approach then no need to re-write a complex query here.

Edit

Here is some sample data

Desired query results: (ignore the format as I typed this in excel)

Here is the query I will be using 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 do a couple of joins 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 produce 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 - left out an 'and' condition:

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

Well since there have been three attempts to answer, and none have worked quite how you want, I'll tell you how I would do it - and this assumes you can use a stored procedure and also assumes that the product table is not so huge that multiple seperate queries 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 didn't test this, but if you can use stored procedures, this will work.

So this is not much different than doing your multiple queries from the client, but should perform better putting them all into a single SP. You could also cut the number of queries down a bit by using some of the code from your other answers, but I have left it this way for clarity.

Answer 3

The following should get what you want. It's pretty long, but is readable so should be easily modified by anyone who needs to:

;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

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 the rows with the particular attributes you care about (high price, low price, most recent). The conditional max then just selects information from those rows.