MS SQL query using Max

Reply

•
•
Join Date: Jun 2008
Posts: 3
Reputation: mhaskell is an unknown quantity at this point 
Solved Threads: 0
mhaskell mhaskell is offline Offline
Newbie Poster

MS SQL query using Max

 
0
  #1
Jun 7th, 2008
I need to write a query that will pull my products and price. However each of the products have multiple prices and I want the price with the most recent date. I have tried the following query but it doesn't work. Can someone assist me. I'm fairly new to sql queries.
sql Syntax (Toggle Plain Text) 
  1. SELECT
  2. 	SDModelProdPrices.EffectiveDate,
  3. 	Products.ProdCode,
  4. 	Products.longDescr,
  5. 	SDModelProdPrices.UnitPrice,
  6. 	SDModels.Descr AS 'Model'
  7. FROM
  8. 	SPLive.dbo.ProdSubCategories ProdSubCategories,
  9. 	SPLive.dbo.Products Products,
  10. 	SPLive.dbo.SDModelProdPrices SDModelProdPrices,
  11. 	SPLive.dbo.SDModelProds SDModelProds,
  12. 	SPLive.dbo.SDModels SDModels,
  13. 	SPLive.dbo.Subdivisions Subdivisions
  14. WHERE
  15. 	SDModelProds.SDModelProdID = SDModelProdPrices.SDModelProdID
  16. 	AND SDModelProds.SDModelID = SDModels.SDModelID
  17. 	AND Products.ProductID = SDModelProds.ProductID
  18. 	AND ProdSubCategories.ProdSubCatID = Products.ProdSubCatID
  19. 	AND Subdivisions.SubdivisionID = SDModels.SubdivisionID
  20. 	AND ProdSubCategories.Descr = 'Value Series Products'
  21. 	AND SDModelProdPrices.EffectiveDate = MAX(SDModelProdPrices.EffectiveDate)
  22. GROUP BY
  23. 	Products.ProdCode,
  24. 	Products.longDescr,
  25. 	SDModelProdPrices.UnitPrice,
  26. 	SDModels.Descr,
  27. 	SDModelProds.Discontinued
  28. HAVING
  29. 	SDModels.Descr = 'Bedford'
  30. 	AND SDModelProds.Discontinued = 0
  31. ORDER BY Products.ProdCode
Last edited by peter_budo; Jun 15th, 2008 at 12:25 pm. Reason: Keep It Organized - please use [code] tags
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 296
Reputation: tesuji is on a distinguished road 
Solved Threads: 42
tesuji tesuji is offline Offline
Posting Whiz in Training

Re: MS SQL query using Max

 
0
  #2
Jun 7th, 2008
•
•
•
•
Originally Posted by mhaskell View Post
...
WHERE...
AND SDModelProdPrices.EffectiveDate = MAX(SDModelProdPrices.EffectiveDate)
...
Aggregate functions are not allowed in WHERE clause. So aggregate functions cannot be used for selecting specific rows. However, you can use them in HAVING clause. For MS Sql Server one solution to get the most recent date values is:
sql Syntax (Toggle Plain Text) 
  1. SELECT TOP 1 ......
  2. ......
  3. ORDER BY SDModelProdPrices.EffectiveDate desc
krs,
tesu
Last edited by tesuji; Jun 7th, 2008 at 12:41 pm.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 3
Reputation: mhaskell is an unknown quantity at this point 
Solved Threads: 0
mhaskell mhaskell is offline Offline
Newbie Poster

Re: MS SQL query using Max

 
0
  #3
Jun 7th, 2008
I think I'm closer would you mind to help me further?
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 3
Reputation: mhaskell is an unknown quantity at this point 
Solved Threads: 0
mhaskell mhaskell is offline Offline
Newbie Poster

Re: MS SQL query using Max

 
0
  #4
Jun 7th, 2008
Data information
ProductA 12.00 12/10/2002
ProductA 12.50 01/15/2005
ProductA 12.01 01/02/2008
ProductB 15.12 12/01/2005
ProductB 16.00 01/09/2008
ProductB 16.24 01/15/2008



I want to return

ProductA 12.01 01/02/2008
ProductB 16.24 01/15/2008


I have tried this query it returns the oldest date of all of the products

sql Syntax (Toggle Plain Text) 
  1. SELECT TOP 1 Product, price, DATE
  2. FROM table
  3. WHERE SDModelProds.SDModelProdID = SDModelProdPrices.SDModelProdPriceID
  4. GROUP BY product, DATE
  5. ORDER BY DATE

Return Data
ProductA 12.00 12/10/2002
Last edited by peter_budo; Jun 15th, 2008 at 12:26 pm. Reason: Keep It Organized - please use [code] tags
Reply With Quote Quick reply to this message  
•
•
Join Date: Apr 2008
Posts: 296
Reputation: tesuji is on a distinguished road 
Solved Threads: 42
tesuji tesuji is offline Offline
Posting Whiz in Training

Re: MS SQL query using Max

 
0
  #5
Jun 7th, 2008
. . . ORDER BY date DESC
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 42
Reputation: huangzhi is an unknown quantity at this point 
Solved Threads: 13
huangzhi huangzhi is offline Offline
Light Poster

Re: MS SQL query using Max

 
0
  #6
Jun 9th, 2008
MS SQL Syntax (Toggle Plain Text) 
  1. CREATE TABLE #tmp (Code varchar(255), Price money, Date smalldatetime)
  2.  
  3. INSERT #tmp (Code, Price, Date)
  4. SELECT 'ProductA', 12.00, '12/10/2002' union ALL
  5. SELECT 'ProductA', 12.50, '01/15/2005' union ALL
  6. SELECT 'ProductA', 12.01, '01/02/2008' union ALL
  7. SELECT 'ProductB', 15.12, '12/01/2005' union ALL
  8. SELECT 'ProductB', 16.00, '01/09/2008' union ALL
  9. SELECT 'ProductB', 16.24, '01/15/2008'
  10.  
  11. SELECT	T.*
  12.   FROM	#tmp T
  13.   WHERE Date = (SELECT max(Date) FROM #tmp X where X.Code = T.Code)
  14.  
  15. DROP TABLE #tmp
Hence Wijaya
www.ex-Soft.tk
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MS SQL Forum


Views: 4395 | Replies: 5
Thread Tools Search this Thread



Tag cloud for MS SQL
"last age autogrowth business connectingtodatabaseinuse count cursor data database dateadd datediff datepart day" dbsize deadlock delete_trigger exploit getdate hack highperformancecomputing hpc hpcserver2008 ibm iis limit live loop maximum microsoft ms mssql multiple multithreading news number password permission position query reporting result security server services sets single source sql sql-injection sqlserver sqlserver2005 subtype supercomputing supertype tables uniqueid update view weekday
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC