LIMIT

Kirish: ushbu darsda siz PostgreSQL LIMIT bandini qanday ishlatib, faqat bir qism qatorlarni qaytarishni o'rganasiz.

PostgreSQL LIMIT bandi bilan tanishuv

LIMIT bandi SELECT ifodasidan faqat bir qism qatorlarni olish imkonini beradi. Katta natija to'plamida ishlayotganda bu juda qulay bo'ladi.

LIMIT bandining sintaksisi:

SELECT
  column1,
  column2
FROM
  table_name
ORDER BY
  sort_expression
LIMIT
  row_count;

Bu sintaksisda:

• Birinchi, FROM bandida ma'lumot olishni istagan jadval nomini ko'rsating.
• Ikkinchi, SELECT bandida qaytariladigan ustunlar ro'yxatini bering.
• Uchinchi, ORDER BY bandi yordamida qatorlarni tartiblang.
• Nihoyat, LIMIT bandida qaytariladigan qatorlar sonini (row_count) ko'rsating.

PostgreSQL bandlarni quyidagi tartibda baholaydi:

1. FROM
2. SELECT
3. ORDER BY
4. LIMIT

LIMIT bandini kutilgan natija to'plamini olish uchun doimo ORDER BY bandi bilan birga ishlatish juda muhim. Agar LIMIT bandini ORDER BY bandisiz ishlatsangiz, SELECT ifodasi qatorlarni aniqlanmagan tartibda qaytaradi va LIMIT bandi tartiblangan qatorlardan emas, balki tartibllanmagan qatorlardan eng yuqorisini tanlaydi — bu esa kutilmagan natijalarga olib kelishi mumkin.

Namuna jadval tayyorlash

LIMIT bandi bilan mashq qilish uchun mahsulot nomlarini va narxlarini saqlaydigan products degan yangi jadval yaratamiz. products jadvalidagi namuna ma'lumotlar:

SQL skript — products jadvalini yaratish va ma'lumotlarni kiritish:

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2)
);

INSERT INTO
  products (name, price)
VALUES
  ('iPhone 16 Pro Max', 1649.00),
  ('iPhone 16', 829.00),
  ('Galaxy S24 Ultra', 1299.99),
  ('Galaxy S24 FE', 949.99),
  ('Pixel 9 Pro', 799.00),
  ('Pixel 8a', 399.00),
  ('OnePlus 12', 745.00),
  ('OnePlus Open', 1514.99),
  ('Galaxy Z Fold 6', 2019.99);

PostgreSQL LIMIT bandi namunasi

Quyidagi ifoda LIMIT bandidan foydalanib, products jadvalidagi eng qimmat beshta mahsulotni qaytaradi:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
LIMIT 5;

Natija:

name              | price
------------------+---------
 Galaxy Z Fold 6  | 2019.99
 iPhone 16 Pro Max| 1649.00
 OnePlus Open     | 1514.99
 Galaxy S24 Ultra | 1299.99
 Galaxy S24 FE    |  949.99

Qanday ishlaydi:

1. FROM — products jadvalidagi qatorlarni qaytaradi.
2. SELECT — name va price ustunlaridan ma'lumot oladi.
3. ORDER BY — FROM bandi qaytargan qatorlarni narxi bo'yicha yuqoridan pastga tartiblaydi.
4. LIMIT — tartiblangan natija to'plamidan dastlabki beshta qatorni qaytaradi.

PostgreSQL LIMIT OFFSET bandi

Bir qism qatorlarni qaytarishdan oldin ba'zi qatorlarni o'tkazib yuborish uchun OFFSET bandidan foydalanish mumkin:

SELECT
  column1,
  column2
FROM
  table_name
ORDER BY
  sort_expression
LIMIT
  row_count
OFFSET
  skip_count;

Agar skip_count nol yoki null bo'lsa, ifoda OFFSET bandisiz ishlaydi. OFFSET bandi ma'lum miqdordagi qatorlarni (skip_count) o'tkazib yuboradi, shundan so'ng LIMIT bandi bir qism qatorlarni qaytaradi.

Quyidagi misolda products jadvalidagi keyingi beshta qatorni qaytarishdan oldin ikkita qatorni o'tkazib yuborish uchun LIMIT OFFSET dan foydalaniladi:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
LIMIT 5 OFFSET 2;

Natija:

name             | price
-----------------+---------
 OnePlus Open    | 1514.99
 Galaxy S24 Ultra| 1299.99
 Galaxy S24 FE   |  949.99
 iPhone 16       |  829.00
 Pixel 9 Pro     |  799.00

Qanday ishlaydi:

1. FROM bandi products jadvalidagi qatorlarni qaytaradi.
2. SELECT bandi name va price ustunlaridan ma'lumot oladi.
3. ORDER BY bandi FROM bandi qaytargan qatorlarni narxi bo'yicha yuqoridan pastga tartiblaydi.
4. OFFSET bandi ikkita qatorni o'tkazib yuboradi, LIMIT bandi esa keyingi beshta qatorni qaytaradi.

Sahifalash uchun PostgreSQL LIMIT OFFSET ishlatish

Natija to'plamini kichikroq bo'laklarga ajratish texnikasi sahifalash (pagination) deb ataladi. Tasavvur qiling, mahsulotlar ro'yxatini sahifalarda ko'rsatmoqchi bo'lsangiz; har bir sahifada cheklangan miqdorda mahsulot bo'ladi.

Quyidagi ifoda birinchi uchta mahsulotni o'z ichiga olgan birinchi sahifani qaytaradi:

SELECT
  product_id,
  name,
  price
FROM
  products
ORDER BY
  price DESC
LIMIT 3;

Natija:

product_id |       name        |  price
-----------+-------------------+---------
         9 | Galaxy Z Fold 6   | 2019.99
         1 | iPhone 16 Pro Max | 1649.00
         8 | OnePlus Open      | 1514.99

Ikkinchi sahifani ko'rsatish uchun OFFSET bandidan foydalaning:

SELECT
  product_id,
  name,
  price
FROM
  products
ORDER BY
  price DESC
LIMIT 3 OFFSET 3;

Natija:

product_id |       name       |  price
-----------+------------------+---------
         3 | Galaxy S24 Ultra | 1299.99
         4 | Galaxy S24 FE    |  949.99
         2 | iPhone 16        |  829.00

Xulosa

• SELECT ifodasidan faqat bir qism qatorlarni olish uchun LIMIT bandidan foydalaning.
• Ba'zi qatorlarni o'tkazib yuborib, keyingilari orasidan bir qism qatorlarni olish uchun LIMIT OFFSET bandidan foydalaning.
• LIMIT bandini doima ORDER BY bandi bilan birga ishlating.