英文:
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 > 0 AND denseRank <= 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 > 1 AND denseRank <= 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 > 1 AND denseRank <= 8
ORDER BY x.dateCreated
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论