Need help getting highest 2 PO numbers per Part

Question

Need help getting highest 2 PO numbers per Part

rhutchinson 13368

SSC Rookie

Points: 42
More actions
June 1, 2011 at 1:24 pm

#150290

I'm a SQL 2000 rookie (Standard Edition Version 8.00.760 - SP3) and I have made numerous attempts (TOP, MAX, Group By, etc...) to get the highest 2 Purchase Order numbers per Part ID. Here's an example:
Table is dbo.PURC_ORDER_LINE.
Data is:
PART_IDPURC_ORDER_ID
30150739714
30150705627
30150704530
81912641851
81912636959
81912636836
81912636428
81912634941
90602241570
90602240588
90602240405
90602239866
90602239770
90602238907
90602238905
90602238898
Desired Results (highest 2 PO’s for each Part #):
PART_IDPURC_ORDER_ID
30150739714
30150705627
81912641851
81912636959
90602241570
90602240588
If you can help - please be specific since I'm a beginner. Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

First and most important question: are you sure you're using SQL Server 2000 and not 2005 or 2008 version? It's important because since SQL Server 2005 it's been a lot easier to tackle such a problem.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 2

-- Get 2 latest orders for each part:

Select o1.*

From PURC_ORDER_LINE as o1

Join (Select o2.PART_ID, Max(o2.PURC_ORDER_ID) as od1

From PURC_ORDER_LINE as o2

Group by o2.PART_ID) as o3

on o3.PART_ID = o1.PART_ID

and o1.PURC_ORDER_ID in (o3.od1

, (Select Max(o4.PURC_ORDER_ID)

From PURC_ORDER_LINE as o4

Where o4.PART_ID = o1.PART_ID

And o4.PURC_ORDER_ID < o3.od1)

)

---------------------------------------------------------------------------------------
It begins by taking the first step.

rhutchinson 13368 SSC Rookie Points: 42 More actions · Answer 3

Unfortunately, yes I'm sure it's SQL Server 2000. In addition to TOP, MAX, etc... I also tried RANK, ROW_NUMBER and those were NOT acceptable commands.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Well, if we're goin' oldskool... 🙂

Note how at the beginning of this script I made the data easily consumable and testable. It's a shortened list but if you want it more thoroughly tested do provide the full test data in this format going forward. There's two examples in the following code, one's brute force, the other is less optimized but rather functional until you hit extreme data loads. At that point we move to different ranking methods, most likely a temp table using cascading updates, but I don't want to overload you... yet. 🙂

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL

DROP TABLE #tmp

CREATE TABLE #tmp (Part_id INT, purc_order_id INT)

INSERT INTO #tmp

SELECT 301507, 39714 UNION ALL

SELECT 301507, 05627 UNION ALL

SELECT 301507, 04530 UNION ALL

SELECT 819126, 41851 UNION ALL

SELECT 819126, 36959 UNION ALL

SELECT 819126, 36836

select * from #tmp

--Time to go old-school. This is the brute force, pure sql method.

select

t.*

from

#tmp AS t

JOIN

(select part_id, max(purc_order_id) AS maxpid2

FROM #tmp AS t1

WHERE purc_order_id < (SELECT MAX(purc_order_id) AS maxpid FROM #tmp AS t2 WHERE t1.part_id = t2.Part_id)

GROUP BY part_id

) AS d2

ONt.part_id = d2.part_id

AND t.purc_order_id >= d2.maxpid2

-- A cleaner coding method, and more versatile, but it triangle joins:

SELECT

t.part_id, t.purc_order_id

FROM

#tmp AS t

JOIN

-- Get a row by row count of the "rank" of the row

(SELECT

ta1.part_id, ta1.purc_order_id, count(*) AS cnt

FROM

#tmp AS ta1

JOIN

#tmp AS ta2

ONta1.part_id = ta2.part_id

AND ta1.purc_order_id <= ta2.purc_order_id

GROUP BY

ta1.part_id, ta1.purc_order_id

HAVING

count(*) = 2 --Change this to what rank you need.

) AS drv

ondrv.part_id = t.part_id

AND t.purc_order_id >= drv.purc_order_id

Edit: Realized d3 wasn't necessary in the first solution query.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95460 More actions · Answer 5

Lordy, I knew 2005 made it easier... forgot just how much easier! I'm having trouble even thinking along those lines anymore. 🙂

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

WayneS (6/1/2011)
Lordy, I knew 2005 made it easier... forgot just how much easier! I'm having trouble even thinking along those lines anymore. 🙂

You'd laugh to know I built one of these not six months ago for a production system, though I did use the cascading update for that, too much data and not enough transitions. The triangles got huuuuge.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

rhutchinson 13368 SSC Rookie Points: 42 More actions · Answer 7

Leju,

You nailed it!!!! I don't fully understand the code, but thank you very much for being specific!!! I really appreciate your help and thanks again.

WayneS SSC Guru Points: 95460 More actions · Answer 8

rhutchinson 13368 (6/1/2011)
Leju,
You nailed it!!!! I don't fully understand the code, but thank you very much for being specific!!! I really appreciate your help and thanks again.

Might I suggest that you take a good, close look at my signature, and heed it's advice. Ask here for understanding, but don't use that code until you do understand it.

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

rhutchinson 13368 SSC Rookie Points: 42 More actions · Answer 9

Wayne,

While Leju did nail the code (thanks again) and get me the desired results, I am still in a test environment. Now that I have the top 2 PO's per part, I still have numerous other issues to resolve before I'm ready for production...