0

Am working with PostgreSQL 8.0.2, I have table 

create table rate_date (id serial, rate_name text);

and it's data is 

 id  rate_name
 --------------
 1   startRate
 2   MidRate
 3   xlRate
 4   xxlRate

After select it will show data with default order or order by applied to any column of same table. My requirement is I have separate entity from where I will get data as (xlRate, MidRate,startRate,xxlRate) so I want to use this data to sort the select on table rate_data. I have tried for values join but it's not working and no other solution am able to think will work. If any one have idea please share detail.

Output should be 

xlRate
MidRate
startRate
xxlRate

my attempt/thinking.

select id, rate_name 
from rate_date r
join (
  VALUES (1, 'xlRate'),(2, 'MidRate')
) as x(a,b) on x.b = c.rate_name
Chetan Pagar
  • 1
  • 1
  • 3
  • 1
    Sample data and desired results would really help. In addition, you should upgrade the version of Postgres. – Gordon Linoff Feb 05 '19 at 12:33
  • Hi @GordonLinoff Thanks, version update is not possible, sample I added table with 2 columns id and rate_name. – Chetan Pagar Feb 05 '19 at 12:36
  • 3
    Postgres 8.0 is long dead and forgotten you shouldn't be using that any more –  Feb 05 '19 at 12:38
  • You'd have to add some `CREATE TABLE` statements and your attempt so that we know what you are talking about. Besides, upgrade. It is possible (and may save your database). – Laurenz Albe Feb 05 '19 at 13:10

1 Answers1

0

I am not sure if this is helpful but in Oracle you could achieve that this way:

select *
from
(
select id, rate_name,
case rate_name
    when 'xlRate' then 1
    when 'MidRate' then 2
    when 'startRate' then 3
    when 'xxlRate' then 4
    else 100
end my_order    
from rate_date r
)
order by my_order

May be you can do something like this in PostgreSQL?

hbourchi
  • 309
  • 2
  • 8