使用dense_rank()对选择的结果集进行排序。

huangapple go评论85阅读模式
英文:

Sorting result set from a select using dense_rank()

问题

这个查询返回了我需要的结果,但我需要根据dateCreated而不是在dense_rank()中使用的job列进行排序。

SELECT *
FROM (
    SELECT 
        dense_rank() OVER(ORDER BY isnp.dateCreated) AS denseRank,
        isnp.interviewSchedule AS interviewSchedule, 
        isnp.job AS job
        ,isnp.dateCreated
    FROM IvSchedule isnp
        JOIN Post p 
            ON p.id = isnp.job
        JOIN IvSchedule i 
            ON i.id = isnp.interviewSchedule
    ORDER BY denseRank OFFSET 0 rows
) x
WHERE denseRank > 0 AND denseRank <= 8

返回的结果将按照日期排序:

2017-03-23 14:30:30.150	1124	1815	1
2017-05-17 17:39:02.430	1138	1817	2
2017-06-12 14:17:04.467	1126	1815	1
2017-09-15 10:04:05.350	1144	1846	4
2017-11-03 13:16:09.337	1149	1845	3
英文:

This query is returning the results that I require but I need to sort based on dateCreated not the job column used in the dense_rank()

SELECT *
FROM (
    SELECT 
        dense_rank() OVER(ORDER BY job) AS denseRank,
        isnp.interviewSchedule AS interviewSchedule, 
        isnp.job AS job
        ,isnp.dateCreated
    FROM IvSchedule isnp
        JOIN Post p 
            ON p.id = isnp.job
        JOIN IvSchedule i 
            ON i.id = isnp.interviewSchedule
    ORDER BY denseRank OFFSET 0 rows
) x
WHERE denseRank &gt; 0 AND denseRank &lt;= 8

The results returned are (which is correct):

2017-03-23 14:30:30.150	1124	1815	1
2017-06-12 14:17:04.467	1126	1815	1
2017-05-17 17:39:02.430	1138	1817	2
2017-11-03 13:16:09.337	1149	1845	3
2017-09-15 10:04:05.350	1144	1846	4

But, I need them ordered by date, like so:

2017-03-23 14:30:30.150	1124	1815	1
2017-05-17 17:39:02.430	1138	1817	2
2017-06-12 14:17:04.467	1126	1815	1
2017-09-15 10:04:05.350	1144	1846	4
2017-11-03 13:16:09.337	1149	1845	3

答案1

得分: 0

样本查询没有提供一个演示ORDER BY子句,因此不能保证行将按任何特定顺序呈现。窗口顺序子句仅确定窗口函数的计算顺序。如果在查询中调用窗口函数但未指定演示ORDER BY子句,那么不能保证行将按与窗口函数的排序相同的顺序呈现。如果需要这样的保证,需要添加演示ORDER BY子句。

SELECT *
FROM (
    SELECT 
        dense_rank() OVER(ORDER BY job) AS denseRank,
        isnp.interviewSchedule AS interviewSchedule, 
        isnp.job AS job
        ,isnp.dateCreated
    FROM IvSchedule isnp
        JOIN Post p 
            ON p.id = isnp.job
        JOIN IvSchedule i 
            ON i.id = isnp.interviewSchedule
    ORDER BY denseRank OFFSET 0 rows
) x
WHERE denseRank &gt; 1 AND denseRank &lt;= 8

ORDER BY x.dateCreated
英文:

The sample query doesn’t have a presentation ORDER BY clause, and therefore, there’s no
assurance that the rows will be presented in any particular order. The window order clause
only determines ordering for the window function’s computation. If you invoke a window
function in your query but don’t specify a presentation ORDER BY clause, there’s no guarantee
that the rows will be presented in the same order as the window function’s ordering.
If you need such a guarantee, you need to add a presentation ORDER BY clause.

SELECT *
FROM (
    SELECT 
        dense_rank() OVER(ORDER BY job) AS denseRank,
        isnp.interviewSchedule AS interviewSchedule, 
        isnp.job AS job
        ,isnp.dateCreated
    FROM IvSchedule isnp
        JOIN Post p 
            ON p.id = isnp.job
        JOIN IvSchedule i 
            ON i.id = isnp.interviewSchedule
    ORDER BY denseRank OFFSET 0 rows
) x
WHERE denseRank &gt; 1 AND denseRank &lt;= 8

ORDER BY x.dateCreated

huangapple
  • 本文由 发表于 2020年7月23日 02:55:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/63041323.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定