| sql order but move some rows bottom [message #182404] |
Sat, 03 August 2013 10:44  |
nawfer
Messages: 34
Registered: August 2011
Karma: 0
|
Member |
|
|
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 [message #182405 is a reply to message #182404] |
Sat, 03 August 2013 10:53  |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 03-08-2013 12:44, nawfer wrote:
> 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)
>
ORDER BY (column2 = 100) ASC, column1 ASC, column2 ASC
?
|
|
|
|
| Re: sql order but move some rows bottom [message #182406 is a reply to message #182405] |
Sat, 03 August 2013 16:10 |
nawfer
Messages: 34
Registered: August 2011
Karma: 0
|
Member |
|
|
Il Sat, 03 Aug 2013 12:53:00 +0200, Luuk ha scritto:
> On 03-08-2013 12:44, nawfer wrote:
>> 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)
>>
>
>
> ORDER BY (column2 = 100) ASC, column1 ASC, column2 ASC
> ?
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 [message #182407 is a reply to message #182406] |
Sat, 03 August 2013 16:31 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 03-08-2013 18:10, nawfer wrote:
> Il Sat, 03 Aug 2013 12:53:00 +0200, Luuk ha scritto:
>
>> On 03-08-2013 12:44, nawfer wrote:
>>> 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
>>>
>> ORDER BY (column2 = 100) ASC, column1 ASC, column2 ASC
>> ?
>
> but if want also to verified the 3 colum (send bottom also the y) ?
ORDER BY (column2 = 100 or column3='Y') ASC, column1 ASC, column2 ASC
|
|
|
|
| Re: sql order but move some rows bottom [message #182425 is a reply to message #182407] |
Sun, 04 August 2013 11:50 |
nawfer
Messages: 34
Registered: August 2011
Karma: 0
|
Member |
|
|
>>> ORDER BY (column2 = 100) ASC, column1 ASC, column2 ASC
>>> ?
>>
>> but if want also to verified the 3 colum (send bottom also the y) ?
>
> ORDER BY (column2 = 100 or column3='Y') ASC, column1 ASC, column2 ASC
ok very very much thanks
Your last touch to perfection
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 [message #182427 is a reply to message #182425] |
Sun, 04 August 2013 13:13 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 04-08-2013 13:50, nawfer wrote:
>>>> ORDER BY (column2 = 100) ASC, column1 ASC, column2 ASC
>>>> ?
>>>
>>> but if want also to verified the 3 colum (send bottom also the y) ?
>>
>> ORDER BY (column2 = 100 or column3='Y') ASC, column1 ASC, column2 ASC
>
>
> ok very very much thanks
> Your last touch to perfection
>
> 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
>
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
>
> ____
> other last two questions in sql is better use OR or || or is same?
read the manual,
i'm not interested in the answer because my opinion is that 'or' is more
readable ;)
>
> 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?
>
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
|
|
|
|
| Re: sql order but move some rows bottom [message #182430 is a reply to message #182427] |
Sun, 04 August 2013 14:42 |
anver
Messages: 5
Registered: November 2010
Karma: 0
|
Junior Member |
|
|
Il Sun, 04 Aug 2013 15:13:38 +0200, Luuk ha scritto:
> On 04-08-2013 13:50, nawfer wrote:
>>>> > ORDER BY (column2 = 100) ASC, column1 ASC, column2 ASC
>>>> > ?
>>>>
>>>> but if want also to verified the 3 colum (send bottom also the y) ?
>>>
>>> ORDER BY (column2 = 100 or column3='Y') ASC, column1 ASC, column2 ASC
>>
>>
>> ok very very much thanks
>> Your last touch to perfection
>>
>> 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
>>
>
> 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
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 [message #182431 is a reply to message #182427] |
Sun, 04 August 2013 14:56 |
nawfer
Messages: 34
Registered: August 2011
Karma: 0
|
Member |
|
|
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 [message #182432 is a reply to message #182427] |
Sun, 04 August 2013 15:49 |
nawfer
Messages: 34
Registered: August 2011
Karma: 0
|
Member |
|
|
> 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
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 [message #182440 is a reply to message #182431] |
Sun, 04 August 2013 17:36 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 04-08-2013 16:56, nawfer wrote:
> 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
>
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 [message #182441 is a reply to message #182432] |
Sun, 04 August 2013 17:37 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 04-08-2013 17:49, 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
>
> 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?
>
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 [message #182444 is a reply to message #182441] |
Sun, 04 August 2013 17:53 |
nawfer
Messages: 34
Registered: August 2011
Karma: 0
|
Member |
|
|
Il Sun, 04 Aug 2013 19:37:32 +0200, Luuk ha scritto:
> On 04-08-2013 17:49, 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
>>
>> 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?
>>
>
>
> Sorry, my English is not good enough to understand what you mean .....
> Could you rephrase the question?
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 [message #182445 is a reply to message #182444] |
Sun, 04 August 2013 18:20 |
Luuk
Messages: 329
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 04-08-2013 19:53, nawfer wrote:
> Il Sun, 04 Aug 2013 19:37:32 +0200, Luuk ha scritto:
>
>> On 04-08-2013 17:49, 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
>>>
>>> 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?
>>>
>>
>>
>> Sorry, my English is not good enough to understand what you mean .....
>> Could you rephrase the question?
>
> 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?
>
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 [message #182460 is a reply to message #182440] |
Mon, 05 August 2013 12:50 |
nawfer
Messages: 34
Registered: August 2011
Karma: 0
|
Member |
|
|
$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 [message #182461 is a reply to message #182445] |
Mon, 05 August 2013 12:57 |
nawfer
Messages: 34
Registered: August 2011
Karma: 0
|
Member |
|
|
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 ?
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]