How to sort record with field value in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi Everyone,

I have a table called Result and result table contains following columns.

Table -- Result

Columns--> ResultId, CategoryId, WeightFrom, PlayerName, Result

and user insert the record in this table like this.

ResultId | CategoryId | WeightFrom | PlayerName | Result
1          1           20.0          Ashu        Silver
2          1           20.0          Pawan       Gold
3          1           22            Mahesh      Bronze
4          1           23            Ram         Bronze
5          2           23.00         Rajesh      Bronze
6          2           22.00         Rakesh      Gold
7          2           20.00         Vikas       Silver
8          2           21            Suraj       Bronze

Now my question is I want to sort record using categoryId,WeightFrom,Result base

but main issue is that when i sort the record like this.

Select PlayerName,Result from Result order by CategoryId,WeightFrom, Result asc

Output is:

Ashu Silver
Pawan Gold
mahesh Bronze

But i want to sort record like this,

Pawan Gold
Ashu Silver
Mahesh Broze
Ram Bronze

I want to display first Gold, Silver, Bronze Record but category or weight from wise.

Please help me what i do for this.

Posted 11-Feb-15 20:50pm

Ram Kumar(Webunitech)

Updated 11-Feb-15 21:05pm

Tomas Takac

v2

Add a Solution

Comments

Tomas Takac 12-Feb-15 3:16am

I'd say the output of your query as it is now should be Pawan, Ashu, Mahesh, Ram, Vikas, Suraj, Rakesh, Rajesh. Just because Pawan and Ashu have the same category and weight and Gold comes before Silver alphabetically. In category 2 the order is decide by weight. The problem is Result is a string and alphabetic order Bronze-Gold-Silver is not correct. But as i said this plays very little role in your current dataset. I'm not entirely sure how would you like the rows to be sorted. Can you provide full expected result?

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)

Suvendu Shekhar Giri · Accepted Answer · 2015-02-11T21:47:00

This can be achieved through "CASE .. WHEN.." like following

SQL

ORDER BY CASE WHEN Result='Gold' THEN 1 WHEN Result='Silver' THEN 2 ELSE 3 END,CategoryId,WeightFrom

For example :

SQL

SELECT * FROM
(
	SELECT 1 ResultId,1 CategoryId,20.0 WeightFrom,'Ashu' PlayerName,'Silver' Result
	UNION
	SELECT 2,          1,           20.0,          'Pawan',       'Gold'
	UNION
	SELECT 3,          1,           22,            'Mahesh',      'Bronze'
	UNION
	SELECT 4,          1,           23,            'Ram',         'Bronze'
	UNION
	SELECT 5,          2,           23.00,         'Rajesh',      'Bronze'
	UNION
	SELECT 6,          2,           22.00,         'Rakesh',      'Gold'
	UNION
	SELECT 7,          2,           20.00,         'Vikas',       'Silver'
	UNION
	SELECT 8,          2,           21,            'Suraj',       'Bronze'
) AS T
ORDER BY CASE WHEN Result='Gold' THEN 1 WHEN Result='Silver' THEN 2 ELSE 3 END,CategoryId,WeightFrom

Result will be like:

ResultId	CategoryId	WeightFrom	PlayerName	Result
2	          1	           20.00	Pawan	      Gold
6	          2	           22.00	Rakesh	      Gold
1	          1	           20.00	Ashu	      Silver
7	          2	           20.00	Vikas	      Silver
3	          1	           22.00	Mahesh	      Bronze
4	          1	           23.00	Ram	      Bronze
8	          2	           21.00	Suraj	      Bronze
5	          2	           23.00	Rajesh	      Bronze

Now you can filter the data as per your requirement, may be using some ranking functions.
In case of any further assistance is required, please let me know :)

Mehdi Gholam · Accepted Answer · 2015-02-11T21:29:00

Solution 1

You can custom sort with CASE in your ORDER BY: http://sqlandme.com/2013/11/18/sql-server-custom-sorting-in-order-by-clause/[^]

Posted 11-Feb-15 21:29pm

Mehdi Gholam