Avoid Unions to get TOP count

Question

Here are two tables:

LocationId   Address          City    State    Zip
1            2100, 1st St     Austin  TX       76819
2            2200, 2nd St     Austin  TX       76829
3            2300, 3rd St     Austin  TX       76839
4            2400, 4th St     Austin  TX       76849
5            2500, 5th St     Austin  TX       76859
6            2600, 6th St     Austin  TX       76869


TripId   PassengerId          FromLocationId    ToLocationId
1        746896               1                 2
2        746896               2                 1
3        234456               1                 3
4        234456               3                 1
5        234456               1                 4
6        234456               4                 1
7        234456               1                 6
8        234456               6                 1
9        746896               1                 2
10       746896               2                 1
11       746896               1                 2
12       746896               2                 1

I want TOP 5 locations which each passenger has traveled to (does not matter if its from or to location). I can get it using a UNION, but was wondering if there was a better way to do this.

My Solution:

select  top 5 *
from 
(select count(l.LocationId) as cnt, l.LocationId, l.Address1, l.Address2, l.City, St.State , l.Zip
from 
Trip t
join LOCATION l on t.FromLocationId = l.LocationId
where t.PassengerId = 746896
group by count(l.LocationId) as cnt, l.LocationId, l.Address1, l.Address2, l.City, St.State , l.Zip
UNION
select count(l.LocationId) as cnt, l.LocationId, l.Address1, l.Address2, l.City, St.State , l.Zip
from 
Trip t
join LOCATION l on t.ToLocationId = l.LocationId
where t.PassengerId = 746896
group by count(l.LocationId) as cnt, l.LocationId, l.Address1, l.Address2, l.City, St.State , l.Zip
) as tbl
order by cnt desc

