How to select (almost) unique values ​​in a specific order

Question

There are several stops on the trip (stop = address where one or more orders are loaded or delivered), in a certain order. For example:

Trip A Trip_order Action Place Ordernumber 10 Load Paris 394798 20 Load Milan 657748 30 UnLoad Athens 657748 40 Unload Thessaloniki 394798 50 Load Thessaloniki 10142 60 Load Thessaloniki 6577 70 Unload Athens 6577 80 Unload Athens 10412 90 Load Thessaloniki 975147 100 Unload Paris 975147 

I want to see specific stops in the travel order:

 Load Paris Load Milan Unload Athens Unload Thessaloniki Load Thessaloniki Unload Athens Load Thessaloniki Unload Paris 

I looked through It , but if I do, I only get to unload Athens, unload Thessaloniki and download Thessaloniki once.

How to solve this?

EDIT: 11:11 (UTC +01: 00) To be more specific: these are tables that represent this information:

 Trips Trip_ID 100001 100002 100003 .... Actions Trip_ID Action MatNr RoOr RoVlg OrderID 100001 1 10 10 1 394798 100001 1 10 20 1 657748 100001 1 10 30 1 657748 100001 1 10 40 1 394798 100001 1 10 50 1 10142 100001 1 10 60 1 6577 100001 1 10 70 1 6577 100001 1 10 80 1 10412 100001 1 10 90 1 975147 100001 1 10 100 1 975147 

(Action: 1 = loading, 4 = unloading) The combination of MatNr, RoOr and RoVlg is the order of travel.

 Orders OrderID LoadingPlace UnloadingPlace 6577 Thessaloniki Athens 10142 Thessaloniki Athens 394798 Paris Thessaloniki 657748 Milan Athens 975147 Thessaloniki Paris 

Answer 1

Try it. No variables, nothing special:

 select a1.action, a1.place from trip_a a1 left join trip_a a2 on a2.trip_order = (select min(trip_order) from trip_a a3 where trip_order > a1.trip_order) where a1.action != a2.action or a1.place != a2.place or a2.place is null 

Demo here: http://sqlfiddle.com/#!9/4b6dc/13

Hope it works on any sql you use, it should, as long as subqueries are supported.

Tt simply finds the next highest trip_id and joins it or joins null if there is no higher trip_order . Then it selects only rows where either place , action , or both are different, or if there is no space in the joined table ( a2.place is null ).

edited after complete change of criteria

If you want to get the same results completely created from your base tables, you can do this:

  select case when a.action = 1 then 'load' when a.action = 0 then 'unload' end as action, case when a.action = 1 then o.loadingplace when a.action = 0 then o.unloadingplace end as place from trips t inner join actions a on t.trip_id = a.trip_id inner join orders o on a.orderid = o.orderid left join actions a2 on a2.roor = (select min(roor) from actions a3 where a3.roor > a.roor) left join orders o2 on a2.orderid = o2.orderid where a.action != a2.action or a2.action is null or case when a.action = 1 then o.loadingplace != o2.loadingplace when a.action = 0 then o.unloadingplace != o2.unloadingplace end order by a.roor asc 

And here is the updated fiddle: http://sqlfiddle.com/#!9/fdf9c/14

Answer 2

You do not need, and you do not want to use distinct for this, because we can see in your example that several destinations are executed several times. What you want: filter out the entries corresponding to the previous entry in terms of action and location.

It might look something like this:

 SELECT * FROM Trips t1 LEFT JOIN Trips t2 ON t1.Trip_Order = t2.Trip_Order - 10 WHERE t1.Action <> t2.Action OR t1.Place <> t2.Place) 

Answer 3

In the SQL server, you can get the difference ROW_NUMBER() based on trip_order and action,place and try something like this.

You can use it as a link to create a similar query in USQL.

Data examples

 DECLARE @Trip TABLE (Trip_order INT, Action VARCHAR(10), Place VARCHAR(50),Ordernumber INT) INSERT INTO @Trip VALUES (10 ,'Load', 'Paris', 394798), (20 ,'Load', 'Milan', 657748), (30 ,'UnLoad', 'Athens', 657748), (40 ,'UnLoad', 'Thessaloniki', 394798), (50 ,'Load', 'Thessaloniki', 10142), (60 ,'Load', 'Thessaloniki', 6577), (70 ,'UnLoad', 'Athens', 6577), (80 ,'UnLoad', 'Athens', 10412), (90 ,'Load', 'Thessaloniki', 975147), (100 ,'UnLoad', 'Paris', 975147); 

Query

 SELECT action,place FROM ( SELECT *,ROW_NUMBER()OVER(ORDER BY trip_order) - ROW_NUMBER()OVER(ORDER BY action,place) n FROM @trip )t GROUP BY n,action,place ORDER BY MIN(trip_order) 

Answer 4

Try the following:

Will work in MySQL :

 SELECT IF(@ temp=@temp :=A.TripName, @rank, @rank: =@rank +1) AS rank, A.TripName FROM (SELECT CONCAT(A.Action, A.Place) AS TripName FROM TripA A ) A, (SELECT @temp:=0, @rank:=0) AS B GROUP BY rank 

Answer 5

 SELECT s.* FROM stops s LEFT JOIN stops prev ON ( prev.Trip_order < s.Trip_order AND NOT EXISTS ( SELECT 'a' FROM stops prev2 WHERE prev2.Trip_order < s.Trip_order AND prev2.Trip_order > prev.Trip_order ) ) WHERE s.Action <> COALESCE(prev.Action, '') OR s.Place <> COALESCE(prev.Place, '') ORDER BY s.Trip_order 

Answer 6

 select a1.action,a1.place from tripa a1,tripa a2 where a2.trip_order = (select min(trip_order) from tripa a3 where trip_order > a1.trip_order) and (a1.action != a2.action or a1.place != a2.place) or a2.place is null 

This will give you the desired result.