Xiaoqing Xiaoqing - 1 month ago 5
SQL Question

To pair up records in oracle sql

This is my sql:
when executed, it said



  1. 00000 - "missing keyword" on the position of "CROSS APPLY".




I'm just trying to pair up some records (in one day =
20160720
) with same TICKET_ID in the table and return their T_TIME and T_LOCATION.

select a.T_TIME, b.T_TIME, a.T_LOCATION, b.T_LOCATION
FROM TABLE a
CROSS APPLY
(select * from TABLE b where a.TICKET_ID = b.TICKET_ID having count(TICKET_ID) > 1) b
where (a.T_DATE=20160720);


Is the problem caused by using
CROSS APPLY
?

Ok, here is the problem I originally want to solve :)

The table looks like this:

T_TIME |T_LOCATION | TICKET_ID|T_DATE
20160720091032| ---0103| 1A268F|20160720
20160720095842| ---0115| 63T37H|20160720
20160720133408| ---0124| 1A268F|20160720
20160721152400| ---0116| 598I3R|20160721
20160720125844| ---0147| 63T37H|20160720


I want to pair up the records with same TICKET_ID. 2 records share one same TICKET_ID. And I want the output like:

20160720091032|20160720133408|0103|0124|
20160720095842|20160720125844|0115|0147|


The table is very large like for T_DATE=20160720 there will be 200000 records in total.

Answer

One way of doing it would be:

select a.ticket_id, a.t_time, b.t_time, a.t_location, b.t_location
from the_table a
  join the_table b on a.ticket_id = b.ticket_id and a.t_time < b.t_time
where a.t_date = 20160720;

The join condition and a.t_time < b.t_time ensure that the "other" version of a pair isn't in the result e.g. you only get (0103, 0124) but not (0124, 0103).