Complex SQL I don't want to convert in a stored procedure

Question

Hi all!

I would like to have your assistance in order to know if it's possible to achieve the desired query without needing a stored procedure. I will try to explain myself as good as I can.

I have a similar structure to this:

PK / FK / DateTime / Value
1  / 68 / 10:30    / 60.5
2  / 68 / 09:30    / 10.5
3  / 61 / 05:30    / 01.0
4  / 58 / 04:30    / 22.2
5  / 58 / 01:00    / 15.0

These rows are defining some kind of event wich is described by the foreign key (FK). What I want to achieve is to get the two most recent rows for a set of events (FK).

Some kind of

SELECT TOP 2 * 
FROM TABLE 
WHERE FK IN (68,58) 
ORDER BY DATETIME DESC

, but obtaining two rows of every FK in the IN clause.

In natural language, the desired operation is to iterate through FK in the IN clause and make a

SELECT TOP 2 * 
FROM TABLE 
ORDER BY DATETIME DESC.

I would like to know if it's possible to express that in one SQL.

Thanks a lot,

Answer 1

You can always use a union:

SELECT TOP 2 * FROM TABLE WHERE FK = 68 ORDER BY DATETIME DESC
UNION
SELECT TOP 2 * FROM TABLE WHERE FK = 58 ORDER BY DATETIME DESC

Answer 2

    SELECT d.*
    FROM(
         SELECT DISTINCT FK FROM YourTable
         ) AS y
    OUTER APPLY(
    SELECT TOP 2 * 
      FROM YourTable AS y1
        WHERE y1.FK = y.FK
        ORDER BY DATETIME DESC
    ) AS d

Answer 3

One option

SELECT
    B.*
FROM
    (SELECT DISTINCT FK FROM MyTable) A
    CROSS APPLY
    (
    SELECT TOP 2 * 
    FROM TABLE T
    WHERE T.FK = A.FK
    ORDER BY DATETIME DESC
    ) B

Or

;WITH CTE AS
(
   SELECT *
      ROW_NUMBER() OVER (PARTITION BY FK ORDER BY DATETIME DESC) AS RowNum
   FROM
      Table
)
SELECT * FROM CTE WHERE RowNum <= 2

Answer 4

SELECT D.*
FROM ( 
   SELECT t.*
      ,  ROW_NUMBER = ROW_NUMBER() OVER (
              PARTITION BY FK 
              ORDER BY DateTime DESC
         ) 
   FROM Table AS t
) AS D
WHERE D.ROW_NUMBER <= 2

Or user RowNumber