Rank function in SQL - To display the Material in Cost wise.

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Dear Friends,

I want to display the data in an order. Kindly check the below eg.

SQL

Select * Into #AP from (
Select Category='Computer',RATES_MASTER='50000'
union
Select Category='Computer',RATES_MASTER='20000'
union
Select Category='Computer',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='40000'
union
Select Category='Laptop',RATES_MASTER='35000'
union
Select Category='Tab',RATES_MASTER='25000'
union
Select Category='Tab',RATES_MASTER='10000'
union
Select Category='Tab',RATES_MASTER='8000')o

Select * from #AP order by cast (RATES_MASTER as int) desc, category desc

Drop Table #AP

It give me the result as Desc. by Rates.
But actually I want to display the Material which cost is High and then group by Material.
Eg: Computer has high rate, so it should display as first category with all the rates, and then it should take the Laptop category.

Kindly help me on this, Thanks in advance.

Posted 19-Sep-13 3:21am

Arunprasath Natarajan

Add a Solution

Comments

gvprabu 19-Sep-13 10:06am

Hi... I am checking... Some case my solution is giving different answer :-(

gvprabu 19-Sep-13 10:51am

Hi... I updated the solution.. now it will work for all combinations

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Love 2 code · Accepted Answer · 2013-09-19T04:05:00

Hi,
my solution involves a temp table:

SQL

Select * Into #AP from (
Select Category='Computer',RATES_MASTER='50000'
union
Select Category='Computer',RATES_MASTER='20000'
union
Select Category='Computer',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='40000'
union
Select Category='Laptop',RATES_MASTER='35000'
union
Select Category='Tab',RATES_MASTER='25000'
union
Select Category='Tab',RATES_MASTER='10000'
union
Select Category='Tab',RATES_MASTER='8000')o
 
CREATE TABLE #OrderdCats
(
Cat NVARCHAR(100) ,
max_rat int
);

insert into #OrderdCats
Select Category, MAX(cast(Rates_Master As int)) from #AP  Group by Category Order By MAX(cast(Rates_Master As int)) DESC;
 
Select a.* From #AP a, #OrderdCats o WHERE a.Category = o.Cat Order By o.max_rat desc, cast(a.Rates_Master As int) desc;

DROP TABLE #OrderdCats
Drop Table #AP

gvprabu · Accepted Answer · 2013-09-19T03:48:00

Hi,

Can you try like below Query...

SQL

Select * Into #AP from (
Select Category='Computer',RATES_MASTER='50000'
union
Select Category='Computer',RATES_MASTER='20000'
union
Select Category='Computer',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='15000'
union
Select Category='Laptop',RATES_MASTER='40000'
union
Select Category='Laptop',RATES_MASTER='35000'
union
Select Category='Tab',RATES_MASTER='25000'
union
Select Category='Tab',RATES_MASTER='10000'
union
Select Category='Tab',RATES_MASTER='8000')o
 
SELECT M.Category, M.RATES_MASTER
FROM #AP M
INNER JOIN (SELECT TOP 100 PERCENT T.Category, T.RATES_MASTER
			FROM (Select ROW_NUMBER()OVER (PARTITION BY Category ORDER BY CAST(RATES_MASTER AS INT) DESC) 'Rno', Category,RATES_MASTER 
			 FROM #AP ) T
			 WHERE T.Rno =1 
			 ORDER BY CAST(RATES_MASTER AS INT) DESC
			) S ON S.Category=M.Category 
ORDER BY CAST(S.RATES_MASTER AS INT) DESC, CAST(M.RATES_MASTER AS INT) DESC
 
Drop Table #AP

-- Note :I also used same like Solution 2, but with out 2nd Temp Table

Regards,
GVPrabu

Rank function in SQL - To display the Material in Cost wise.

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us