sql - SQL 中的最后一行

Question

我有这样的问题

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

我在这里尝试获取表 1 中针对 organizationID 的最后数据 .. 表示 79,87 和 19 的最后数据，并尝试与 union all 结合，但这显示错误

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

然后这显示第 1 行 79 和 87 以及最后一行 19 但我想要最后一行对 79,87 和 19

有什么帮助吗？

Answer 1

;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