CODEWITHSUNDEEP
phpmysql
dojogeorge
dojogeorge

Reputation: 1704

MySQL Order by specific rows only

I have a MySQL query that returns results in the following way:

id | count | type
------------------
1  | 1000  | 1
2  | 100   | 2
3  | 80    | 2

I would like to order the results only by rows where the type is 2. The order of the other rows does not matter, although control over order by them could be useful down the line. The resulting order would therefore be id = 2 then id = 3 then id = 1. Is this possible without doing so in post-processing?

Upvotes: 1

Views: 1313

Answers (4)

Venkatesh Panabaka
Venkatesh Panabaka

Reputation: 2154

I think these sql useful to you

select * from table where type=2
union
select * from table

Thank you.

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

You can use field:

SELECT * FROM TableName ORDER BY field(type,2,1)

Result:

id  count   type
----------------
2   100     2
3   80      2
1   1000    1

See demo in SQL Fiddle

NB: You can use as many parameters as you want.

Ex: field(type,2,1,6,3,5,4)

It will order the result in the order you specified in field()

If you don't want to specify all values, you can use desc at the end by specifying the parameters reverse.

ex: 

SELECT * FROM TableName ORDER BY field(type,3,2,5) desc

Fiddle demo

Upvotes: 0

deceze
deceze

Reputation: 522382

If you want to order rows with type 2 first and anything else after:

SELECT ... ORDER BY IF(type = 2, 0, 1) ASC

Upvotes: 5

user128161
user128161

Reputation:

If you want to order by a column, it's:

SELECT * FROM table ORDER BY type ASC;

If you want to select only rows that type = 2, then it's:

SELECT * FROM table WHERE type = '2'

Upvotes: 0

Related Questions