[aliday03]

مثال عملي على السيكوال سيرفر

كود :

CREATE DATABASE Test
GO
USE Test
go
CREATE TABLE Subscribers (
  SubscriberID INT PRIMARY KEY,
  EmployeeName NVARCHAR(255),
  SubscriptionYear INT
);
go
INSERT INTO Subscribers (SubscriberID, EmployeeName, SubscriptionYear) VALUES
(1,N'أحمد', 2020),(10,N'أحمد', 2021),(11,N'أحمد', 2022),(12,N'أحمد', 2023),(13,N'أحمد', 2024),(14,N'أحمد', 2025),
(2,N'خالد', 2019),(21,N'خالد', 2020),(22,N'خالد', 2021),(26,N'خالد', 2022),(23,N'خالد', 2023),(24,N'خالد', 2024),
(3,N'فاطمة', 2022),(31,N'فاطمة', 2023),(32,N'فاطمة', 2024),(33,N'فاطمة', 2025),(34,N'علي', 2018),
(4,N'علي', 2019),(41,N'علي', 2023),(42,N'علي', 2024),
(5,N'سارة', 2021),(51,N'سارة', 2022),
(6,N'محمد', 2022),(61,N'محمد', 2023),(62,N'محمد', 2024),(63,N'محمد', 2025);

GO
WITH LatestYear AS (
  SELECT MAX(SubscriptionYear) AS MaxYear
  FROM Subscribers
),
RankedSubscriptions AS (
  SELECT
      SubscriberID,
      EmployeeName,
      SubscriptionYear,
      RANK() OVER (PARTITION BY SubscriberID ORDER BY SubscriptionYear DESC) AS SubscriptionRank
  FROM
      Subscribers
  WHERE SubscriptionYear = (SELECT MaxYear FROM LatestYear) -- استخدام السنة الأحدث من الاستعلام الفرعي
)
SELECT
  SubscriberID,
  EmployeeName,
  SubscriptionYear
FROM
  RankedSubscriptions
WHERE
  SubscriptionRank = 1;