Finding the two highest values for a criteria

Question

Post reply

Finding the two highest values for a criteria

FelixG

SSCrazy

Points: 2550
More actions
September 15, 2010 at 1:33 pm

#227790

Let's assume that I have the following table tables:
Orders (orderdate, ordernumber, productcode)
- ordernumber is an identity column
I need to get the two highest values (orderdate and ordernumber) for each product code.
Query performances is vital.
Any brilliant idea not involving cursors or temp tables??

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

WITH CTE AS (

SELECT ProductCode, MAX(OrderDate) as OrderDate, MAX(OrderNumber) as OrderNumber

FROM Orders

GROUP BY ProductCode

)

SELECT O.ProductCode, cte.OrderDate as [MaxOrderDate], cte.OrderNumber as [MaxOrderNumber]

FROM Orders O

INNER JOIN CTE on CTE.ProductCode = O.ProductCode

I wasn't 100% sure on your requirements..but this will return the highest orderdate and the highest ordernumber, even if they aren't the same record. If you don't want that, please clarify a bit more.

There are a million ways to do this..I prefer using CTE because it's easy to see what's going on, and to modify/add to it if needed.

FelixG SSCrazy Points: 2550 More actions · Answer 2

I don't want just the highgest value, but the highest value and the second highest value (orderdate+ordernumber) for ecah product code... in other words: the most recent two orders for each product.

WayneS SSC Guru Points: 95460 More actions · Answer 3

Something like this should get the top two of each:

DECLARE @Orders TABLE (ProductCode int, OrderDate datetime, OrderNumber varchar(5));

INSERT INTO @Orders

SELECT 1, '20100915', '11112' UNION ALL

SELECT 1, '20100823', '11122' UNION ALL

SELECT 1, '20100804', '11121' UNION ALL

SELECT 2, '20100915', '11113' UNION ALL

SELECT 2, '20100831', '22233' UNION ALL

SELECT 2, '20100623', '22244';

select * from @Orders;

WITH OrderDate AS

(

SELECT ProductCode,

OrderDate,

RN = ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY OrderDate DESC)

FROM @Orders

), OrderNumber AS

(

SELECT ProductCode,

OrderNumber,

RN = ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY OrderNumber DESC)

FROM @Orders

)

SELECT t1.ProductCode, t1.OrderDate, t2.OrderNumber

FROM OrderDate t1

JOIN OrderNumber t2

ON t1.ProductCode = t2.ProductCode

AND t1.RN = t2.RN

WHERE t1.RN <= 2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Sorry, misunderstood...try this. Will pull the two highest dates for each product, and print their corresponding order number.

;WITH CTE AS (

SELECT ProductCode,

ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY OrderDate DESC) as RN,

OrderDate,

OrderNumber

FROM Orders

)

SELECT ProductCode, OrderDate, OrderNumber FROM CTE

WHERE RN <= 2

edit: Wayne and I have different interpretations of this.

You mean you want to pull the two most recent orders for each product, sorted by OrderDate, and then display the corresponding OrderNumber, right?

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 5

FelixG (9/15/2010)
Let's assume that I have the following table tables:
Orders (orderdate, ordernumber, productcode)
- ordernumber is an identity column
I need to get the two highest values (orderdate and ordernumber) for each product code.
Query performances is vital.
Any brilliant idea not involving cursors or temp tables??

Think "ROW_NUMBER()" with a "partition" with a "DESC" ORDER BY... 😉

That being said, you say "query performances is vital" so post the complete CREATE TABLE statement including constraints, defaults, triggers, indexes, and any thing else that may be a "trait" of the table. A bit of "easily consumable data" (see the first link in my signature below for how to do that) would likely give someone enough information to build a million row test table to ensure they give you a bit of tested high performance code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 6

Derrick Smith (9/15/2010)
You mean you want to pull the two most recent orders for each product, sorted by OrderDate, and then display the corresponding OrderNumber, right?

FelixG (9/15/2010)
I need to get the two highest values (orderdate and ordernumber) for each product code.

This question needs to be clarified as to which answer is correct. As I was coding the solution I posted, I was thinking this is definitely a weird requirement. My interpretation is that the two highest of each the orderdate and ordernumber are desired. It makes more sense to have the two highest (latest) orders, by their order dates, with their corresponding order number.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

WayneS (9/15/2010)
Derrick Smith (9/15/2010)
You mean you want to pull the two most recent orders for each product, sorted by OrderDate, and then display the corresponding OrderNumber, right?
FelixG (9/15/2010)
I need to get the two highest values (orderdate and ordernumber) for each product code.
This question needs to be clarified as to which answer is correct. As I was coding the solution I posted, I was thinking this is definitely a weird requirement. My interpretation is that the two highest of each the orderdate and ordernumber are desired. It makes more sense to have the two highest (latest) orders, by their order dates, with their corresponding order number.

A weird requirement indeed if that's the case...but most business requirements are.

I guess since the OrderNumber field is an Identity column, the only way the 2 highest orderdates and 2 highest ordernumbers wouldn't be the same is if they started manually inputting the date, and went backwards. I've seen weirder..

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

Jeff Moden (9/15/2010)
FelixG (9/15/2010)
Let's assume that I have the following table tables:
Orders (orderdate, ordernumber, productcode)
- ordernumber is an identity column
I need to get the two highest values (orderdate and ordernumber) for each product code.
Query performances is vital.
Any brilliant idea not involving cursors or temp tables??
Think "ROW_NUMBER()" with a "partition" with a "DESC" ORDER BY... 😉
That being said, you say "query performances is vital" so post the complete CREATE TABLE statement including constraints, defaults, triggers, indexes, and any thing else that may be a "trait" of the table. A bit of "easily consumable data" (see the first link in my signature below for how to do that) would likely give someone enough information to build a million row test table to ensure they give you a bit of tested high performance code.

Heh... happened again... two folks posted while I was thinking about posting. Sorry about the overlap of advice.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

FelixG SSCrazy Points: 2550 More actions · Answer 9

Thanks a lot Jeff, I'm coming from SQL Server 2000 and did not have knowledge about rownumber().

I said "performance is vital" to avoid the use of temp tables or cursors for the solution, but using the CTE posted I got the needed response time.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

FelixG (9/16/2010)
Thanks a lot Jeff, I'm coming from SQL Server 2000 and did not have knowledge about rownumber().
I said "performance is vital" to avoid the use of temp tables or cursors for the solution, but using the CTE posted I got the needed response time.

That's cool. Thanks for the feedback.

As a sidebar, use of a temp table does not cause a performance problem... incorrect use of a temp table does. 😉 I use Temp Tables to hold inerim results for very complicated queries to cause a "Divide'n'Conquer" effect. It's made many a 40 minute run execute in less that 3 or 4 seconds. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Jeff Moden (9/16/2010)
FelixG (9/16/2010)
Thanks a lot Jeff, I'm coming from SQL Server 2000 and did not have knowledge about rownumber().
I said "performance is vital" to avoid the use of temp tables or cursors for the solution, but using the CTE posted I got the needed response time.
That's cool. Thanks for the feedback.
As a sidebar, use of a temp table does not cause a performance problem... incorrect use of a temp table does. 😉 I use Temp Tables to hold inerim results for very complicated queries to cause a "Divide'n'Conquer" effect. It's made many a 40 minute run execute in less that 3 or 4 seconds. 🙂

Likewise, and more - if you notice rubbish performance for a CTE chain, try breaking it up by running an intermediate result into a temp table. It can make a dramatic difference.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden