sql order but move some rows bottom

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on
I would like to order a list; sorted by column 1,
but when the elements have the value 100,
move them at the end of the list

A)
if I have 2 columns
a   2
b   1
c  100
d   1
a  100
b   1
c   2
d   1

B)
to sort by column 1
a   2
a  100
b   1
b   1
c   2
c  100
d   1
d   1

C)
but make sure that when I 100 the row is moved under
a   2
b   1
b   1
c   2
d   1
d   1
a  100
c  100

how can solve both with sql and with php? I was interested in both
solutions although I prefer to understand first how can do with sql;

the sql after fill the php array;

if I use ORDER BY column1 ASC, (column2 = 100) ASC
in reality does how in B)

Re: sql order but move some rows bottom

On 03-08-2013 12:44, nawfer wrote:

ORDER BY (column2 = 100) ASC, column1 ASC, column2 ASC
?

Re: sql order but move some rows bottom

Il Sat, 03 Aug 2013 12:53:00 +0200, Luuk ha scritto:

ok thanks, pheraps work; must do some test

other: in case must verify also a 3 column

a   2     x
b   1     x
c  100    x
d   1     x
a  100    x
b   1     y
c   2     x
d   1     x

if  I use your code I can have this  (move bottom the 100)
a   2
b   1
b   1     y
c   2
d   1
d   1
a  100
c  100

but if want also to verified the 3 colum (send bottom also the y) ?
a   2
b   1
c   2
d   1
d   1
a  100
c  100
b   1     y

Re: sql order but move some rows bottom

On 03-08-2013 18:10, nawfer wrote:

ORDER BY (column2 = 100 or column3='Y') ASC, column1 ASC, column2 ASC

Re: sql order but move some rows bottom

ok very very much thanks

I changed to better explain the first column; instead of letters I used
numbers
after sort I have

1
1
2
3
3
4
4
5
5
6
7
8
1     100
1     100
2          y
4          y
5          y
5     100
7     100

ok 100 and y are bottom; but are mixed, or better they are ordered (col 1)
is possible have separated ? y first or after 100

example y first of 100
6
7
8
2          y
4          y
5          y
1     100
1     100
5     100
7     100

____
other last two questions in sql is better use OR  or ||  or is same?

and  col01 <= col02
in this case
col01              col02
null or void filed    <=    10
I noticed that condition isn't verified (for me ok if so) but is always so
for sql? col 01 if haven't a value isn't considered minor of a number?

Re: sql order but move some rows bottom

On 04-08-2013 13:50, nawfer wrote:

y before 100:
ORDER BY
(CASE WHEN column2 = 100 THEN 2 ELSE 0 END + CASE WHEN column3='Y' THEN
1 ELSE 0 END) ASC, column1 ASC, column2 ASC

100 before y
ORDER BY
(CASE WHEN column2 = 100 THEN 1 ELSE 0 END + CASE WHEN column3='Y' THEN
2 ELSE 0 END) ASC, column1 ASC, column2 ASC

i'm not interested in the answer because my opinion is that 'or' is more

http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

Re: sql order but move some rows bottom

Il Sun, 04 Aug 2013 15:13:38 +0200, Luuk ha scritto:

ok wonderful
newly thanks
how really champion (your) last finishing touch
I assuming case where there is a row that have 100 and y

ORDER BY (column2 = 100 or column3='Y') ASC, column1 ASC, column2 ASC
6
7
8
1    100
1    100
2          y
4          y
5          y
5    100   y
5    100
7    100

.......................................................................
1) y before 100:
ORDER BY
(CASE WHEN column2 = 100 THEN 2 ELSE 0 END + CASE WHEN column3='Y' THEN
1 ELSE 0 END) ASC, column1 ASC, column2 ASC

6
7
8
2         y
4         y
5         y
1    100
1    100
5    100
7    100
5    100  y

in this case row  100 y is in the 'group' of the 100 rows; set at the '100
group's end; is possible have first? so:
2         y
4         y
5         y
5    100  y
1    100
1    100
5    100
7    100

........................................................................
2) 100 before y
ORDER BY
(CASE WHEN column2 = 100 THEN 1 ELSE 0 END + CASE WHEN column3='Y' THEN
2 ELSE 0 END) ASC, column1 ASC, column2 ASC

6
7
8
1    100
1    100
5    100
7    100
2         y
4         y
5         y
5    100  y

in this case row  100 y is in the 'group' of the y rows
set at the 'y group's end; is possible have first? so:
1    100
1    100
5    100
7    100
5    100  y
2         y
4         y
5         y

Re: sql order but move some rows bottom

Il Sun, 04 Aug 2013 15:13:38 +0200, Luuk ha scritto:

ok wonderful
newly thanks
how really champion (your) last finishing touch
I assuming case where there is a row that have 100 and y

ORDER BY (column2 = 100 or column3='Y') ASC, column1 ASC, column2 ASC
6
7
8
1    100
1    100
2          y
4          y
5          y
5    100   y
5    100
7    100

.......................................................................
1) y before 100:
ORDER BY
(CASE WHEN column2 = 100 THEN 2 ELSE 0 END + CASE WHEN column3='Y' THEN
1 ELSE 0 END) ASC, column1 ASC, column2 ASC

6
7
8
2         y
4         y
5         y
1    100
1    100
5    100
7    100
5    100  y

in this case row  100 y is in the 'group' of the 100 rows; set at the '100
group's end; is possible have first? so:
2         y
4         y
5         y
5    100  y
1    100
1    100
5    100
7    100

........................................................................
2) 100 before y
ORDER BY
(CASE WHEN column2 = 100 THEN 1 ELSE 0 END + CASE WHEN column3='Y' THEN
2 ELSE 0 END) ASC, column1 ASC, column2 ASC

6
7
8
1    100
1    100
5    100
7    100
2         y
4         y
5         y
5    100  y

in this case row  100 y is in the 'group' of the y rows
set at the 'y group's end; is possible have first? so:
1    100
1    100
5    100
7    100
5    100  y
2         y
4         y
5         y

Re: sql order but move some rows bottom

On 04-08-2013 16:56, nawfer wrote:

If you understood the example i gave you, you should be able to figure
that part out yourself!

Re: sql order but move some rows bottom

ok you are good teacher I will study the code

Re: sql order but move some rows bottom

\$query="SELECT *
FROM table1
ORDER BY
(CASE WHEN column2 = 100 THEN 1 ELSE 0 END + CASE WHEN column3='Y' THEN
2 ELSE 0 END) ASC, column1 ASC, column2 ASC;

is possible to have a
numeration for every row;
the total number of rows
the total number rows but with not the number rows with 100 and y ?

Re: sql order but move some rows bottom

small curiosity
the above code respect at a simple order

ORDER BY column1 ASC, column2 ASC

change the performance (example - 5%)or is insignificant the difference?

Re: sql order but move some rows bottom

On 04-08-2013 17:49, nawfer wrote:

Sorry, my English is not good enough to understand what you mean .....
Could you rephrase the question?

Re: sql order but move some rows bottom

Il Sun, 04 Aug 2013 19:37:32 +0200, Luuk ha scritto:

I ask if the code you post me for does order taht is more complex than a
simple order by make slow the query ; is more slow of 5% 10% or more?

Re: sql order but move some rows bottom

On 04-08-2013 19:53, nawfer wrote:

Yes, its slower, but i dont not know how much slower. With a couple of
records you will not notice the difference, but with 'a lot of records'*
you might notice some delay ;)

*) 'a lot of records' is not defined here ;)

Re: sql order but move some rows bottom

in this select
\$query="SELECT *
FROM table1
ORDER BY
(CASE WHEN column2 = 100 THEN 1 ELSE 0 END + CASE WHEN column3='Y' THEN
2 ELSE 0 END) ASC, column1 ASC, column2 ASC;

is possible to have a

numeration for every row;
the total number of rows
the total number rows but with not the number rows with 100 and y ?