ORDER BY

Kirish: ushbu darsda siz PostgreSQL ORDER BY bandini SELECT ifodasida ishlatib, qatorlarni o'sish yoki kamayish tartibida qanday saralashni o'rganasiz.

PostgreSQL ORDER BY bandiga kirish

SELECT ifodasi odatda natija to'plamini aniqlanmagan tartibda qaytaradi. SELECT ifodasi qaytargan qatorlarni tartiblash uchun ORDER BY bandidan foydalaniladi.

ORDER BY bandining sintaksisi:

SELECT
  column1,
  column2,
  ...
FROM
  table_name
ORDER BY
  sort_expression [ASC | DESC];

Bu sintaksisda:

• Birinchi, FROM bandida ma'lumot olishni istagan jadval nomini ko'rsating.
• Ikkinchi, SELECT bandida natija to'plamiga kiritilishi kerak bo'lgan ustunlarni aniqlang.
• Uchinchi, ORDER BY bandida tartiblash ifodasini ko'rsating.

sort_expression — tartiblash uchun ishlatiladigan ustun yoki ifoda bo'lishi mumkin.

Qatorlarni o'sish tartibida saralash uchun ASC, kamayish tartibida saralash uchun DESC ishlatiladi.

ASC yoki DESC parametri ixtiyoriy. Agar aniq ko'rsatilmasa, ORDER BY bandi standart holda ASC dan foydalanadi.

PostgreSQL SELECT ifodasidagi bandlarni quyidagi tartibda baholaydi:

1. FROM
2. SELECT
3. ORDER BY

Agar SELECT bandida ustun taxallusi (alias) bo'lsa, uni ORDER BY bandida ham ishlatishingiz mumkin. Sababi, PostgreSQL SELECT bandini ORDER BY bandidan oldin baholaydi. SELECT bandida mavjud bo'lgan ustun taxalluslari ORDER BY bandida ham foydalanish uchun ochiq bo'ladi.

Namuna jadval tayyorlash

ORDER BY bandi bilan mashq qilish uchun yangi inventories jadvalini yaratamiz:

SQL Skript — inventories jadvalini yaratish va qatorlar qo'shish:

CREATE TABLE inventories (
    name VARCHAR(255) NOT NULL,
    price DEC(11,2) NOT NULL,
    quantity INT NOT NULL,
    color VARCHAR(50),
    updated_date DATE NOT NULL    
);

INSERT INTO inventories (name, price, quantity, color, updated_date) 

VALUES
('iPhone 15 Black', 999.99, 10, 'Black', '2024-12-01'),
('Galaxy S23', 899.99, 12, NULL, '2024-12-02'),
('Pixel 8', 799.99, 7, NULL, '2024-12-02'),
('iPhone 15 Pro', 1099.99, 6, 'Silver', '2024-12-03'),
('Galaxy S23 Ultra', 1199.99, 4, 'Black', '2024-12-03'),
('Pixel 8 Pro', 999.99, 9, 'Red', '2024-12-03'),
('iPhone 15 Pro Max', 1299.99, 3, 'Gold', '2024-12-04');

Qatorlarni bitta ustun bo'yicha tartiblash

Quyidagi ifoda ORDER BY bandidan foydalanib, inventariyadagi mahsulotlarni narxi bo'yicha pastkidan yuqorisiga tartiblab ko'rsatadi:

SELECT
  name,
  price
FROM
  inventories
ORDER BY
  price;

Natija:

name              | price
------------------+---------
 Pixel 8          | 799.99
 Galaxy S23       | 899.99
 Pixel 8 Pro      | 999.99
 iPhone 15 Black  | 999.99
 iPhone 15 Pro    | 1099.99
 Galaxy S23 Ultra | 1199.99
 iPhone 15 Pro Max| 1299.99

Mahsulotlarni narxi bo'yicha kamayish tartibida saralash uchun DESC parametridan foydalaniladi:

SELECT
  name,
  price
FROM
  inventories
ORDER BY
  price DESC;

Natija:

name              | price
------------------+---------
 iPhone 15 Pro Max| 1299.99
 Galaxy S23 Ultra | 1199.99
 iPhone 15 Pro    | 1099.99
 iPhone 15 Black  | 999.99
 Pixel 8 Pro      | 999.99
 Galaxy S23       | 899.99
 Pixel 8          | 799.99

Qatorlarni bir nechta ustun bo'yicha tartiblash

Ba'zi mahsulotlarning narxi bir xil; mahsulotlarni avval narxi bo'yicha, keyin esa tartiblangan mahsulot ro'yxatini nomi bo'yicha saralashingiz mumkin. Quyidagi so'rov ORDER BY bandidan foydalanib, mahsulotlarni narxi va nomi bo'yicha tartiblaydi:

SELECT
  name,
  price
FROM
  inventories
ORDER BY
  price DESC,
  name DESC;

Natija:

name              | price
------------------+---------
 iPhone 15 Pro Max| 1299.99
 Galaxy S23 Ultra | 1199.99
 iPhone 15 Pro    | 1099.99
 Pixel 8 Pro      | 999.99
 iPhone 15 Black  | 999.99
 Galaxy S23       | 899.99
 Pixel 8          | 799.99

Qatorlarni ifodalar bo'yicha tartiblash

Quyidagi ifoda har bir mahsulotning inventariya summasini hisoblab chiqadi va mahsulotlarni inventariya summasi bo'yicha yuqoridan pastga tartiblab ko'rsatadi:

SELECT
  name,
  price * quantity
FROM
  inventories
ORDER BY
  price * quantity DESC;

