0

I currently have a table that has a list of locations a schedule will use and I want to be able to get the location code and platform for both the current row and the next one, and then have the database return a distinct pair.

Sometimes the Platform is null.

Table:

HeaderId    LocationOrdinal TIPLOC  Platform
1   0   GRAINEW 1
1   1   GRAIN   A
1   2   CLFFD12
1   3   HOOJ    2
1   4   GRVSEND 5
1   5   SPHEADJ A
1   6   DARTFD
1   7   DARTFDJ 
1   8   CRFDSPB
1   9   CRFD    1
1   10  SIDCUP
1   11  LEEE
1   12  HTHRGRN
2   0   HULL    7
2   1   HESSLE  1
2   ...

I currently use the following SQL to get the pairs of Tiplocs (without platforms)

  select distinct TIPLOC as A, next_location as B
    from (select st.*, 
             lead(TIPLOC) over (partition by HeaderId order by LocationOrdinal) as next_location
      from sched_timings st
     ) st
    where next_location is not null;

Current Output

A   B
GRAINEW GRAIN
GRAIN   CLFFD12
CLFFD12 HOOJ
HOOJ    GRVSEND
GRVSEND SPHEADJ
SPHEADJ DARTFD
DARTFD  DARTFDJ
DARTFDJ CRFDSPB
CRFDSPB CRFD
CRFD    SIDCUP

Desired Output

A    APlat    B    BPlat
GRAINEW 1    GRAIN  A
GRAIN   A    CLFFD12
CLFFD12      HOOJ   2
...

UPDATE: I have done the code below which seems to work but not sure if there's a more efficient approach.

  select distinct A, AP, next_location as B, next_location_plat as BP
    from (select st.TIPLOC as A, st.Platform as AP,
             lead(TIPLOC) over (partition by HeaderId order by LocationOrdinal) as next_location,
             lead(Platform) over (partition by HeaderId order by LocationOrdinal) as next_location_plat
      from sched_timings st
     ) st
    where next_location is not null;
2
  • 2
    If this query gives you the result you are looking for then I don't think you can do much about performance by rewriting the query. You should instead have a look at using a index that helps. May 18, 2022 at 11:23
  • 1
    Efficiency is more easily measurable by the execution plan than it is by the query itself. But if the query runs fast enough for you as it then it's likely fine enough. If it's not fast enough, then you can add the execution plan if you need assistance analyzing it.
    – J.D.
    May 18, 2022 at 18:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.