Howto select (almost) unique values in a specific order

Question

In a trip there are several stops, (a stop = an adress whereone or multiple orders are loaded, or delivered), in a specific 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 the specific stops, in order of the trip:

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

I did look at This, but if I do that, I only get the unload Athens, unload Thessaloniki and Load Thessaloniki once.

How do I solve this?

EDIT: 11:11 (UTC +01:00) To be more specific: these are the tables which present 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=load, 4=unload) The combination of MatNr, RoOr and RoVlg is the order of the Trip.

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

I don't use MySQL or SQL server, it's a specific USQL language, but it's similar to those. — PSVSupporter, May 01 '15 at 08:03
No, it isn't. It can be any number, but it must be unique. So it could be 10, 12, 55, 87. But it can't be 10, 10, 10, 87. — PSVSupporter, May 01 '15 at 08:23
@PSVSupporter okay - makes a little trickier, but solved, i think — pala_, May 01 '15 at 08:25
@PSVSupporter in your updated data - for the actions table, should all actions there be 1? or should some of them be zero? — pala_, May 01 '15 at 10:21

pala_ · Accepted Answer · 2015-05-01T10:47:11.220

Try this one. No variables, nothing especially fancy:

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

Hopefully it works on whatever sql you're using, it should, so long as subqueries are supported.

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

edited after criteria changed completely

If you want to get the same results, built entirely 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's an updated fiddle: http://sqlfiddle.com/#!9/fdf9c/14

This is probably what I'm looking for. Except the example I gave, is not in 1 table, but in three: trip - stops - order [code] trip: tripid 12345 stops: tripid matnr routenr vlg action orderid 12345 10 10 1 load a1 12345 10 20 1 unload b1 12345 10 30 1 load c1 12345 20 10 1 unload d1 12345 20 20 1 load e1 order orderid loadingplace unloadingplace a1 Paris Athens a1 Paris Athens[/code] — PSVSupporter, May 01 '15 at 08:53
ugh.. then that info should be in the question. you can always shove the query that gave you the table you did put in the question, into this query in place of 'trip_a', however — pala_, May 01 '15 at 09:02
@PSVSupporter If you put the source tables, with sample data, in the question, i can update the answer — pala_, May 01 '15 at 10:14
@PSVSupporter sorry didnt see - could you also put the query you used to arrive at the initial data you posted? — pala_, May 01 '15 at 10:18
@PSVSupporter nevermind about that - think i knocked it on the head. I think you may have typo'd some of the actions. — pala_, May 01 '15 at 10:48

score 2 · Answer 2 · answered May 01 '15 at 08:15

2

You don't need and you don't want to use distinct for that because we can see in your example that several destinations occur multiple times. What you want: filter out records that match the preceding record in terms of action and place.

This could 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)

answered May 01 '15 at 08:15

lex82

11,173
2
44
69

1

This requires of course that your Trip_Order increases in steps of 10 and your actions are grouped per destination (first unloads, then loads) – lex82 May 01 '15 at 08:17
This will work, if the trip_order is always per 10, but it isn't (of course ;-) ) It can be 10, 12, 25, 66... (because of inserting stops between other stops, or moving/deleting stops). – PSVSupporter May 01 '15 at 08:19
You can do it on an intermediate result using row_number() or something like that. – lex82 May 01 '15 at 08:23
except the sql-server tag probably shouldnt be there. – pala_ May 01 '15 at 08:27
i mean in the comments on the OP, it's stated this isn't really an sql-server environment – pala_ May 01 '15 at 08:38

score 2 · Answer 3 · answered May 01 '15 at 08:20

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

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

Sample Data

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)

score 1 · Answer 4 · answered May 01 '15 at 08:09

1

Try this:

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

answered May 01 '15 at 08:09

Saharsh Shah

28,687
8
48
83

Thanks, but USQL can't handle variables in such way. – PSVSupporter May 01 '15 at 08:12

score 1 · Answer 5 · answered May 01 '15 at 08:23

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

score -1 · Answer 6 · edited May 02 '15 at 02:08

-1

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 gives you the required result.

edited May 02 '15 at 02:08

pala_

8,901
1
15
32

answered May 01 '15 at 11:14

Manu

1
2

Howto select (almost) unique values in a specific order

6 Answers6