Thread: help with query: advanced ORDER BY...
hi list here's what i would like to do: table1: field0: id field1: boolean field2: numeric field3: varchar when selecting records from this table, i would like to have different sort orders depending if field1 is true or false: all records with field1 = true should be sorted with field2 first then field3, all records having field2 = false sorted by field3 first then field2. example: field0 | field1 | field 2 | field3 ------------------------------------------- 1 t 2 b 2 f 1 z 3 t 1 a 4 f 4 a desired output after sorting 4 f 4 a 2 f 1 z 3 t 1 a 1 t 2 b is this possible in a single query? (i hope my explanations make sense ;-)) - thomas
On Sat, Jan 14, 2006 at 05:47:41PM +0100, me@alternize.com wrote:
> when selecting records from this table, i would like to have different sort
> orders depending if field1 is true or false: all records with field1 = true
> should be sorted with field2 first then field3, all records having field2 =
> false sorted by field3 first then field2.
Something like this might work:
SELECT * FROM table1
ORDER BY field1,
CASE WHEN field1 THEN field2 ELSE NULL END,
CASE WHEN field1 THEN field3 ELSE NULL END,
CASE WHEN field1 THEN NULL ELSE field3 END,
CASE WHEN field1 THEN NULL ELSE field2 END;
If field2 and field3 were the same type then you could shorten the
query to:
SELECT * FROM table1
ORDER BY field1,
CASE WHEN field1 THEN field2 ELSE field3 END,
CASE WHEN field1 THEN field3 ELSE field2 END;
The extra CASE statements in the first form are necessary if field2
and field3 are different types; with the shorter version you'd get
an error like:
ERROR: CASE types character varying and numeric cannot be matched
--
Michael Fuhr
> Something like this might work: > > SELECT * FROM table1 > ORDER BY field1, > CASE WHEN field1 THEN field2 ELSE NULL END, > CASE WHEN field1 THEN field3 ELSE NULL END, > CASE WHEN field1 THEN NULL ELSE field3 END, > CASE WHEN field1 THEN NULL ELSE field2 END; it indeed does, thanks alot. will pgsql still use the indices when ordering through CASE? cheers, thomas
On Sat, Jan 14, 2006 at 09:32:03PM +0100, me@alternize.com wrote: > >Something like this might work: > > > >SELECT * FROM table1 > >ORDER BY field1, > > CASE WHEN field1 THEN field2 ELSE NULL END, > > CASE WHEN field1 THEN field3 ELSE NULL END, > > CASE WHEN field1 THEN NULL ELSE field3 END, > > CASE WHEN field1 THEN NULL ELSE field2 END; > > > it indeed does, thanks alot. will pgsql still use the indices when ordering > through CASE? I don't think so, unless you define a multicolumn index on those specific expressions. Why the different ordering? What are you doing? -- Michael Fuhr
> I don't think so, unless you define a multicolumn index on those
> specific expressions.
>
> Why the different ordering? What are you doing?
here's what we currently need it for (simplified):
the movie schedules for theatres normally contains the information
theater_id, screen_date, screen_time, screen_number, movie_name and should
be outputed accordingly. it now happens to have some cinema multiplex owners
who for some reasons do not want to publish the screen_number to the
public - but the internal data we receive does contain that information.
thus, for all mulitplex theatres that do not want to publish screen number
information, the data must be ordered by theater_id, screen_date,
screen_time, movie_name.
SELECT * FROM schedule
ORDER BY theater_id,
screen_date,
screen_time,
CASE WHEN no_screennumber THEN NULL ELSE screen_number END,
movie_name;
the (simplyified) query does that just fine... here the loss of indexing
doesn't matter that much as the screen numbers are only in a small range and
thus a seqscan probably as fast as an indexscan.
but there are other more complex queries needing the similar logic with
multiple fields involved, that probably might suffer some performance loss.
i haven't touched the more complex ones yet, but still was wondering if
there might be some performance problems - the realtime queries take quite
long already due to some other non-optimized tables so i wouldn't want to
make this even worse before i had a chance to optimize them ;-)
cheers,
thomas
On Jan 15, 2006, at 11:15 , <me@alternize.com> <me@alternize.com> wrote:
> the movie schedules for theatres normally contains the information
> theater_id, screen_date, screen_time, screen_number, movie_name and
> should be outputed accordingly. it now happens to have some cinema
> multiplex owners who for some reasons do not want to publish the
> screen_number to the public - but the internal data we receive does
> contain that information.
Something you may consider doing is creating a view that masks the
screen_number in those cases, something like
create view schedule_public_view as
select theater_id
, screen_date
, screen_time
, case when no_screen_number then 0 else screen_number end as
screen_number
, movie_name
from schedules;
Michael Glaesemann
grzm myrealbox com
> Something you may consider doing is creating a view that masks the > screen_number in those cases, something like > > create view schedule_public_view as > select theater_id > , screen_date > , screen_time > , case when no_screen_number then 0 else screen_number end as > screen_number > , movie_name > from schedules; wouldn't this be the same problem: pgsql not using the index for screen_number anymore? - thomas
On Jan 15, 2006, at 19:53 , <me@alternize.com> <me@alternize.com> wrote: >> Something you may consider doing is creating a view that masks the >> screen_number in those cases, something like >> >> create view schedule_public_view as >> select theater_id >> , screen_date >> , screen_time >> , case when no_screen_number then 0 else screen_number end as >> screen_number >> , movie_name >> from schedules; > > wouldn't this be the same problem: pgsql not using the index for > screen_number anymore? That may be. Only way to be sure is to try it. (And I'm not knowledgeable enough to know for sure.) Michael Glaesemann grzm myrealbox com
On Sun, 15 Jan 2006 me@alternize.com wrote: > > Something you may consider doing is creating a view that masks the > > screen_number in those cases, something like > > > > create view schedule_public_view as > > select theater_id > > , screen_date > > , screen_time > > , case when no_screen_number then 0 else screen_number end as > > screen_number > > , movie_name > > from schedules; > > wouldn't this be the same problem: pgsql not using the index for > screen_number anymore? Theoretically an expressional index with the case expression should be usable for sorting with more complicated expressions like that. That really only an option if theres a small number of fixed orders though (and in testing it looked like you might need to play with settings to get it chosen).