[sqlite] : it seems to be only orber by and group

[R.Smith]

This will work great - just a correction, there should be a comma after 
"columnB" in the order by clause, else it might not parse, so the 
revised is:

SELECT * FROM table
  ORDER BY columnA, columnB,
  CASE WHEN columnC = 1
  THEN 0 ELSE 1 END;



On 2015-08-22 10:47 PM, Kevin Benson wrote:
> On Sat, Aug 22, 2015
>
>> afriendandmore wrote:
>>> The table shall be ordered according to A. (Order by A)
>>> But if C ist true and to the value B1 in this Record, there exists
>> another Record with B2 and B2 = B1, then B1 should be ordered in the row
>> above B2.
>
> If the B1 and B2, to which you refer, are just two equal values in
> different rows of column B, then it sounds like you want:
>
> SELECT * FROM table
>   ORDER BY columnA, columnB
>   CASE WHEN columnC = 1
>   THEN 0 ELSE 1 END;
>
> If neither ASC or DESC are specified, rows are sorted in ascending (smaller
> values first) order by default.
> --
> --
>--
>   --???--
>  K e V i N
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] : it seems to be only orber by and group

[Clemens Ladisch]

afriendandmore wrote:
> The table shall be ordered according to A. (Order by A)
> But if C ist true and to the value B1 in this Record, there exists another 
> Record with B2 and B2 = B1, then B1 should be ordered in the row above B2.

Do those two records have the same A values?


Regards,
Clemens

[sqlite] : it seems to be only orber by and group

[Kevin Benson]

On Sat, Aug 22, 2015

> afriendandmore wrote:
> > The table shall be ordered according to A. (Order by A)
> > But if C ist true and to the value B1 in this Record, there exists
> another Record with B2 and B2 = B1, then B1 should be ordered in the row
> above B2.


If the B1 and B2, to which you refer, are just two equal values in
different rows of column B, then it sounds like you want:

SELECT * FROM table
 ORDER BY columnA, columnB
 CASE WHEN columnC = 1
 THEN 0 ELSE 1 END;

If neither ASC or DESC are specified, rows are sorted in ascending (smaller
values first) order by default.
--
   --
  --
 --???--
K e V i N

[sqlite] : it seems to be only orber by and group

[afriendandmore]

but I fail to get it done.


Hello,

I got the following problem, that I cannot get worked out.

I have a table that has 3 columns A, B and C.
C is a boolean Column, that indicates if the record has been logically 
deleted, It shall not be removed from the database.
The table shall be ordered according to A. (Order by A)
But if C ist true and to the value B1 in this Record, there exists 
another Record with B2 and B2 = B1, then B1 should be ordered in the row 
above B2.

B



A



C 1=true, 0 = false

307



{null}



1

307



{null}



1

307



{null}



1

307



1.7



0

301



1,5



1

301



2



0

304



3



0

305



3



1

305



3



0


Thank you very much for any help.