By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,654 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,654 IT Pros & Developers. It's quick & easy.

'case column' in HAVING clause

P: 5
Hello,

I'm having problem migrating to postgreSQL from MySQL, some of my queries does not work in postgres.

Query(simplified):
Expand|Select|Wrap|Line Numbers
  1. SELECT t1.bus_stop, t1.time,
  2. (case when t1.time >2200 then t1.time + 10000 else t1.time END) AS sort_column
  3. FROM routes AS t1
  4. GROUP BY  t1.bus_stop, t1.time
  5. HAVING sort_column > 2200 ORDER BY sort_column  LIMIT 1
Error:
Expand|Select|Wrap|Line Numbers
  1. ERROR:  column "sort_column" does not exist
How to use a 'case column' in HAVING clause?
Apr 3 '08 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 700
Maybe like this

Expand|Select|Wrap|Line Numbers
  1. SELECT i,j, sort_kolumn from (
  2. SELECT t1.bus_stop as i, t1.time as j,
  3. (case when t1.time >2200 then t1.time + 10000 else t1.time END) AS sort_column FROM routes AS t1) foo
  4. GROUP BY  i,j
  5. HAVING sort_column > 2200 ORDER BY sort_column  LIMIT 1
Does it work?
Apr 3 '08 #2

P: 5
Maybe like this

Expand|Select|Wrap|Line Numbers
  1. SELECT i,j, sort_kolumn from (
  2. SELECT t1.bus_stop as i, t1.time as j,
  3. (case when t1.time >2200 then t1.time + 10000 else t1.time END) AS sort_column FROM routes AS t1) foo
  4. GROUP BY  i,j
  5. HAVING sort_column > 2200 ORDER BY sort_column  LIMIT 1
Does it work?
Yes, it works. Thank you rski. :)
But for me it looks like way around the problem, is there a better way to do this?
I'm migrating to postgres to speed up my aplication so I'm concerned about the speed of this query.
Apr 3 '08 #3

Expert 100+
P: 700
If i'm right HAVING clause can't have CASE clause.
Say what do you want to achieve and what is table structure, maybe there is another solution than yours.
Apr 3 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.