FUDforum - خوراک RDF
http://fudforum.org/forum/index.php
sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182404&th=123122#msg_182404
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)]]>nawfer2013-08-03T10:44:07-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182405&th=123122#msg_182405
> 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
?]]>Luuk2013-08-03T10:53:00-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182406&th=123122#msg_182406
> 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]]>nawfer2013-08-03T16:10:31-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182407&th=123122#msg_182407
> 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]]>Luuk2013-08-03T16:31:13-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182425&th=123122#msg_182425
>>> 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?]]>nawfer2013-08-04T11:50:23-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182427&th=123122#msg_182427
>>>> 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]]>Luuk2013-08-04T13:13:38-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182430&th=123122#msg_182430
> 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]]>anver2013-08-04T14:42:53-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182431&th=123122#msg_182431
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]]>nawfer2013-08-04T14:56:35-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182432&th=123122#msg_182432
> 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?]]>nawfer2013-08-04T15:49:15-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182440&th=123122#msg_182440
> 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!]]>Luuk2013-08-04T17:36:32-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182441&th=123122#msg_182441
>> 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?]]>Luuk2013-08-04T17:37:32-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182443&th=123122#msg_182443
> If you understood the example i gave you, you should be able to figure
> that part out yourself!
ok you are good teacher I will study the code]]>nawfer2013-08-04T17:52:02-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182444&th=123122#msg_182444
> 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?]]>nawfer2013-08-04T17:53:28-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182445&th=123122#msg_182445
> 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 ;)]]>Luuk2013-08-04T18:20:52-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182460&th=123122#msg_182460
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 ?]]>nawfer2013-08-05T12:50:46-00:00Re: sql order but move some rows bottom
http://fudforum.org/forum/index.phpindex.php?t=rview&goto=182461&th=123122#msg_182461
$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 ?]]>nawfer2013-08-05T12:57:31-00:00