CODEWITHSUNDEEP
sqlsql-servert-sqlmax
Smudger
Smudger

Reputation: 10781

sql, return only the max version of a record from a table

I have the following table which has a few key fields. the most important being the version and the dates. enter image description here I need a query that will allow me to display the active prices in the system for each of the company and products.

so show all dates between start and end, easy enough show only the maximum version with those results - this is where I am stuck.

I have created a fiddle to show my example http://sqlfiddle.com/#!6/e0d4f/3

how can I return only the record for each company and product that has the highest version within the date ranges?

this is what I have so far but incomplete:

select * from 
prices
where getdate() between [start] and [end] 
--and max(version)

Upvotes: 1

Views: 150

Answers (2)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

;WITH PricesCTE AS
(
  SELECT *,
         ROW_NUMBER()OVER(PARTITION BY companyid,product ORDER BY version DESC) AS rn
  FROM   prices
  WHERE  GETDATE() BETWEEN [start] AND [end]
)
SELECT * 
FROM   PricesCTE 
WHERE  rn = 1

SQLFiddle Demo

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

First find the highest version per product for the desired date. Then join with your table to get that record.

select * 
from 
(
  select companyid, product, max(version) as max_version 
  from prices
  where getdate() between [start] and [end]
  group by companyid, product
) this_date 
inner join prices
  on prices.companyid = this_date.companyid
  and prices.product = this_date.product 
  and prices.version = this_date.max_version;

Here is the SQL fiddle: http://sqlfiddle.com/#!6/e0d4f/32.

Upvotes: 1

Related Questions