57 / 100

SELECT DISTINCT

Noyob qatorlarni tanlash

Kirish: Ushbu qo'llanmada siz PostgreSQL SELECT DISTINCT bandi yordamida natijalar to'plamidagi takrorlanuvchi (dublikat) qatorlarni olib tashlash va faqat unikal (noyob) qiymatlarni tanlab olishni o'rganasiz.

PostgreSQL SELECT DISTINCT bandiga kirish

SELECT DISTINCT bandi jadvaldan faqat noyob qiymatlarni ajratib olish uchun ishlatiladi.

Uning asosiy sintaksisi quyidagicha:

query.sql

SELECT DISTINCT column1
FROM table_name;

Ushbu sintaksisda SELECT DISTINCT bandi column1 dagi qiymatlarni tahlil qiladi va takrorlanganlarini olib tashlab, faqat bir martadan qolgan noyob qatorlarni qaytaradi.

PostgreSQL SELECT DISTINCT bandiga misol

Misol uchun ombordagi tovarlar zaxirasini saqlovchi inventories jadvalini yaratamiz:

query.sql

CREATE TABLE inventories(
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   product_name VARCHAR(50) NOT NULL,
   brand VARCHAR(50) NOT NULL,
   warehouse VARCHAR(50) NOT NULL,
   quantity INT NOT NULL
);

INSERT INTO inventories (product_name, brand, warehouse, quantity) 
VALUES
('iPhone 16', 'Apple', 'San Jose', 50),
('iPhone 16', 'Apple', 'San Francisco', 30),
('iPhone 16 Pro', 'Apple', 'San Jose', 40),
('iPhone 16 Pro', 'Apple', 'San Francisco', 20),
('iPhone 16 Pro Max', 'Apple', 'San Francisco', 25),
('Galaxy S22', 'Samsung', 'San Jose', 50),
('Galaxy S22', 'Samsung', 'San Francisco', 30),
('Galaxy S22 Ultra', 'Samsung', 'San Francisco', 20),
('Galaxy Z Fold 4', 'Samsung', 'San Jose', 55),
('Galaxy Z Fold 4', 'Samsung', 'San Francisco', 35);

Agar barcha tovar nomlarini oddiy SELECT bilan so'rasak:

query.sql

SELECT
  product_name
FROM
  inventories
ORDER BY
  product_name;

Natija:

matn

   product_name
-------------------
 Galaxy S22
 Galaxy S22
 Galaxy S22 Ultra
 Galaxy Z Fold 4
 Galaxy Z Fold 4
 iPhone 16
 iPhone 16
 iPhone 16 Pro
 iPhone 16 Pro
 iPhone 16 Pro Max

Ko'rib turganingizdek, bitta mahsulot bir nechta omborda bo'lgani uchun uning nomi bir necha bor takrorlanmoqda.

Natijadan dublikatlarni olib tashlash uchun SELECT DISTINCT ni qo'llaymiz:

query.sql

SELECT DISTINCT
  product_name
FROM
  inventories
ORDER BY
  product_name;

Natija:

matn

   product_name
-------------------
 Galaxy S22
 Galaxy S22 Ultra
 Galaxy Z Fold 4
 iPhone 16
 iPhone 16 Pro
 iPhone 16 Pro Max

Bir nechta ustunlarga SELECT DISTINCT qo'llash

SELECT DISTINCT bandiga bir nechta ustunlarni berish mumkin:

query.sql

SELECT DISTINCT column1, column2
FROM table_name;

Bunday holda, PostgreSQL ko'rsatilgan ustunlar qiymatlarining kombinatsiyasi (juftligi) bo'yicha noyoblikni baholaydi.

Masalan, mahsulot nomi va uning brendi bo'yicha noyob juftliklarni olish:

query.sql

SELECT DISTINCT product_name, brand
FROM inventories;

Natija:

matn

   product_name    |  brand
-------------------+---------
 iPhone 16         | Apple
 iPhone 16 Pro Max | Apple
 Galaxy S22 Ultra  | Samsung
 iPhone 16 Pro     | Apple
 Galaxy S22        | Samsung
 Galaxy Z Fold 4   | Samsung

SELECT DISTINCT va GROUP BY farqi

SELECT DISTINCT natijalar to'plamidan dublikatlarni tozalaydi. GROUP BY esa qatorlarni guruhlaydi va odatda agregat funksiyalar bilan birga qo'llaniladi.

Agregat funksiyasiz ishlatilgan GROUP BY amalda SELECT DISTINCT bilan bir xil natija beradi:

query.sql

SELECT
  product_name
FROM
  inventories
GROUP BY
  product_name
ORDER BY
  product_name;

Biroq, agar maqsadingiz shunchaki noyob qatorlarni tanlash bo'lsa, SELECT DISTINCT kodni o'qish va tushunish uchun ancha sodda va ma'noli hisoblanadi.

Xulosa

Natijalar to'plamidan takroriy qatorlarni olib tashlash va faqat noyob yozuvlarni olish uchun SELECT DISTINCT bandidan foydalaning.
Bir nechta ustun ko'rsatilganda, noyoblik barcha ko'rsatilgan ustunlarning kombinatsiyasi bo'yicha aniqlanadi.

Oldingi

Keyingi