在postgres查询中获取第一行 [英] getting first row in postgres query
本文介绍了在postgres查询中获取第一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用内部联接从2个表中查询一些数据.
I am querying some data from 2 tables using inner join.
这是查询
test_db=> select api_booking.install_ts, api_user.id from api_booking inner join api_user on api_booking.user_id=api_user.id and api_booking.status='completed' limit 20 ;
install_ts | id
-------------------------------+----
2016-09-15 09:53:42.511367+00 | 9
2016-10-12 11:37:11.438715+00 | 9
2016-10-21 08:55:49.57813+00 | 9
2017-02-27 06:12:17.362996+00 | 9
2017-02-27 06:24:59.316051+00 | 9
2017-02-28 06:15:35.00841+00 | 9
2017-02-28 06:34:57.766365+00 | 9
2017-05-23 14:40:54.831525+00 | 14
2017-06-05 07:47:39.78306+00 | 17
2017-06-05 07:55:30.171103+00 | 17
2016-12-06 06:47:43.860581+00 | 19
2016-09-09 07:34:58.40589+00 | 20
2016-11-16 09:09:24.466439+00 | 24
2016-10-03 02:52:24.419793+00 | 24
2017-05-02 03:48:02.843209+00 | 24
2017-05-08 10:01:45.77093+00 | 24
2016-09-09 12:08:27.503695+00 | 27
2016-09-10 10:05:44.617737+00 | 27
2016-09-11 10:08:22.791411+00 | 27
2016-09-12 08:56:31.462979+00 | 27
(20 rows)
现在,我只想要每个user_id的第一行.
Now, I only want the first row from each user_id.
喜欢,
install_ts | id
-------------------------------+----
2016-09-15 09:53:42.511367+00 | 9
2017-05-23 14:40:54.831525+00 | 14
2017-06-05 07:47:39.78306+00 | 17
2016-12-06 06:47:43.860581+00 | 19
2016-09-09 07:34:58.40589+00 | 20
2016-11-16 09:09:24.466439+00 | 24
2016-09-09 12:08:27.503695+00 | 27
我应该对此使用什么查询?
What query should I use for this ?
推荐答案
这应该可以解决问题:
select distinct on (api_user.id) api_user.id,api_booking.install_ts
from api_booking inner
join api_user on api_booking.user_id=api_user.id
and api_booking.status='completed'
order by api_user.id,api_booking.install_ts
limit 20 ;
这篇关于在postgres查询中获取第一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文