راهنمای جامع مرتب‌سازی پیشرفته داده‌ها در SQL Server با دستور ORDER BY

مرتب‌سازی پیشرفته داده‌ها در SQL Server با دستور ORDER BY: راهنمای جامع

کلاس `ORDER BY` یکی از ابزارهای ضروری در SQL است که برای مرتب‌سازی نتایج یک کوئری (query) بر اساس یک یا چند ستون استفاده می‌شود. این دستور به شما امکان می‌دهد تا داده‌ها را به صورت صعودی (ASC) یا نزولی (DESC) منظم کنید و دید بهتری نسبت به اطلاعات به دست آورید. در ادامه به بررسی دقیق‌تر و ارائه مثال‌های کاربردی از `ORDER BY` در SQL Server می‌پردازیم. این مقاله به شما کمک می‌کند تا با تکنیک‌های مختلف مرتب‌سازی داده‌ها آشنا شوید و از آن‌ها در پروژه‌های خود بهره ببرید.

برای شروع کار با مثال‌های `ORDER BY`، ابتدا یک جدول نمونه به نام `Employees` و `Departments` ایجاد می‌کنیم و چند رکورد به آن اضافه می‌کنیم. این ساختار داده برای نمایش انواع سناریوهای مرتب‌سازی مفید خواهد بود.

-- Drop table if it already exists
DROP TABLE IF EXISTS Employees;
DROP TABLE IF EXISTS Departments;

-- Create Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
);

-- Insert data into Departments
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance'),
(4, 'Marketing'),
(5, 'Sales');

-- Create Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeFirstName VARCHAR(50),
    EmployeeLastName VARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10, 2),
    HireDate DATE,
    ManagerID INT NULL,
    Email VARCHAR(100) NULL,
    CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Insert sample data into Employees
INSERT INTO Employees (EmployeeID, EmployeeFirstName, EmployeeLastName, DepartmentID, Salary, HireDate, ManagerID, Email) VALUES
(1, 'Ali', 'Mohammadi', 1, 60000.00, '2020-01-15', NULL, 'ali.m@example.com'),
(2, 'Sara', 'Ahmadi', 2, 75000.00, '2019-03-20', 1, 'sara.a@example.com'),
(3, 'Reza', 'Karimi', 3, 80000.00, '2021-07-01', 1, 'reza.k@example.com'),
(4, 'Mina', 'Hassani', 1, 62000.00, '2022-02-10', 1, 'mina.h@example.com'),
(5, 'Hamed', 'Fallah', 2, 70000.00, '2020-11-05', 2, 'hamed.f@example.com'),
(6, 'Nazanin', 'Sadeghi', 4, 55000.00, '2023-01-01', 2, 'nazanin.s@example.com'),
(7, 'Javad', 'Moradi', 3, 85000.00, '2018-09-12', 3, 'javad.m@example.com'),
(8, 'Leila', 'Abbasi', 5, 68000.00, '2021-04-25', 3, 'leila.a@example.com'),
(9, 'Amir', 'Bagheri', 2, 72000.00, '2022-06-18', 5, 'amir.b@example.com'),
(10, 'Zahra', 'Pournia', 1, 58000.00, '2023-03-30', 4, 'zahra.p@example.com'),
(11, 'Sima', 'Ebadi', 5, 71000.00, '2020-08-01', 8, 'sima.e@example.com'),
(12, 'Mehdi', 'Afshar', NULL, 90000.00, '2017-05-10', NULL, NULL);

کاربرد پایه دستور ORDER BY در SQL Server

اساسی‌ترین کاربرد `ORDER BY` مرتب‌سازی نتایج بر اساس یک ستون است. شما می‌توانید جهت مرتب‌سازی را به صورت صعودی (`ASC`) یا نزولی (`DESC`) مشخص کنید. اگر هیچ جهتی مشخص نشود، به طور پیش‌فرض مرتب‌سازی صعودی انجام می‌شود.

-- مرتب‌سازی کارمندان بر اساس نام خانوادگی به صورت صعودی (پیش‌فرض)
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, Salary
FROM Employees
ORDER BY EmployeeLastName;

-- مرتب‌سازی کارمندان بر اساس حقوق به صورت نزولی
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, Salary
FROM Employees
ORDER BY Salary DESC;

مرتب‌سازی با استفاده از چندین ستون در ORDER BY

شما می‌توانید نتایج را بر اساس چندین ستون مرتب کنید. در این حالت، مرتب‌سازی ابتدا بر اساس ستون اول انجام می‌شود و سپس، برای رکوردهایی که مقادیر یکسانی در ستون اول دارند، بر اساس ستون دوم و همینطور الی آخر صورت می‌گیرد. این قابلیت برای مرتب‌سازی دقیق‌تر داده‌ها در SQL Server بسیار کاربردی است.

