Thread: order of results
Hi, i have a table called "people" (name:varchar, lastname:varchar). i do a select on it: select * from people where name like '%n1%' or lastname like '%l1%'. i would like the results in this order: first the results that satisfy only the (name like '%n1%') condition, then the ones that satisfy only the (lastname like '%l1%') condition and last the results that satisfy both conditions. Is this possible in only one SQL? (note that the search conditions n1 and l1 differ from search to search. TNX, Gregor
On Thu, Mar 25, 2004 at 14:23:00 +0100, Gregor Rot <zara4tustra@yahoo.com> wrote: > Hi, > > i have a table called "people" (name:varchar, lastname:varchar). > > i do a select on it: > > select * from people where name like '%n1%' or lastname like '%l1%'. > > i would like the results in this order: > > first the results that satisfy only the (name like '%n1%') condition, > then the ones that satisfy only the (lastname like '%l1%') condition and > last the results that satisfy both conditions. > > Is this possible in only one SQL? > (note that the search conditions n1 and l1 differ from search to search. Yes. You can order by true/false results from conditions to get the results in the desired order.
Bruno Wolff III wrote: > On Thu, Mar 25, 2004 at 14:23:00 +0100, > Gregor Rot <zara4tustra@yahoo.com> wrote: > >>Hi, >> >>i have a table called "people" (name:varchar, lastname:varchar). >> >>i do a select on it: >> >>select * from people where name like '%n1%' or lastname like '%l1%'. >> >>i would like the results in this order: >> >>first the results that satisfy only the (name like '%n1%') condition, >>then the ones that satisfy only the (lastname like '%l1%') condition and >>last the results that satisfy both conditions. >> >>Is this possible in only one SQL? >>(note that the search conditions n1 and l1 differ from search to search. > > > Yes. You can order by true/false results from conditions to get the > results in the desired order. Thank you - sorry, but how do you do that? Tnx, Gregor
On Sat, Apr 03, 2004 at 17:08:34 +0200, Gregor Rot <zara4tustra@yahoo.com> wrote: > Bruno Wolff III wrote: > >On Thu, Mar 25, 2004 at 14:23:00 +0100, > > Gregor Rot <zara4tustra@yahoo.com> wrote: > > > >>Hi, > >> > >>i have a table called "people" (name:varchar, lastname:varchar). > >> > >>i do a select on it: > >> > >>select * from people where name like '%n1%' or lastname like '%l1%'. > >> > >>i would like the results in this order: > >> > >>first the results that satisfy only the (name like '%n1%') condition, > >>then the ones that satisfy only the (lastname like '%l1%') condition and > >>last the results that satisfy both conditions. > >> > >>Is this possible in only one SQL? > >>(note that the search conditions n1 and l1 differ from search to search. > > > > > >Yes. You can order by true/false results from conditions to get the > >results in the desired order. > > Thank you - sorry, but how do you do that? Here is a simple example that illustrates what you can do: bruno=> select col from test order by col like 'bb%', col;col -----aaaaaccccccdbbabbbbbc (7 rows)