On Wed, 31 Jul 2002, Tom Haddon wrote: > Hi Folks, > > Pretty basic one here. I'm trying to do a SELECT statement that includes a > CASE structure that will be used in the ORDER BY clause, and I want the CASE > structure to evaluate a bunch of criteria and in each case increment its > value by a certain amount: > > SELECT id, description CASE > field1 IS TRUE THEN CASE=CASE+1 > field2 IS TRUE THEN CASE=CASE+2 > END > FROM table > ORDER BY CASE, description; > > Am I misunderstanding what CASE can do here, and if so, how else do I do > what I'm trying to do? I know that that isn't right - any pointers > appreciated.
Are you trying to get a 0-3 based on field1 and field2 or some kind of aggregate? The first you can do that with something like case when field1 is true then 1 else 0 end + case when field2 is true then 2 else 0 end I think. (Extend for more columns) Or if you've got like 7-8 columns maybe just make a function that does the work for you for clarity. You'll also need a comma after description and you'll want to alias the case expression and use that in order by or use the position number. ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
