在多个列中按ID desc排序不同的张贴

用户名
SELECT impressions.* 
FROM impressions 
WHERE impressions.user_id = 2 
  AND impressions.action_name = 'show' 
  AND (impressions.message IS NOT NULL) 
GROUP BY impressionable_id, impressionable_type

我想从表格中选择印象ID降序后在Impressionable_id和impresssionable_type排序中唯一的所有最后印象,并获取最近的10

进一步解释

id, impressionabale_type, impressionable_id, action_name

50012, assignment, 2, show
50011, assignment, 1, show
50010, person, 1, show
50009, assignment, 1, show
50008, person, 5, show
50007, person, 4, show
50006, person, 3, show
50005, person, 1, show
50004, person, 1, show
50003, person, 2, show
50002, person, 2, show
50001, person, 1, show
50000, person, 1, show

理想情况下,我想要这个

50012, assignment, 2, show
50011, assignment, 1, show
50010, person, 1, show
50008, person, 5, show
50007, person, 4, show
50006, person, 3, show
50003, person, 2, show

我曾尝试过与众不同,但我的SQL知识充其量是公平的。

我懂了

PG::GroupingError: ERROR:  column "impressions.id" must appear in the GROUP BY clause or be used in an aggregate function

有人可以照亮一下吗

重做

也许这将满足您的需求:

SELECT t2.*
FROM (
    SELECT DISTINCT impressionable_id, impressionabale_type
    FROM impressions
    WHERE impressions.action_name = 'show'
) t1, LATERAL (
    SELECT *
    FROM impressions
    WHERE (t1.impressionable_id,t1.impressionabale_type) = (impressionable_id,impressionabale_type)
    ORDER BY id DESC
    LIMIT 1
) t2
ORDER BY id DESC
LIMIT 10

这将找到和的所有唯一组合,impressionable_id并且impressionable_type对于每个组合,都将idLATERAL子查询中找到具有最大组合的行

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章