英文:
Filter table to unique IDs without subquery
问题
I can help you translate the code portion without the need for subqueries, CTEs, or views:
select sorhsch.*
from sorhsch
inner join (
select sorhsch_pidm as pidm,
max(sorhsch_surrogate_id) as good_row
from sorhsch
group by sorhsch_pidm) sorhsch_filter
on sorhsch_pidm = pidm
and sorhsch_surrogate_id = good_row;
Here's the translated code:
选择 sorhsch.* -- 选择任意表
从 sorhsch
内部连接 (
选择 sorhsch_pidm 作为 pidm,
max(sorhsch_surrogate_id) 作为 good_row
从 sorhsch
分组按 sorhsch_pidm) sorhsch_filter
在 sorhsch_pidm = pidm
和 sorhsch_surrogate_id = good_row;
Please note that this translation only includes the code portion, as you requested.
英文:
I've got a table that looks something like this:
User ID (sorhsch_pidm) |
High school (sorhsch_sbgi_code) |
Surrogate ID (sorhsch_surrogate_id) |
|---|---|---|
| 123 | blah HS | 1 |
| 123 | blah2 HS | 2 |
| 234 | blah HS | 3 |
| 345 | blah4 HS | 4 |
| 567 | homeschool | 5 |
Contextually, I'd expect this particular table's schema to enforce uniqueness on the sorhsch_pidm field. (according to my vendor, the surrogate_id field is just a row number.) The problem is that it doesn't. I'm not sure why, but what I know is that I need to reduce it down until no sorhsch_pidm has >1 row. I don't really care which row gets chosen, only that exactly one remains for later querying.
Of course, I can do something like:
select sorhsch.* --IE select arbitrary tables
from sorhsch
inner join (
select sorhsch_pidm as pidm,
max(sorhsch_surrogate_id) as good_row
from sorhsch
group by sorhsch_pidm) sorhsch_filter
on sorhsch_pidm = pidm
and sorhsch_surrogate_id = good_row;
but this solution has me defining a subquery, which I personally dislike. Is there a way of solving this problem without using subqueries, CTEs, or views?
答案1
得分: 2
你可以使用带有WITH TIES选项的行限制子句，如下所示：
select * from sorhsch
order by row_number() over (partition by sorhsch_pidm order by sorhsch_surrogate_id desc)
fetch next 1 row with ties
英文:
You can use the row limiting clause with the WITH TIES option as the following:
select * from sorhsch
order by row_number() over (partition by sorhsch_pidm order by sorhsch_surrogate_id desc)
fetch next 1 row with ties
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论