Natija:

name              | ?column?
------------------+----------
 Galaxy S23       | 10799.88
 iPhone 15 Black  |  9999.90
 Pixel 8 Pro      |  8999.91
 iPhone 15 Pro    |  6599.94
 Pixel 8          |  5599.93
 Galaxy S23 Ultra |  4799.96
 iPhone 15 Pro Max|  3899.97

PostgreSQL SELECT ifodasini ORDER BY ifodasidan oldin baholaydi, shuning uchun SELECT va ORDER BY bandlarida ustun taxallusidan foydalanish mumkin:

SELECT
  name,
  price * quantity AS amount
FROM
  inventories
ORDER BY
  amount DESC;

Natija:

name              | amount
------------------+----------
 Galaxy S23       | 10799.88
 iPhone 15 Black  |  9999.90
 Pixel 8 Pro      |  8999.91
 iPhone 15 Pro    |  6599.94
 Pixel 8          |  5599.93
 Galaxy S23 Ultra |  4799.96
 iPhone 15 Pro Max|  3899.97

Bu misolda price * quantity ifodasiga amount ustun taxallusini belgiladik va tartiblash uchun ORDER BY bandida undan foydalandik.

Qatorlarni sana bo'yicha tartiblash

Quyidagi ifodalar ORDER BY bandidan foydalanib, inventories jadvalidagi qatorlarni yangilangan sanasi bo'yicha eng eski sanadan eng yangisiga qarab tartiblaydi:

SELECT
  name,
  updated_date
FROM
  inventories
ORDER BY
  updated_date;

Natija:

name              | updated_date
------------------+--------------
 iPhone 15 Black  | 2024-12-01
 Galaxy S23       | 2024-12-02
 Pixel 8          | 2024-12-02
 iPhone 15 Pro    | 2024-12-03
 Galaxy S23 Ultra | 2024-12-03
 Pixel 8 Pro      | 2024-12-03
 iPhone 15 Pro Max| 2024-12-04

Quyidagi kabi DESC parametrini ishlatib, inventariyadagi mahsulotlarni yangilangan sanasi bo'yicha eng yangisidan eng eskisiga qarab ham tartiblashingiz mumkin:

SELECT
  name,
  updated_date
FROM
  inventories
ORDER BY
  updated_date DESC;

Natija:

name              | updated_date
------------------+--------------
 iPhone 15 Pro Max| 2024-12-04
 iPhone 15 Pro    | 2024-12-03
 Galaxy S23 Ultra | 2024-12-03
 Pixel 8 Pro      | 2024-12-03
 Galaxy S23       | 2024-12-02
 Pixel 8          | 2024-12-02
 iPhone 15 Black  | 2024-12-01

PostgreSQL ORDER BY: Tartiblashda NULL larni boshqarish

PostgreSQL da NULL noma'lum yoki yo'qolgan ma'lumotni bildiruvchi belgidir. NULL noma'lum bo'lgani uchun uni boshqa qiymatlar bilan taqqoslab bo'lmaydi. Biroq, tartiblash bajarilayotganda PostgreSQL qaysi qiymatlarning boshqa qiymatlardan oldin yoki keyin kelishini bilishi kerak. NULL ga kelganda, PostgreSQL ORDER BY bandida ikkita parametr taqdim etadi:

ORDER BY sort_expression NULLS FIRST;
ORDER BY sort_expression NULLS LAST;

Bu sintaksisda:

• NULLS FIRST — NULL larni boshqa NULL bo'lmagan qiymatlardan oldin joylashtiradi.
• NULLS LAST — NULL larni boshqa NULL bo'lmagan qiymatlardan keyin joylashtiradi.

E'tibor bering, tartiblash ifodasi va NULLS FIRST yoki NULLS LAST orasida ASC yoki DESC parametridan foydalanishingiz mumkin.

Quyidagi ifoda ORDER BY bandidan foydalanib, inventariyadagi mahsulotlarni rang bo'yicha alifbo tartibida tartiblaydi va NULL larni birinchi o'ringa qo'yadi:

SELECT
  name,
  color
FROM
  inventories
ORDER BY
  color NULLS FIRST;

Natija:

name              | color
------------------+--------
 Galaxy S23       | NULL
 Pixel 8          | NULL
 iPhone 15 Black  | Black
 Galaxy S23 Ultra | Black
 iPhone 15 Pro Max| Gold
 Pixel 8 Pro      | Red
 iPhone 15 Pro    | Silver

NULL larni NULL bo'lmagan boshqa qiymatlardan keyin joylashtirish uchun NULLS LAST parametridan foydalanishingiz mumkin:

SELECT
  name,
  color
FROM
  inventories
ORDER BY
  color NULLS LAST;

Natija:

name              | color
------------------+--------
 Galaxy S23 Ultra | Black
 iPhone 15 Black  | Black
 iPhone 15 Pro Max| Gold
 Pixel 8 Pro      | Red
 iPhone 15 Pro    | Silver
 Pixel 8          | NULL
 Galaxy S23       | NULL

Xulosa

• Bir yoki bir nechta ustun bo'yicha qatorlarni tartiblash uchun SELECT ifodasida ORDER BY bandidan foydalaning.
• PostgreSQL ORDER BY bandini SELECT bandidan keyin baholaydi.
• ASC parametri qatorlarni pastdan yuqoriga, DESC esa yuqoridan pastga tartiblab ko'rsatadi.
• NULLS FIRST — NULL larni boshqalardan oldin, NULLS LAST esa NULL bo'lmagan qiymatlardan keyin joylashtiradi.