Also, performance is very good with a compound index on emis_number, status, entry_date, significance.
RBS On Wed, Apr 1, 2015 at 11:06 AM, Bart Smissaert <bart.smissaert at gmail.com> wrote: > Thanks, that does work indeed. My actual real order is now this: > > ORDER BY emis_number asc, status desc, > (case status when 2 then entry_date when 1 then significance end) desc, > entry_date desc > > All working perfect. > Never realised you could do this. > > RBS > > > On Wed, Apr 1, 2015 at 10:03 AM, Simon Slavin <slavins at bigfraud.org> > wrote: > >> >> On 1 Apr 2015, at 9:29am, Bart Smissaert <bart.smissaert at gmail.com> >> wrote: >> >> > This needs to be sorted on column A asc >> > Then when the value in A is 1 the second sort needs to be asc on column >> B, >> > but when the value in A is 2 then the second sort needs to be asc on >> > column C. >> >> You just put it all in your ORDER BY clause. >> >> SELECT * FROM myTable ORDER BY a, (CASE a WHEN 1 THEN b WHEN 2 THEN c >> END) ASC >> >> What you can't do is create an index which perfects suit this clause. >> You'll get the right results but not as quickly as a clause with an ideal >> index. I can think of a few indexes which the planner might take advantage >> of, but not any which are ideal. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > >
