CODEWITHSUNDEEP
mysqlsql
Michael
Michael

Reputation: 5335

Change order of rows in a SQL query

I have a table like this (here is a simplified version):

create table t(
  id integer primary key auto_increment,
  room varchar(10),
  kind char(1),
  value double
  );

insert into t(room,kind,value) values
  ('009','T',23),
  ('009','H',35),
  ('All','P',740),
  ('008','T',22),
  ('008','H',35),
  ('106','T',22),
  ('106','H',38),
  ('006','H',40),
  ('006','T',25),
  ('All','P',740);

When I select room and kind, it gives me rows in the order of appearance:

select distinct room, kind from t;

| room | kind |
|------|------|
|  009 |    T |
|  009 |    H |
|  All |    P |
|  008 |    T |
|  008 |    H |
|  106 |    T |
|  106 |    H |
|  006 |    H |
|  006 |    T |

I want the result to be like this:

|  room  | kind |
|--------|------|
|  room1 |    T |
|  room1 |    H |
|  room2 |    T |
|  room2 |    H |
................
|  All   |    P | <-- this should be the last row

i.e. every room should have a kind 'T' then 'H', and the last row should have room='All'. Is it possible to do it without making complicated query?

Upvotes: 1

Views: 443

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I think you are just looking for order by:

select distinct room, kind
from t
order by (room = 'All') asc,  -- put 'All' last
         room, kind desc;

Why does this work? MySQL treats a boolean expression (such as (room = 'All')) as a number, with 1 for true and 0 for false. Hence, the "true" values are sorted after the "false" values.

Note: Your data doesn't seem to have rows with repeated values of room/kind. If you have no duplicates, then use select rather than select distinct.

Upvotes: 1

Vecchiasignora
Vecchiasignora

Reputation: 1315

every string which starts with digit , for example '01', '54154A' always smaller than string which start with 'A' character, for example 'ALL'

try it 

select distinct room, kind from t order by room

Upvotes: 0

Abderrahim Soubai-Elidrisi
Abderrahim Soubai-Elidrisi

Reputation: 5202

simply

select distinct kind,room  from t;

Upvotes: 0

Related Questions