英文:
Taking different values for each different id
问题
我想从一列中仅使用每个ID的最高值，但在另一列中有多个值时，也要使用最低值：
就像在这种情况下，我们有2行具有相同的ID 11024，我希望结果只显示每个ID的一行，但dtStart必须是最低的，而dtEnd必须是最高的：
（此图像是虚构的，只是为了显示应该是什么样子）
我的选择是：
SELECT top 100
id,
min(dtStart) dtStart,
max(dtEnd) dtEnd
FROM T_registable
WHERE sup_id = 3356
GROUP by
id, dtStart, dtEnd
我尝试使用“min”/“max”值，尝试使用union all，尝试使用分区。
英文:
I want to use only the highest value from a column when there's multiples for each id, but also the lowest from another column when theres multiples:
Like in this case we have 2 lines with the same id 11024, I want the result to only show a single line for each id, but the dtStart must be the lowest and the dtEnd must be the higher:
(this image was fabricated, just to show how it should be)
My select is:
SELECT top 100
id ,
min (dtStart ) dtStart,
max (dtEnd ) dtEnd
FROM T_registable
WHERE sup_id = 3356
GROUP by
id, dtStart, dtEnd
I tried to use "min"/"max" value, tried union all, tried to use partition by.
答案1
得分: 2
从你的GROUP BY子句中移除dtStart和dtEnd应该解决这个问题。TOP通常与ORDER BY一起使用。
SELECT
id,
MIN(dtStart) AS dtStart,
MAX(dtEnd) AS dtEnd
FROM T_registable
WHERE sup_id = 3356
GROUP BY id
英文:
Removing dtStart and dtEnd from your GROUP BY clause should solve the issue. TOP is usually used with an ORDER BY.
SELECT
id,
MIN(dtStart) AS dtStart,
MAX(dtEnd) AS dtEnd
FROM T_registable
WHERE sup_id = 3356
GROUP BY id
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论