Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #10355 > unrolled thread

sql order but move some rows bottom

Started bynawfer <novalidsforspam@alt.al>
First post2013-08-03 12:44 +0200
Last post2013-08-05 14:57 +0200
Articles 16 — 3 participants

Back to article view | Back to comp.lang.php

Contents

  sql order but move some rows bottom nawfer <novalidsforspam@alt.al> - 2013-08-03 12:44 +0200
    Re: sql order but move some rows bottom Luuk <luuk@invalid.lan> - 2013-08-03 12:53 +0200
      Re: sql order but move some rows bottom nawfer <novalidsforspam@alt.al> - 2013-08-03 18:10 +0200
        Re: sql order but move some rows bottom Luuk <luuk@invalid.lan> - 2013-08-03 18:31 +0200
          Re: sql order but move some rows bottom nawfer <novalidsforspam@alt.al> - 2013-08-04 13:50 +0200
            Re: sql order but move some rows bottom Luuk <luuk@invalid.lan> - 2013-08-04 15:13 +0200
              Re: sql order but move some rows bottom anver <NOSPAMM@NO.SPAMMS> - 2013-08-04 16:42 +0200
              Re: sql order but move some rows bottom nawfer <novalidsforspam@alt.al> - 2013-08-04 16:56 +0200
                Re: sql order but move some rows bottom Luuk <luuk@invalid.lan> - 2013-08-04 19:36 +0200
                  Re: sql order but move some rows bottom nawfer <novalidsforspam@alt.al> - 2013-08-04 19:52 +0200
                  Re: sql order but move some rows bottom nawfer <novalidsforspam@alt.al> - 2013-08-05 14:50 +0200
              Re: sql order but move some rows bottom nawfer <novalidsforspam@alt.al> - 2013-08-04 17:49 +0200
                Re: sql order but move some rows bottom Luuk <luuk@invalid.lan> - 2013-08-04 19:37 +0200
                  Re: sql order but move some rows bottom nawfer <novalidsforspam@alt.al> - 2013-08-04 19:53 +0200
                    Re: sql order but move some rows bottom Luuk <luuk@invalid.lan> - 2013-08-04 20:20 +0200
                      Re: sql order but move some rows bottom nawfer <novalidsforspam@alt.al> - 2013-08-05 14:57 +0200

#10355 — sql order but move some rows bottom

Fromnawfer <novalidsforspam@alt.al>
Date2013-08-03 12:44 +0200
Subjectsql order but move some rows bottom
Message-ID<1qthmi1tu68bo$.9vs28kns3wda.dlg@40tude.net>
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)

[toc] | [next] | [standalone]

#10356

FromLuuk <luuk@invalid.lan>
Date2013-08-03 12:53 +0200
Message-ID<b14vca-jku.ln1@luuk.invalid.lan>
In reply to#10355
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
?

[toc] | [prev] | [next] | [standalone]

#10357

Fromnawfer <novalidsforspam@alt.al>
Date2013-08-03 18:10 +0200
Message-ID<13413lih7j7xy.ntybohdhh9wy.dlg@40tude.net>
In reply to#10356
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

[toc] | [prev] | [next] | [standalone]

#10358

FromLuuk <luuk@invalid.lan>
Date2013-08-03 18:31 +0200
Message-ID<frnvca-j63.ln1@luuk.invalid.lan>
In reply to#10357
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

[toc] | [prev] | [next] | [standalone]

#10376

Fromnawfer <novalidsforspam@alt.al>
Date2013-08-04 13:50 +0200
Message-ID<19m253q4wpnqg.3aexf4o8h0ae$.dlg@40tude.net>
In reply to#10358
>>> 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?

[toc] | [prev] | [next] | [standalone]

#10378

FromLuuk <luuk@invalid.lan>
Date2013-08-04 15:13 +0200
Message-ID<1l02da-q4u.ln1@luuk.invalid.lan>
In reply to#10376
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

[toc] | [prev] | [next] | [standalone]

#10381

Fromanver <NOSPAMM@NO.SPAMMS>
Date2013-08-04 16:42 +0200
Message-ID<rvwsyz3mrt0c$.1u01rcayrb8lq.dlg@40tude.net>
In reply to#10378
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

[toc] | [prev] | [next] | [standalone]

#10382

Fromnawfer <novalidsforspam@alt.al>
Date2013-08-04 16:56 +0200
Message-ID<1tsi9ibmoa4l7$.ni03ixud0whf$.dlg@40tude.net>
In reply to#10378
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

[toc] | [prev] | [next] | [standalone]

#10392

FromLuuk <luuk@invalid.lan>
Date2013-08-04 19:36 +0200
Message-ID<v1g2da-vl1.ln1@luuk.invalid.lan>
In reply to#10382
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!

[toc] | [prev] | [next] | [standalone]

#10394

Fromnawfer <novalidsforspam@alt.al>
Date2013-08-04 19:52 +0200
Message-ID<1f3z3fwn5typr$.1lc9jbjf47253$.dlg@40tude.net>
In reply to#10392
> 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

[toc] | [prev] | [next] | [standalone]

#10411

Fromnawfer <novalidsforspam@alt.al>
Date2013-08-05 14:50 +0200
Message-ID<jw9ivlansyw7.19sv6rl3hxxp9.dlg@40tude.net>
In reply to#10392
$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 ?

[toc] | [prev] | [next] | [standalone]

#10383

Fromnawfer <novalidsforspam@alt.al>
Date2013-08-04 17:49 +0200
Message-ID<cilihgucmwnw.1jmbvfrj3djn8.dlg@40tude.net>
In reply to#10378
> 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?

[toc] | [prev] | [next] | [standalone]

#10391

FromLuuk <luuk@invalid.lan>
Date2013-08-04 19:37 +0200
Message-ID<q3g2da-vl1.ln1@luuk.invalid.lan>
In reply to#10383
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?

[toc] | [prev] | [next] | [standalone]

#10395

Fromnawfer <novalidsforspam@alt.al>
Date2013-08-04 19:53 +0200
Message-ID<esac9bff8sko.tmz61t9wiv4.dlg@40tude.net>
In reply to#10391
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?

[toc] | [prev] | [next] | [standalone]

#10396

FromLuuk <luuk@invalid.lan>
Date2013-08-04 20:20 +0200
Message-ID<2li2da-282.ln1@luuk.invalid.lan>
In reply to#10395
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 ;)

[toc] | [prev] | [next] | [standalone]

#10412

Fromnawfer <novalidsforspam@alt.al>
Date2013-08-05 14:57 +0200
Message-ID<1smx7ufa0utah$.1e3rpvvm9jnbi$.dlg@40tude.net>
In reply to#10396
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 ?

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web