Take the 2-minute tour ×

Im working in an SQL Query like this: (sorted by the station visits)

111        'KC'   New York
111        'KC'   Washington
111        'KC'   Boston
111        'KC'   Denver
222        'FC'   London
222        'FC'   Paris

I'd like to SELECT distinct trains, and actual row must include the first and the last station like:

111         'KC'     New York        Denver
222         'FC'     Denver          Paris

Anyone can give a hand? Thank you in anticipation!

share|improve this question
Database tables do not have any order upon which you can base such a query. You'd need an additional order column. –  bernie Mar 28 '12 at 16:33
Are you using SQLServer? –  PraVn Mar 28 '12 at 16:34
is FirstStation = Denver for train 222 intentional or a typo (was expecting London) –  Mikeb Mar 28 '12 at 16:37
add comment

1 Answer

up vote 3 down vote accepted

Assuming you find something to define an order on the stations so that you can identify the "last" and "first" one, the following should work:

WITH numbered_stations AS (
  SELECT train_id, 
         row_number() over (partition by train_id order by some_order_column) as rn,
         count(*) over (partition by train_id) as total_stations
  FROM the_unknown_table
SELECT f.train_id,  
      f.station as first_station, 
      l.station as last_station
FROM (SELECT train_id, 
  FROM numbered_stations
  WHERE rn = 1
) f
  JOIN (SELECT train_id, 
        FROM numbered_stations 
        WHERE rn = total_stations) l 
  ON f.train_id = l.train_id
ORDER BY train_id

This assumes that some_order_column can be used to identify the last and first station.

It also assumes that the type is always the same for all combinations of train_id and station.

The shown syntax is standard ANSI SQL and should work on most modern DBMS.

share|improve this answer
+1 for well-explained, well-formatted query that addresses the question that should've been asked –  bernie Mar 28 '12 at 16:45
Doesn't it only work with Oracle? He didn't mention specific DBMS. –  Alex Gitelman Mar 28 '12 at 16:46
@AlexGitelman: should work with PostgreSQL, Oracle, DB2, SQL Server, Teradata, Sybase, Firebird 3.0. The CTE (WITH ...) is not strictly necessary and if that is replaced with a derived table it should also work with H2 and Derby (although a bit uglier) –  a_horse_with_no_name Mar 28 '12 at 16:47
add comment

Your Answer


