Duplicate records with postgres

Question

Hello everyone Im working with postgres to get a result pair but I am getting duplicate records. Im getting something like:

stop_name|departure_time
------------------------
AAA      | 16489646
BBB      | 16465464
AAA      | 46546665
AAA      | 18421654
BBB      | 16849685
AAA      | 56496865

I expect something like:

stop_name|departure_time
------------------------
AAA      | 16489646
BBB      | 16465464

Because is a bit complex I made a fiddle HERE where you can see a sample of the schema and the query Im using. Can some one gimme a hand to achieve the expected results? Thanks.

This work is related to GTFS(transportation) and I want to get the possible departures given a specific time. After some discussion at work the answer given will work. — LouieV
– LouieV, Commented Jul 16, 2013 at 22:59

sgeddes · Accepted Answer · 2013-07-16 22:34:33Z

3

I think you just need to use the MIN aggregate:

SELECT s.stop_name, min(st.departure_time) departure_time
  FROM stops s
    INNER JOIN stop_times st
      ON s.stop_id = st.stop_id
    INNER JOIN (
      SELECT DISTINCT t.trip_id, t.route_id
        FROM trips t, northbound nb
        WHERE t.trip_id LIKE CONCAT(nb.train_id,'%')
        AND t.route_id = '2'
    ) TR
      ON TR.trip_id = st.trip_id
WHERE st.departure_time > 1373948273
GROUP BY s.stop_name

Updated Fiddle

answered Jul 16, 2013 at 22:34

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LouieV Over a year ago

Thank you! Sometimes you get wrapped up with complexity and forget the simple things. That was the case here :(

Collectives™ on Stack Overflow

Duplicate records with postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related