-- مرتب‌سازی بر اساس دپارتمان به صورت صعودی و سپس بر اساس حقوق به صورت نزولی
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, DepartmentID, Salary
FROM Employees
ORDER BY DepartmentID ASC, Salary DESC;

ترکیب ORDER BY با دستور TOP

دستور `TOP` برای محدود کردن تعداد رکوردهای بازگشتی از یک کوئری استفاده می‌شود. وقتی `TOP` را با `ORDER BY` ترکیب می‌کنید، می‌توانید `N` رکورد برتر یا پایین‌تر را بر اساس یک معیار مشخص بازیابی کنید. این یک روش موثر برای نمایش داده‌های پرطرفدار یا کم‌طرفدار است.

-- 3 کارمند با بالاترین حقوق
SELECT TOP 3 EmployeeID, EmployeeFirstName, EmployeeLastName, Salary
FROM Employees
ORDER BY Salary DESC;

-- 2 کارمند با پایین‌ترین حقوق در دپارتمان 2
SELECT TOP 2 EmployeeID, EmployeeFirstName, EmployeeLastName, DepartmentID, Salary
FROM Employees
WHERE DepartmentID = 2
ORDER BY Salary ASC;

استفاده از ORDER BY با OFFSET FETCH

عبارت `OFFSET FETCH` که در SQL Server 2012 معرفی شد، جایگزینی منعطف‌تر برای `TOP` است، به ویژه برای پیاده‌سازی صفحه‌بندی (pagination). `OFFSET` تعداد رکوردهایی را که باید نادیده گرفته شوند مشخص می‌کند و `FETCH NEXT` تعداد رکوردهای بعدی را که باید برگردانده شوند تعیین می‌کند. این ترکیب برای ساختاردهی به نمایش نتایج در برنامه‌های وب با حجم بالای داده‌ها ایده‌آل است.

-- رد کردن 2 رکورد اول و نمایش 3 رکورد بعدی بر اساس تاریخ استخدام
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, HireDate
FROM Employees
ORDER BY HireDate ASC
OFFSET 2 ROWS
FETCH NEXT 3 ROWS ONLY;

مرتب‌سازی مقادیر NULL در ORDER BY

مقادیر `NULL` در SQL Server هنگام مرتب‌سازی رفتار خاصی دارند. به طور پیش‌فرض، `NULL`ها در مرتب‌سازی صعودی (ASC) ابتدا و در مرتب‌سازی نزولی (DESC) در انتها قرار می‌گیرند. شما می‌توانید با استفاده از `NULLS FIRST` یا `NULLS LAST` این رفتار را صریحاً کنترل کنید. این قابلیت برای کنترل دقیق‌تر نمایش داده‌های ناقص یا نامشخص ضروری است.

-- مرتب‌سازی بر اساس ManagerID، NULLها در ابتدا (پیش‌فرض ASC)
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, ManagerID
FROM Employees
ORDER BY ManagerID ASC;

-- مرتب‌سازی بر اساس Email به صورت نزولی، NULLها در انتها (پیش‌فرض DESC)
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, Email
FROM Employees
ORDER BY Email DESC;

-- برای کنترل صریح NULLها در SQL Server، می‌توان از CASE statement استفاده کرد (NULLS FIRST/LAST مستقیماً پشتیبانی نمی‌شوند)
-- مرتب‌سازی بر اساس Email، NULLها در انتها به صورت صعودی
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, Email
FROM Employees
ORDER BY CASE WHEN Email IS NULL THEN 1 ELSE 0 END, Email ASC;

-- مرتب‌سازی بر اساس Email، NULLها در ابتدا به صورت نزولی
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, Email
FROM Employees
ORDER BY CASE WHEN Email IS NULL THEN 0 ELSE 1 END DESC, Email DESC;

استفاده از عبارت CASE در دستور ORDER BY

عبارت `CASE` در `ORDER BY` به شما این امکان را می‌دهد که منطق مرتب‌سازی پیچیده‌تری را تعریف کنید. می‌توانید شرایطی را مشخص کنید که بر اساس آن‌ها داده‌ها به ترتیب خاصی مرتب شوند. این روش برای مرتب‌سازی سفارشی و غیرخطی بسیار قدرتمند است و کنترل بیشتری بر نحوه نمایش نتایج در اختیار شما قرار می‌دهد.

-- مرتب‌سازی کارمندان: ابتدا HR، سپس IT، و بقیه به ترتیب الفبا بر اساس نام دپارتمان
SELECT e.EmployeeFirstName, e.EmployeeLastName, d.DepartmentName, e.Salary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
ORDER BY
    CASE d.DepartmentName
        WHEN 'HR' THEN 1
        WHEN 'IT' THEN 2
        ELSE 3
    END,
    d.DepartmentName,
    e.EmployeeFirstName;

ORDER BY و نام مستعار ستون (Column Aliases)

در SQL Server، شما می‌توانید از نام مستعار (alias) برای ستون‌ها در عبارت `SELECT` استفاده کنید و سپس آن نام مستعار را در عبارت `ORDER BY` به کار ببرید. این کار به خوانایی کوئری‌های شما کمک می‌کند و آنها را قابل فهم‌تر می‌سازد. همچنین، SQL Server امکان مرتب‌سازی بر اساس شماره ستون را نیز فراهم می‌کند.

در SQL Server، می‌توانید از عدد برای ارجاع به ستون در دستور `ORDER BY` استفاده کنید. این قابلیت به ویژه زمانی مفید است که نام ستون‌ها طولانی یا پیچیده باشند، یا زمانی که از یک نام مستعار (Alias) در عبارت `SELECT` استفاده کرده‌اید. مثال زیر این موضوع را نشان می‌دهد:

ORDER BY N

در این حالت، `N` نشان‌دهنده شماره ستون در لیست انتخاب (`SELECT list`) است. به عنوان مثال، اگر ستون “EmployeeFirstName” سومین ستون در `SELECT` باشد، می‌توانید از “ORDER BY 3” برای مرتب‌سازی بر اساس آن استفاده کنید.

-- مرتب‌سازی با استفاده از نام مستعار
SELECT EmployeeFirstName AS First, EmployeeLastName AS Last, Salary
FROM Employees
ORDER BY Last DESC, First ASC;

-- مرتب‌سازی با استفاده از شماره ستون (First = 1, Last = 2, Salary = 3)
SELECT EmployeeFirstName, EmployeeLastName, Salary
FROM Employees
ORDER BY 2 DESC, 1 ASC;

ORDER BY با VIEW

یک `VIEW` در SQL Server یک جدول مجازی است که نتایج یک کوئری ذخیره شده را نشان می‌دهد. وقتی یک `VIEW` ایجاد می‌کنید، معمولاً `ORDER BY` در تعریف `VIEW` مجاز نیست، مگر اینکه `TOP` یا `OFFSET FETCH` استفاده شود. اما می‌توانید روی `VIEW` ایجاد شده کوئری بزنید و نتایج آن را با `ORDER BY` مرتب کنید. این روش برای مدیریت و نمایش داده‌ها به شکلی سازمان‌یافته از طریق نماهای پایگاه داده مفید است.

-- ایجاد یک VIEW
CREATE OR ALTER VIEW vw_EmployeeSalaries
AS
SELECT
    e.EmployeeID,
    e.EmployeeFirstName,
    e.EmployeeLastName,
    d.DepartmentName,
    e.Salary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- کوئری از VIEW و مرتب‌سازی نتایج
SELECT EmployeeFirstName, EmployeeLastName, DepartmentName, Salary
FROM vw_EmployeeSalaries
ORDER BY DepartmentName ASC, Salary DESC;

ORDER BY با UNION و UNION ALL

هنگام استفاده از عملگرهای `UNION` یا `UNION ALL` برای ترکیب نتایج چند کوئری، دستور `ORDER BY` باید در انتهای آخرین عبارت `SELECT` قرار گیرد و بر روی تمامی نتایج ترکیب شده اعمال شود. این نکته برای اطمینان از اعمال مرتب‌سازی صحیح بر روی مجموعه داده نهایی بسیار مهم است. `UNION` رکوردهای تکراری را حذف می‌کند، در حالی که `UNION ALL` تمامی رکوردها را، حتی اگر تکراری باشند، شامل می‌شود.

-- ترکیب کارمندان با حقوق بالای 70000 از دپارتمان IT و HR، مرتب‌سازی نهایی بر اساس حقوق
SELECT EmployeeFirstName, EmployeeLastName, Salary, 'IT Department' AS Source
FROM Employees
WHERE DepartmentID = 2 AND Salary > 70000
UNION ALL
SELECT EmployeeFirstName, EmployeeLastName, Salary, 'HR Department' AS Source
FROM Employees
WHERE DepartmentID = 1 AND Salary > 60000
ORDER BY Salary DESC;