Grouped Order By - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Grouped Order By

Author

Topic

asoltys
Starting Member

8 Posts

Posted - 2008-05-23 : 13:44:39

Hello,

I have a 'competencies' table and a 'categories' table. A competency has a name and a category_id. There are four categories altogether:

'Core'
'Leadership'
'Knowledge'
'Other'

I want to perform a query that will return a record set with the 'Core', 'Leadership', and 'Other' competencies listed alphabetically at the top followed by the 'Knowledge' competencies listed alphabetically at the bottom.

Is this possible with a single query?

Thanks,

Adam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 13:46:24

Can you illustrate your requirement with some sample data?

asoltys
Starting Member

8 Posts

Posted - 2008-05-23 : 14:00:12

Sure,

Let's say I have the following categories:

id | name
1 | Core
2 | Leadership
3 | Other
4 | Knowledge

And I have these competencies:

name | category_id
Adaptability | 1
Advice | 2
Analytical Thinking | 3
Client Focus | 2
Consulting | 3
Engagement | 1
Influence | 2
Initiative | 3
Knowledge of management practices | 4
Knowledge of acts and regulations | 4
Knowledge of branch programs | 4
Negotiating | 1
Problem Management | 2

I want to get a recordset ordered like this:

Adaptability
Advice
Analytical Thinking
Client Focus
Consulting
Engagement
Influence
Initiative
Negotiating
Problem Management
Knowledge of acts and regulations
Knowledge of branch programs
Knowledge of management practices

The thing to note is that everything is alphabetical regardless of category_id EXCEPT for the knowledges with category id of 4 which must appear at the bottom rather than in between 'Initiative' and 'Negotiation'.

I hope this makes sense. Let me know if I need to clarify anything.

Adam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 14:15:19

[code]SELECT t.name
FROM
(SELECT name,category_id,
case when category_id=4 then 2 else 1 end as OrderValue
FROM competencies)t
ORDER BY t.OrderValue,t.name[/code]

asoltys
Starting Member

8 Posts

Posted - 2008-05-23 : 14:34:52

Thanks visakh, it worked great.

Adam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-26 : 06:04:22

or just

SELECT name,
case when category_id=4 then 2 else 1 end as OrderValue
FROM competencies
ORDER BY OrderValue,name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources