sql - SQL的最后一行

我有这样的查询

select  top 1* from table1 where organizationID= 79 order by D_Index desc
select  top 1* from table1 where organizationID= 87 order by D_Index desc
select  top 1* from table1 where organizationID= 19 order by D_Index desc

在这里我试图获取针对organizationID的表1中的最后一个数据..表示79,87和19的最后一个数据，并且还尝试与所有工会合并，但这显示了错误

select  top 1* from table1 where organizationID= 79 order by D_Index desc union all
select  top 1* from table1 where organizationID= 87 order by D_Index desc union all
select  top 1* from table1 where organizationID= 19 order by D_Index desc

如果我写这个

  select  top 1* from table1 where organizationID= 79  union all
    select  top 1* from table1 where organizationID= 87  union all
    select  top 1* from table1 where organizationID= 19
order by D_Index desc

然后这显示第一行的79和87，最后一行在19，但是我希望最后一行对79,87和19

有什么帮助吗？

最佳答案

;WITH CTE as
(
  SELECT
    *,
    row_number() over (partition by organizationID order by D_Index desc) rn
  FROM
    table1
  WHERE organizationID in (19,79,87)
)
SELECT *
FROM CTE
WHERE rn = 1

没有CTE（根据要求）

SELECT *
FROM
  (
    SELECT
      *,
      row_number() over (partition by organizationID order by D_Index desc) rn
    FROM
      table1
    WHERE organizationID in (19,79,87)
  ) x
WHERE rn = 1