Thread: ORDER BY 'DK', 'DE', DESC?
Hi! I would like to know if it's possible to give a priority order of how to sort the returning rows? Like for example to order every row with a field language = DK first, then the rows with field language = *DE' and last the other languages, ordered alphabetically…? Sincerely Victor
On Tue, 11 May 2004, [ISO-8859-1] Victor Spång Arthursson wrote: > Hi! > > I would like to know if it's possible to give a priority order of how > to sort the returning rows? > > Like for example to order every row with a field language = DK first, > then the rows with field language = *DE' and last the other languages, > ordered alphabetically…? Well, I think you can do something like: ORDER BY (language = 'DK'), (language = 'DE'), language (or you could possibly condense the first two into one with case)
On May 20, 2004, at 11:20 AM, Stephan Szabo wrote: > On Tue, 11 May 2004, [ISO-8859-1] Victor Spång Arthursson wrote: > >> Hi! >> >> I would like to know if it's possible to give a priority order of how >> to sort the returning rows? >> >> Like for example to order every row with a field language = DK first, >> then the rows with field language = *DE' and last the other languages, >> ordered alphabetically…? > > Well, I think you can do something like: > > ORDER BY (language = 'DK'), (language = 'DE'), language > > (or you could possibly condense the first two into one with case) Due to the sorting of boolean values, you'd need: ORDER BY language = 'DK' desc, language like '%DE' desc, language; > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org >
On Thu, 20 May 2004, Adam Ruth wrote: > > On May 20, 2004, at 11:20 AM, Stephan Szabo wrote: > > > On Tue, 11 May 2004, [ISO-8859-1] Victor Spång Arthursson wrote: > > > >> Hi! > >> > >> I would like to know if it's possible to give a priority order of how > >> to sort the returning rows? > >> > >> Like for example to order every row with a field language = DK first, > >> then the rows with field language = *DE' and last the other languages, > >> ordered alphabetically…? > > > > Well, I think you can do something like: > > > > ORDER BY (language = 'DK'), (language = 'DE'), language > > > > (or you could possibly condense the first two into one with case) > > Due to the sorting of boolean values, you'd need: > > ORDER BY language = 'DK' desc, language like '%DE' desc, language; Yep, someday I'll remember that 0 is less than 1. ;)
Adam Ruth <aruth@intercation.com> writes:
> Due to the sorting of boolean values, you'd need:
>
> ORDER BY language = 'DK' desc, language like '%DE' desc, language;
Personally I find something like this clearer:
ORDER BY (CASE WHEN language = 'DK' THEN 1
WHEN language like '%DE' THEN 2
WHEN ...
ELSE 5
END
)
--
greg
On May 20, 2004, at 12:19 PM, Stephan Szabo wrote: > > On Thu, 20 May 2004, Adam Ruth wrote: > >> >> On May 20, 2004, at 11:20 AM, Stephan Szabo wrote: >> >>> On Tue, 11 May 2004, [ISO-8859-1] Victor Spång Arthursson wrote: >>> >>>> Hi! >>>> >>>> I would like to know if it's possible to give a priority order of >>>> how >>>> to sort the returning rows? >>>> >>>> Like for example to order every row with a field language = DK >>>> first, >>>> then the rows with field language = *DE' and last the other >>>> languages, >>>> ordered alphabetically…? >>> >>> Well, I think you can do something like: >>> >>> ORDER BY (language = 'DK'), (language = 'DE'), language >>> >>> (or you could possibly condense the first two into one with case) >> >> Due to the sorting of boolean values, you'd need: >> >> ORDER BY language = 'DK' desc, language like '%DE' desc, language; > > Yep, someday I'll remember that 0 is less than 1. ;) > I only remember it after I try it once and wonder why my trues are at the bottom! Adam Ruth