Use something like TOP with GROUP BY

Viewed 187

With table table1 like below

+--------+-------+-------+------------+-------+
| flight |  orig |  dest |  passenger |  bags |
+--------+-------+-------+------------+-------+
|   1111 |  sfo  |  chi  |  david     |     3 |
|   1112 |  sfo  |  dal  |  david     |     7 |
|   1112 |  sfo  |  dal  |  kim       |     10|
|   1113 |  lax  |  san  |  ameera    |     5 |
|   1114 |  lax  |  lfr  |  tim       |     6 |
|   1114 |  lax  |  lfr  |  jake      |     8 |
+--------+-------+-------+------------+-------+

I'm aggregating the table by orig like below

select 
  orig
  , count(*) flight_cnt
  , count(distinct passenger) as pass_cnt
  , percentile_cont(0.5) within group ( order by bags ASC) as bag_cnt_med
from table1
group by orig

I need to add the passenger with the longest name ( length(passenger) ) for each orig group - how do I go about it?

Output expected

+------+-------------+-----------+---------------+-------------------+
| orig |  flight_cnt |  pass_cnt |  bags_cnt_med | pass_max_len_name |
+------+-------------+-----------+---------------+-------------------+
| sfo  |           3 |         2 |             7 |  david            |
| lax  |           3 |         3 |             6 | ameera            |
+------+-------------+-----------+---------------+-------------------+
5 Answers
Related