Rank() problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rank() problem

Author

Topic

hello101
Starting Member

1 Post

Posted - 2011-04-20 : 02:14:00

Hi Everyone,
My question is as follows.

Company

company-names Sales
att 500
verizon 100
sprint 400
cox 200
comcast 600
tmobile 800
apple 850

Above is a table Company with 2 columns.

I want to know the nearest competitor of cox which is one competitor above cox(which is sprint) and one competitor below cox(verizon).

The competition is based on the sales number so therefore the result should display the sales and company name if sprint, cox and verizon.

I have used rank.

select company-name,sales, rank() over(sales)as RANK from company

If I execute the query above than I will get the rank of all the company in a column RANK but I want only one above and one below competitor of cox.

Hope you understood.

Thank you.

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-20 : 02:51:07

Hey you are using oracle?

Raghu' S

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-20 : 03:31:32

Possibly this is helpful for you
http://connectsql.blogspot.com/2011/01/sql-server-rownumber-vs-denserank.html

hello110
Starting Member

3 Posts

Posted - 2011-04-20 : 10:43:38

Hi,
I am using SQL server but if you know the answer in Oracle that will be helpful too.

Thank you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-20 : 10:58:55

declare @company table (Co varchar(10),Sales int)

INSERT INTO @Company
SELECT 'att', 500 UNION ALL
SELECT 'verizon', 100 UNION ALL
SELECT 'sprint', 400 UNION ALL
SELECT 'cox', 200 UNION ALL
SELECT 'comcast', 600 UNION ALL
SELECT 'tmobile', 800 UNION ALL
SELECT 'apple', 850

;with cte
as
(

select co,sales,rank() over(order by sales) as rown
from @company
)

select *
from cte

where rown >= (select rown-1 from cte where co = 'cox')
and rown <= (select rown+1 from cte where co = 'cox')

Jim

Everyday I learn something that somebody else already knew

hello110
Starting Member

3 Posts

Posted - 2011-04-20 : 12:07:12

Thank you Jimf,

Is cte like a temporary table which consist the result

select co,sales,rank() over(order by sales) as rown
from @company

can we give another name instead of cte? No right since it is common table expressions

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-20 : 12:15:49

Correct, it is a Common Table Expression, which is like a temporary table -- it goes away after the transaction completes. You can name the cte anythng you want, e.g.
;with RankedSales
as
(

select co,sales,rank() over(order by sales) as rown
from @company
)

select * from RankedSales
Jim

Everyday I learn something that somebody else already knew

hello110
Starting Member

3 Posts

Posted - 2011-04-20 : 12:43:38

Got it. Thank you so much...Appreciate it.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-20 : 12:46:23

You're Welcome!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources