SQL Query ORDER BY column values

Question

I have a column which can take any values from 0 to 100.

Now i have a type TYPE1 which can take values 2, 4, 16

TYPE2 which can take values 8,12,64.

Now i want to sort the column by TYPE1 values first and then TYPE2 values. Is there any way to do that.

My column has only these values. It does not know the Type1 or Type2. I am using SQL2005

NOTE: TYPE1 and TYPE2 are not columns. They are just logical entities.

"My column has only these values" -- how does your one column hold three values e.g. are you using CREATE TYPE TYPE1 AS TABLE...? — onedaywhen
– onedaywhen, Commented Jun 24, 2011 at 9:35
By these values i mean they hold numbers ranging from 0 to 100 in each no row. It does not hold 3 values in a single column for a row. — ckv
– ckv, Commented Jun 24, 2011 at 9:37

Sarfraz · Accepted Answer · 2011-06-24 09:10:53Z

3

You can specify both columns in ORDER BY caluse:

SELECT * FROM yourTable ORDER BY Type1, Type2

This would by default sort the fields in ascending order. If you want to order in reverse, you can use DESC keyword just after column names.

answered Jun 24, 2011 at 9:10

Sarfraz

384k81 gold badges561 silver badges613 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sarfraz Over a year ago

@ckv: You can replace Type1 and Type2 with your actual column names you want to sort against, the syntax will be the same.

ckv Over a year ago

Hey Sarfraz, Yeah i can replace the column name with exact name but the problem is i will get sorted in a mixed way. I want to sort in such a way that i get 2, 4, 16 as one set and rest as other. Because the TYPE2 has a value 8 which will come in between.

Maxim Krizhanovsky · Accepted Answer · 2011-06-24 09:11:36Z

2

What are TYPE1 and TYPE2 and what is your database? In MySQL you can do ORDER BY FIELD(columnnane, 2,4,16,8,12,64)

answered Jun 24, 2011 at 9:11

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

Comments

niktrs · Accepted Answer · 2011-06-24 09:11:32Z

0

SELECT *
FROM table
ORDER BY type1, type2 -- the columns you want to sort in order

answered Jun 24, 2011 at 9:11

niktrs

10.1k1 gold badge32 silver badges32 bronze badges

Comments

Kinexus · Accepted Answer · 2011-06-24 09:12:42Z

0

You can add multiple fields to the Order By Clause

select type1, type2 from table order by type1, type2

edited Jun 24, 2011 at 9:12

answered Jun 24, 2011 at 9:11

Kinexus

12.9k6 gold badges43 silver badges63 bronze badges

Comments

Nuno Veloso · Accepted Answer · 2022-03-25 09:54:57Z

0

use a case to separate type 1 and type 2, then order by the column:

SELECT * FROM yourTable ORDER BY case when column in (2,4,16) then 1, else 2 end,column

This way, first you order by type 1 or 2, them order by value of the column

answered Mar 25, 2022 at 9:54

Nuno Veloso

11 bronze badge

Collectives™ on Stack Overflow

SQL Query ORDER BY column values

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related