Cloudera / Impala / sql：在特定列中查找具有唯一值的所有行

发布于 2025-02-12 17:49:37 字数 743 浏览 0 评论 0原文

希望对你们中的某些人来说是一个简单的问题：我有一个表如下所示（daginiestrong for the Clofting the Clofting）：

callign |时间|速度|
a | 23421 | 431 |
A | 23422 | 426 |
A | 23423 | 459 |
b | 23424 | 521 |
b | 23425 | 601 |
b | 23426 | 401 |
C | 23427 | 454 |
C | 23428 | 499 |
C | 23429 | 621 |

我希望结果输出是呼号的每个唯一值的第一行：

A 23421 431
B 23424 521
C 23427 454

我尝试了以下情况，没有成功：

SELECT callsign, time, speed FROM adsb_table WHERE speed>400 ORDER BY callsign GROUP by callsign

我不知道我正在使用Impala的事实是否有所不同在查询中。没有产生输出 - 如果我删除“组”条款，所有有序记录列出了....所以我想我正在错误地使用该组。帮助。

原文

Hopefully a simple question for some of you: I have a table adsb_table as as follows (apologiesstrong text for the formatting of the table):

callsign | time | speed|
A | 23421 | 431 |
A | 23422 | 426 |
A | 23423 | 459 |
B | 23424 | 521 |
B | 23425 | 601 |
B | 23426 | 401 |
C | 23427 | 454 |
C | 23428 | 499 |
C | 23429 | 621 |

I want the resulting output to be the first row for each unique value of callsign:

A 23421 431
B 23424 521
C 23427 454

I have tried the following without success:

SELECT callsign, time, speed FROM adsb_table WHERE speed>400 ORDER BY callsign GROUP by callsign

I don't know if the fact that I am using Impala makes the difference in the query. No output is generated - if I remove the "GROUP BY" clause all ordered records are listed....so I am using the GROUP BY incorrectly I guess. Help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

往昔成烟 2025-02-19 17:49:38

如果您始终想要每个通话的第一行，则可以使用row_number（）

WITH cte AS (
  SELECT 
   callsign, 
   time, 
   speed, 
   ROW_NUMBER() OVER (PARTITION BY callsign) AS row_no
  FROM adsb_table 
  WHERE speed > 400 
)
SELECT * 
FROM cte
WHERE row_no = 1
ORDER BY callsign

If you always want the first row per callsign, you can use ROW_NUMBER()

WITH cte AS (
  SELECT 
   callsign, 
   time, 
   speed, 
   ROW_NUMBER() OVER (PARTITION BY callsign) AS row_no
  FROM adsb_table 
  WHERE speed > 400 
)
SELECT * 
FROM cte
WHERE row_no = 1
ORDER BY callsign

回复收藏 0 原文

~没有更多了~