1

I have created a table 

create table routes (
   type    character varying
 , quality character varying
 , route   integer
 , fare    integer
);

insert into routes (type, quality, route, fare)
values
  ('X', 'GOOD',    1, 5)
 ,('Y', 'GOOD',    1, 7)
 ,('X', 'AVERAGE', 2, 10)
 ,('Y', 'GOOD',    2, 7)
 ,('X', 'BAD',     3, 8)
 ,('Y', 'BAD',     3, 15);

For each route number there are 2 records, I need to select one row(route,fare) for each route based on type and quality.
For example if the type is 'X' and quality is 'GOOD' or 'AVERAGE', I will select the route and fare of that row and move over to next 'route' . Otherwise, I will select the 'route' and 'fare' from row with type 'Y'. That means from the above table, I should get:

1,5
2,10
3,15

I couldn't figure out to looping through the records of each route and derive the output from the two records of same route.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
sagar chandra
  • 21
  • 1

2 Answers2

1
SELECT DISTINCT ON (route)
       route, fare
FROM   routes
ORDER  BY route, (type = 'X' AND quality <> 'BAD') DESC, (type = 'X')

Fast and produces result ordered by route.
Assuming all columns NOT NULL.

Explanation

You can order by boolean expressions in Postgres. Details in this related answer:
Time based priority in Active Record Query

We could just use type DESC as last ORDER BY element, but that would rely on alphabetic sort order while X and Y look like placeholders for something else.

More explanation for DISTINCT ON:
Select first row in each GROUP BY group?

->SQLfiddle demo.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Try this query (using JOIN and CASE expresion):

SELECT  x.route,
        CASE WHEN x.quality IN ('GOOD', 'AVERAGE' )
             THEN x.fare  ELSE y.fare
        END fare
FROM routes x
JOIN routes y
ON x.type = 'X' AND y.type = 'Y' AND x.route = y.route

Demo --> http://www.sqlfiddle.com/#!2/e58441/2

krokodilko
  • 35,300
  • 7
  • 55
  • 79