FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » sql order but move some rows bottom
Show: Today's Messages :: Polls :: Message Navigator
Switch to threaded view of this topic Create a new topic Submit Reply
sql order but move some rows bottom [message #182404] Sat, 03 August 2013 10:44 Go to next message
nawfer is currently offline  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 Go to previous messageGo to next message
Luuk is currently offline  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 Go to previous messageGo to next message
nawfer is currently offline  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 Go to previous messageGo to next message
Luuk is currently offline  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 Go to previous messageGo to next message
nawfer is currently offline  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 Go to previous messageGo to next message
Luuk is currently offline  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 Go to previous messageGo to next message
anver is currently offline  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 Go to previous messageGo to next message
nawfer is currently offline  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 Go to previous messageGo to next message
nawfer is currently offline  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 Go to previous messageGo to next message
Luuk is currently offline  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 Go to previous messageGo to next message
Luuk is currently offline  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 #182443 is a reply to message #182440] Sun, 04 August 2013 17:52 Go to previous messageGo to next message
nawfer is currently offline  nawfer
Messages: 34
Registered: August 2011
Karma: 0
Member
> 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
Re: sql order but move some rows bottom [message #182444 is a reply to message #182441] Sun, 04 August 2013 17:53 Go to previous messageGo to next message
nawfer is currently offline  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 Go to previous messageGo to next message
Luuk is currently offline  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 Go to previous messageGo to next message
nawfer is currently offline  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 Go to previous message
nawfer is currently offline  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 ?
  Switch to threaded view of this topic Create a new topic Submit Reply
Previous Topic: fetch items from a row
Next Topic: Validate Radio Buttons?
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 03 12:27:03 GMT 2024

Total time taken to generate the page: 0.04408 seconds