March 21, 2008 at 2:51 am
Hello
I have a table that consist of product items and their unit price. Can anyone help me create a script or a guideline on how I can select the product item with the 4th highest price?
March 21, 2008 at 4:49 am
There can be so many ways to do it:
Following are two of the ways; one using the top clause and the other using the ranking function row_number()of the SQL Server 2k5
Both of the below queries will return the 5th rows concidering the PaymentID for ranking
1. Using the top clause:
select top 1 * from TopDemo a
where PaymentID in (select top 5 PaymentID from TopDemo)
order by PaymentID desc
2. Using the row_number()
SELECT *
FROM
(
SELECT a.*, row_number() OVER (
ORDER BY a.PaymentID) AS Rank
FROM TopDemo a) AS t
WHERE
t.Rank = 5
The sample data for the above two queries is as follows:
create table TopDemo (
PaymentID int identity,
Amount money,
PayDate DateTime
)
insert into TopDemo(Amount, PayDate) values (12.23,'1/2/2006')
insert into TopDemo(Amount, PayDate) values (73.18,'1/10/2006')
insert into TopDemo(Amount, PayDate) values (92.20,'1/21/2006')
insert into TopDemo(Amount, PayDate) values (62.20,'1/21/2006')
insert into TopDemo(Amount, PayDate) values (12.45,'1/23/2006')
insert into TopDemo(Amount, PayDate) values (10.99,'2/4/2006')
insert into TopDemo(Amount, PayDate) values (34.23,'2/7/2006')
insert into TopDemo(Amount, PayDate) values (34.87,'2/12/2006')
insert into TopDemo(Amount, PayDate) values (58.32,'2/15/2006')
insert into TopDemo(Amount, PayDate) values (34.23,'2/23/2006')
insert into TopDemo(Amount, PayDate) values (98.26,'3/1/2006')
insert into TopDemo(Amount, PayDate) values (45.32,'3/15/2006')
insert into TopDemo(Amount, PayDate) values (47.30,'3/16/2006')
insert into TopDemo(Amount, PayDate) values (35.21,'3/24/2006')
insert into TopDemo(Amount, PayDate) values (57.23,'3/26/2006')
insert into TopDemo(Amount, PayDate) values (24.56,'3/30/2006')
insert into TopDemo(Amount, PayDate) values (34.23,'4/4/2006')
insert into TopDemo(Amount, PayDate) values (23.99,'4/6/2006')
insert into TopDemo(Amount, PayDate) values (56.32,'4/8/2006')
insert into TopDemo(Amount, PayDate) values (47.53,'6/25/2006')
insert into TopDemo(Amount, PayDate) values (30.56,'7/7/2006')
insert into TopDemo(Amount, PayDate) values (65.34,'7/8/2006')
insert into TopDemo(Amount, PayDate) values (44.55,'8/4/2006')
insert into TopDemo(Amount, PayDate) values (84.23,'8/8/2006')
insert into TopDemo(Amount, PayDate) values (23.56,'8/22/2006')
insert into TopDemo(Amount, PayDate) values (45.77,'9/12/2006')
insert into TopDemo(Amount, PayDate) values (65.90,'9/25/2006')
Best regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
March 21, 2008 at 5:49 am
In case of situation where yopu have multiple products with the 4th highest price, you need to modify the quieries a little.
such as (use the with ties option with the top clause):
select * from TopDemo where PaymentID in
(select top 1 PaymentID from TopDemo a
where PaymentID in (select top 5 with ties PaymentID from TopDemo order by PaymentID )
order by PaymentID desc)
Best regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
March 21, 2008 at 7:32 am
Using ROW_NUMBER in this case makes the query more complicated than it needs to be because Row_Number does not incorporate ties. You should use the rank function. This way if a the 4 highest price is tied with another product, both will be returned. Your query should look like this.
SELECT *
FROM(
SELECT a.*,
Rank() OVER(ORDER BY a.Amount DESC) AS Rank
FROM #TopDemo a
) AS t
WHERE t.Rank = 4
March 21, 2008 at 9:30 am
Thanks Adam. You are right. This is the required modification in the 2nd query which I pointed to in my 2nd post.
Best regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
March 21, 2008 at 11:07 am
Depending on how you count it, Dense_Rank might be better than Rank in a situation like this.
For example, if you have a tie for first place, the second highest number will be Rank 3, not Rank 2, and if you happen to have a tie for 3rd place, you won't have a Rank 4 at all.
Value Rank Dense_Rank
1011
1011
932
932
932
863
774
Is a sample of how these work. Thus, if you use Rank(), sometimes you won't get any values from your Where clause, unless you can guarantee that the numbers you are ranking are distinct.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2008 at 12:01 pm
So very true. Dense_Rank fits the OP needs much better than rank in this particular scenario. Thanks for posting the samples and clarying your position. 🙂
March 21, 2008 at 12:02 pm
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply