| 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:
|
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
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]