Take a look at the [`ROW_NUMBER()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017) function. — Joe Farrell, May 03 '18 at 16:14
@kiranreloaded Does your sample query run? I am getting errors for it ... http://sqlfiddle.com/#!18/4fe69/4 - Incorrect syntax near the keyword 'as'. E.g. simplest example: http://sqlfiddle.com/#!18/4fe69/8 — Menelaos, May 03 '18 at 16:26
Do you want for all passengers at once or just a specific passenger? — Gordon Linoff, May 03 '18 at 16:29
Do you want the top 5 location for all passengers or top 5 location of each passenger? For example. `746896 - 1, 4, 3, 4` | `746890 - 2, 4, 3, 4` | etc.. — DxTx, May 03 '18 at 17:41
@MenelaosBakopoulos - I have created a fiddle - http://sqlfiddle.com/#!18/cec8b/105 — kiranreloaded, May 03 '18 at 17:47
@GordonLinoff - I want top 5 location of a particular passenger — kiranreloaded, May 03 '18 at 17:48
@kiranreloaded same results without UNION. Just missing some cosmetic detail columns: http://sqlfiddle.com/#!18/cec8b/144 — Menelaos, May 03 '18 at 18:20

score 1 · Answer 1 · answered May 03 '18 at 16:15

1

YOou'll need to replace the SELECT *'s with the columns you need, however, something like this should work:

WITH Visits AS (
    SELECT *,
           COUNT(*) OVER (PARTITION BY t.PassengerID, L.LocationID) AS Visits
    FROM Trip T
         JOIN [Location] L ON T.FromLocationId = L.LocationId),
Rankings AS (
    SELECT *,
           DENSE_RANK() OVER (PARTITION BY V.PassengerID ORDER BY Visits DESC) AS Ranking
    FROM Visits V)
SELECT *
FROM Rankings
WHERE Ranking <= 5;

answered May 03 '18 at 16:15

Thom A

88,727
11
45
75

This is only taking FromLocationId into account. I need it across both From and To Location Id. – kiranreloaded May 03 '18 at 17:22
@kiranreloaded Then add it to the `PARTITION` :) I couldn't test as I didn't have any consumable data to use. – Thom A May 03 '18 at 17:44
@DT - I'm not able to get it to work. Here is the fiddle - http://sqlfiddle.com/#!18/cec8b/118 – kiranreloaded May 03 '18 at 18:01

Gordon Linoff · Answer 2 · 2018-05-04T03:16:27.793

1

You can do this without union all:

select top (5) t.passengerid, v.locationid, count(*)
from trip t cross apply
     (values (fromlocationid), (tolocationid)) v(locationid) join
     location l
     on v.locationid = l.locationid
where t.PassengerId = 746896
group by  t.passengerid, v.locationid
order by count(*) desc;

If you want an answer for all passengers, it would be a similar idea, using row_number(), but your query suggests you want the answer only for one customer at a time.

You can include additional fields from location as well.

Here is a SQL Fiddle.

edited May 04 '18 at 03:16

answered May 03 '18 at 16:30

Gordon Linoff

1,242,037
58
646
786

I'm getting the following error: Msg 207, Level 16, State 1, Line 4 Invalid column name 'fromlocationid'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'tolocationid'. – kiranreloaded May 03 '18 at 17:24
1

@kiranreloaded You should be able to troubleshoot your own column names. :) – Thom A May 03 '18 at 17:44
@kiranreloaded . . . My bad. I misspelled `apply` the first time around. – Gordon Linoff May 04 '18 at 03:17

Menelaos · Answer 3 · 2018-05-03T18:42:50.410

Further simplified solution

  select  top 3 * from
( 

  Select  distinct count(locationId) as cnt, locationId from trip 
  unpivot
  (
   locationId 
   for direction in (fromLocationId, toLocationId)
  )u 
  where passengerId IN (746896, 234456)
  group by direction,  locationId
)as tbl2  

  order by cnt desc;

Solution combining columns

The main issue for me is avoiding union to combine the two columns. The UNPIVOT command can do this.

select  top 3 * from (
  select count(locationId) cnt, locationId
from
(

  Select valu as locationId, passengerId from trip
  unpivot
  (
   valu 
   for loc in (fromLocationId, toLocationId)
  )u
)united  
where passengerId IN (746896, 234456)
group by locationId
 ) as tbl
order by cnt desc;

http://sqlfiddle.com/#!18/cec8b/136

If you want to get the counts by direction:

select  top 3 * from (
  select count(locationId) cnt, locationId, direction
from
(

  Select valu as locationId, direction, passengerId from trip
  unpivot
  (
   valu 
   for direction in (fromLocationId, toLocationId)
  )u
)united  
where passengerId IN (746896, 234456)
group by locationId, direction
 ) as tbl
order by cnt desc;

http://sqlfiddle.com/#!18/cec8b/139

Same Results as you ( minus some minor descriptions )

select  top 3 * from
(

select  distinct * from (
  select count(locationId) cnt, locationId 
from
(

  Select valu as locationId, direction, passengerId from trip
  unpivot
  (
   valu 
   for direction in (fromLocationId, toLocationId)
  )u
)united  
where passengerId IN (746896, 234456)
group by locationId, direction
 ) as tbl
)as tbl2  
order by cnt desc;

DxTx · Accepted Answer · 2018-05-05T12:13:17.317

1

This will give you top 5 location.

SELECT TOP 5 tmp.fromlocationid        AS locationid, 
             Count(tmp.fromlocationid) AS Times 
FROM   (SELECT fromlocationid 
        FROM   trip 
        UNION ALL 
        SELECT tolocationid 
        FROM   trip) tmp 
GROUP  BY tmp.fromlocationid

Method 1: This will give you top 5 location of each passenger.

WITH cte AS
(   SELECT passengerid, 
        locationid, 
        Count(locationid) AS Times, 
        Row_number() OVER(partition BY passengerid ORDER BY passengerid ASC) AS RowNum 
    FROM   (SELECT tripid, passengerid, fromlocationid AS locationid 
            FROM   trip 
            UNION ALL 
            SELECT tripid, passengerid, tolocationid AS locationid 
            FROM   trip) tmp 
    GROUP  BY passengerid, locationid ) 


SELECT * 
FROM   cte 
WHERE  rownum <= 5 
ORDER  BY passengerid, Times DESC

Method 2: Same result without Union Operator (Top 5 location of each passenger)

WITH cte AS
(   SELECT passengerid, 
        locationid, 
        Count(locationid) AS Times, 
        Row_number() OVER(partition BY passengerid ORDER BY passengerid ASC) AS RowNum 
    FROM   trip 
        UNPIVOT ( locationid 
                FOR subject IN (fromlocationid, tolocationid) ) u 
    GROUP  BY passengerid, locationid ) 


SELECT * 
FROM   cte 
WHERE  rownum <= 5 
ORDER  BY passengerid, times DESC

If you also want to get the location details, you can simply join the location table.

SELECT cte.* , location.* 
FROM   cte 
INNER  JOIN location ON location.locationid = cte.locationid
WHERE  rownum <= 5 
ORDER  BY passengerid, times DESC

^Reference
^{- https://stackoverflow.com/a/19056083/6327676}

edited May 05 '18 at 12:13

answered May 03 '18 at 17:45

DxTx

3,049
3
23
34

1

I need it for a particular passenger – kiranreloaded May 03 '18 at 17:54
@kiranreloaded, Added the function (with & without the union operator). Try it & let me know if there are any errors. – DxTx May 03 '18 at 19:40
1

Your method 2 worked great. Marking it as Accepted. Thanks! – kiranreloaded May 03 '18 at 21:29
1

Great use of unpivot and great solution!! - especially going all the way to get the right result. It is essentially the only way to combine two columns into one (without using UNION) as I had pointed out. I'm glad that my UNPIVOT example helped come up with the complete answer :) . using the alias 'u' after unpivot gave that part away , but all the rest is you 100% :D – Menelaos May 04 '18 at 10:16
1

@MenelaosBakopoulos, Thank you & no, I did not copy your answer. :D If I copy one of the answers in the same question, Then I'll definitely give the credits. I love SQL but I'm not an expert in SQL. However, I'm really really good at googling stuff. Look at this solution. https://stackoverflow.com/a/19056083/6327676 And yes, I believe your answer is a great one and that's why I upvoted it. (as well as others). :) – DxTx May 04 '18 at 13:07
1

@D T Nice comment and link. Apologies, you are correct! I even see the same alias that we both used `u` :D . – Menelaos May 04 '18 at 13:31
@MenelaosBakopoulos, Well I should have included a reference link to that answer & If I did, this would never happen. I know how to include [reference link](https://stackoverflow.com/a/49615506/6327676) but still trying to figure out how to include bibliography links. – DxTx May 04 '18 at 13:44

Avoid Unions to get TOP count

4 Answers4

Further simplified solution

Solution combining columns

Same Results as you ( minus some minor descriptions )