Last row in SQL

Question

I have a query like this

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

here i am trying to get last data which is in table 1 against organizationID .. means last data of 79,87 and 19 and also try to combine with union all but this shows an error

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 

If i wrote this

  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

then this shows 1st row of 79 and 87 and last row on 19 but i want last rows against 79,87 and 19

any help?

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

Without CTE(as requested)

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