Visualizzare x record

USE tempdb;

CREATE TABLE dbo.News(
NewsID int NOT NULL IDENTITY PRIMARY KEY,
Testo varchar(10) NOT NULL,
Lega varchar(10) NOT NULL,
Orario smalldatetime NOT NULL
);

INSERT dbo.News VALUES('Testo 1', 'Lega 1', '19000101 1:00');
INSERT dbo.News VALUES('Testo 2', 'Lega 1', '19000101 2:00');
INSERT dbo.News VALUES('Testo 3', 'Lega 1', '19000101 3:00');
INSERT dbo.News VALUES('Testo 4', 'Lega 2', '19000101 4:00');
INSERT dbo.News VALUES('Testo 5', 'Lega 2', '19000101 5:00');
INSERT dbo.News VALUES('Testo 6', 'Lega 3', '19000101 6:00');
GO

CREATE FUNCTION dbo.ufn_Last2News(
@Lega varchar(10)
)
RETURNS TABLE
RETURN (
    SELECT TOP 2 *
    FROM dbo.News
    WHERE Lega = @Lega
    ORDER BY Orario DESC
);
GO

WITH CTE_Leghe AS
(
    SELECT DISTINCT Lega
    FROM dbo.News
)
SELECT U.*
FROM CTE_Leghe
CROSS APPLY dbo.ufn_Last2News(Lega) AS U
ORDER BY NewsID;

/* Output:

NewsID      Testo      Lega       Orario
----------- ---------- ---------- -----------------------
2           Testo 2    Lega 1     1900-01-01 02:00:00
3           Testo 3    Lega 1     1900-01-01 03:00:00
4           Testo 4    Lega 2     1900-01-01 04:00:00
5           Testo 5    Lega 2     1900-01-01 05:00:00
6           Testo 6    Lega 3     1900-01-01 06:00:00

(5 row(s) affected)

*/

DROP FUNCTION dbo.ufn_Last2News;
DROP TABLE dbo.News;