Looking for Number Two
August 2, 2002 Timothy Prickett Morgan
Hey, Ted:
I have a file of sales reps and the amount of sales, in dollars, so far this year. I can easily find the top producer using SQL.
select repid, amt from sales2 where amt =
(select max(amt) from sales2)
|
How do I find the number-two producer?
— Roger
There are probably several ways to answer your question, Roger.
First, here’s the raw data that I’ll use to illustrate:
REPID AMT JLM1 25,922 NTP2 177,208 LJS2 15,424 CRC0 122,730 HFH1 95,682 JKS0 76,903 JLM2 55,088 JTL4 99,944 MWS0 12,155 BRS1 54,673
To find the number two producer:
select repid, amt from sales2 as a where 1 =
(select count(*) from sales2 as b where
b.amt > a.amt)
For each row (record), the inner select produces the number of reps with a higher sales figure. The number two producer is the one who has only one person with a higher sales figure, thus the number one appears before the equal sign.
The query returns one row.
REPID AMT CRC0 122,730
If you wanted to find the third highest producer, you would replace the 1 with a 2.
select repid, amt from sales2 as a where 2 =
(select count(*) from sales2 as b where
b.amt > a.amt)
Again, the query returns one row.
REPID AMT JTL4 99,944
If you want to list the top two producers, change the equal sign to a greater-than-or-equal relation.
select repid, amt from sales2 as a where 1 >=
(select count(*) from sales2 as b where
b.amt > a.amt)
The query returns two rows.
REPID AMT NTP2 177,208 CRC0 122,730
— Ted
Sponsored By CENTERFIELD TECHNOLOGY
|
|
$10,000 of Consulting for only $925 Not only do we take the guesswork out of iSeries DASD Management with disk/HUNTER, but Centerfield Technology also offers a robust set of iSeries tools for application performance and user access control and diagnostics. Check out our low cost/high value service offering, the SQL Insurance Analysis. For only $925 we will show you how to increase the performance of your business process and how to secure your exposed critical files. Our analysis is geared toward any one using SQL-based applications on the iSeriesJ.D Edwards OneWorld® and WorldSoftware®, MAPICS, BPCS, Web Sphere, Data Warehousing, applications developed in-house, etc. Key Benefits of SQL Insurance Analysis
This extensive program set combines a methodology and tools to simplify the complex and time-consuming process of controlling and tuning SQL applications. Our easy, low cost analysis will help you fix availability problems now, before significant cost/damage occurs. And, we’ll show you how to control application performance and query access permanently – 24/7! Click here for more information. |
