MS SQL query using Max

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 391,994 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,270 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser:

Thread Tools Display Modes

Views: 1446 | Replies: 5

•

Join Date: Jun 2008

Posts: 3

Reputation: mhaskell is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

mhaskell

Offline

Newbie Poster

MS SQL query using Max

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.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT
	SDModelProdPrices.EffectiveDate,
	Products.ProdCode,
	Products.longDescr,
	SDModelProdPrices.UnitPrice,
	SDModels.Descr AS 'Model'
FROM
	SPLive.dbo.ProdSubCategories ProdSubCategories,
	SPLive.dbo.Products Products,
	SPLive.dbo.SDModelProdPrices SDModelProdPrices,
	SPLive.dbo.SDModelProds SDModelProds,
	SPLive.dbo.SDModels SDModels,
	SPLive.dbo.Subdivisions Subdivisions
WHERE
	SDModelProds.SDModelProdID = SDModelProdPrices.SDModelProdID
	AND SDModelProds.SDModelID = SDModels.SDModelID
	AND Products.ProductID = SDModelProds.ProductID
	AND ProdSubCategories.ProdSubCatID = Products.ProdSubCatID
	AND Subdivisions.SubdivisionID = SDModels.SubdivisionID
	AND ProdSubCategories.Descr = 'Value Series Products'
	AND SDModelProdPrices.EffectiveDate = MAX(SDModelProdPrices.EffectiveDate)
GROUP BY
	Products.ProdCode,
	Products.longDescr,
	SDModelProdPrices.UnitPrice,
	SDModels.Descr,
	SDModelProds.Discontinued
HAVING
	SDModels.Descr = 'Bedford'
	AND SDModelProds.Discontinued = 0
ORDER BY Products.ProdCode
SELECT
	SDModelProdPrices.EffectiveDate,
	Products.ProdCode,
	Products.longDescr,
	SDModelProdPrices.UnitPrice,
	SDModels.Descr AS 'Model'
FROM
	SPLive.dbo.ProdSubCategories ProdSubCategories,
	SPLive.dbo.Products Products,
	SPLive.dbo.SDModelProdPrices SDModelProdPrices,
	SPLive.dbo.SDModelProds SDModelProds,
	SPLive.dbo.SDModels SDModels,
	SPLive.dbo.Subdivisions Subdivisions
WHERE
	SDModelProds.SDModelProdID = SDModelProdPrices.SDModelProdID
	AND SDModelProds.SDModelID = SDModels.SDModelID
	AND Products.ProductID = SDModelProds.ProductID
	AND ProdSubCategories.ProdSubCatID = Products.ProdSubCatID
	AND Subdivisions.SubdivisionID = SDModels.SubdivisionID
	AND ProdSubCategories.Descr = 'Value Series Products'
	AND SDModelProdPrices.EffectiveDate = MAX(SDModelProdPrices.EffectiveDate)
GROUP BY
	Products.ProdCode,
	Products.longDescr,
	SDModelProdPrices.UnitPrice,
	SDModels.Descr,
	SDModelProds.Discontinued
HAVING
	SDModels.Descr = 'Bedford'
	AND SDModelProds.Discontinued = 0
ORDER BY Products.ProdCode

Last edited by peter_budo : Jun 15th, 2008 at 11:25 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: MS SQL query using Max

Jun 7th, 2008

•

Originally Posted by mhaskell

...
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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT TOP 1 ......
......
ORDER BY SDModelProdPrices.EffectiveDate DESC
select TOP 1 ......
......
ORDER BY SDModelProdPrices.EffectiveDate desc

krs,
tesu

Last edited by tesuji : Jun 7th, 2008 at 11:41 am.

•

Join Date: Jun 2008

Posts: 3

Reputation: mhaskell is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

mhaskell

Offline

Newbie Poster

Re: MS SQL query using Max

Jun 7th, 2008

I think I'm closer would you mind to help me further?

•

Join Date: Jun 2008

Posts: 3

Reputation: mhaskell is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

mhaskell

Offline

Newbie Poster

Re: MS SQL query using Max

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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT TOP 1 Product, price, date
FROM TABLE
WHERE SDModelProds.SDModelProdID = SDModelProdPrices.SDModelProdPriceID
GROUP BY product, date
ORDER BY date
SELECT TOP 1 Product, price, date
FROM table
WHERE SDModelProds.SDModelProdID = SDModelProdPrices.SDModelProdPriceID
GROUP BY product, date
ORDER BY date

Return Data
ProductA 12.00 12/10/2002

Last edited by peter_budo : Jun 15th, 2008 at 11:26 am. Reason: Keep It Organized - please use [code] tags

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: MS SQL query using Max

Jun 7th, 2008

. . . ORDER BY date DESC

•

Join Date: Feb 2008

Posts: 14

Reputation: huangzhi is an unknown quantity at this point

Rep Power: 1

Solved Threads: 4

huangzhi

Offline

Newbie Poster

Re: MS SQL query using Max

Jun 9th, 2008

Help with Code Tags

(Toggle Plain Text)

create table #tmp (Code varchar(255), Price money, Date smalldatetime)

insert #tmp (Code, Price, Date)
select 'ProductA', 12.00, '12/10/2002' union all
select 'ProductA', 12.50, '01/15/2005' union all
select 'ProductA', 12.01, '01/02/2008' union all
select 'ProductB', 15.12, '12/01/2005' union all
select 'ProductB', 16.00, '01/09/2008' union all
select 'ProductB', 16.24, '01/15/2008'

select	T.*
  from	#tmp T
  where Date = (select max(Date) from #tmp X where X.Code = T.Code)

drop table #tmp

Hence Wijaya
www.ex-Soft.tk