Classics Forum

Informix SQL Help

Posted By: Sharp, Kirk (USMS)
Date: Friday, 3 February 2006, at 10:33 a.m.

Given the data in the 'route' table listed below, I need to retrieve the
most frequent route taken between locations '7PC' and 'REE'.

The following routes are detailed in the table below

7PC to KMCI
KMCI to OKL
OKL to REE
(Refer to route_id's 1 -3)

7PC to KMCI
KMCI to OKL
OKL to KELP
KELP to REE
(Refer to route_id's 4 -15)

7PC to KMCI
KMCI to OKL
OKL to 6PM
6PM to 6J8
6J8 to REE
(Refer to route_id's 15 -25)

Expected Results - The most frequent route used:
7PC to KMCI
KMCI to OKL
OKL to KELP
KELP to REE

Analyzing the given data below, the above route occurred 3 times. The
1st route (refer to route_id's 1-3) occurred only 1 time, and the 3rd
route (refer to route id's 15-25) occurred only 2 times.

I am totally lost as to how to retrieve the above expected results using
SQL.

Any help would be greatly appreciated.

Thanks

Kirk

Note: If needed, the table below can be cut and pasted into an Excel
spreadsheet.

Route Table

route_id movement_no mvnt_on_trip_id start_loc_code
end_location_code
1 1110091 1 7PC KMCI
2 1110091 2 KMCI OKL
3 1110091 3 OKL REE
4 1128647 1 7PC KMCI
5 1128647 2 KMCI OKL
6 1128647 3 OKL KELP
7 1128647 4 KELP REE
8 1128679 1 7PC KMCI
9 1128679 2 KMCI OKL
10 1128679 3 OKL KELP
11 1128679 4 KELP REE
12 1130202 1 7PC KMCI
13 1130202 2 KMCI OKL
14 1130202 3 OKL KELP
15 1130202 4 KELP REE
16 1132208 1 7PC KMCI
17 1132208 2 KMCI OKL
18 1132208 3 OKL 6PM
19 1132208 4 6PM 6J8
20 1132208 5 6J8 REE
21 1150727 1 7PC KMCI
22 1150727 2 KMCI OKL
23 1150727 3 OKL 6PM
24 1150727 4 6PM 6J8
25 1150727 5 6J8 REE

--Boundary_(ID_hiuUwe6bIihJphq5Hxj3Nw)

Messages In This Thread

• Informix SQL Help
  Sharp, Kirk (USMS) -- Friday, 3 February 2006, at 10:33 a.m.

Classics Forum is maintained by Administrator with WebBBS 5.12.