SQL ORDER BY va LIMIT — tartiblash

SELECT ma’lumotni oldi, WHERE uni filtrladi. Lekin natija baza saqlagan tartibda chiqadi. Ko’pincha uni tartiblash kerak bo’ladi: “eng arzondan qimmatga” yoki “alifbo bo’yicha”. Bu darsda ORDER BY bilan tartiblashni va LIMIT bilan natijani cheklashni ko’ramiz.

mahsulotlar jadvali:

id	nom	narx	soni	turi
1	Olma	8000	50	meva
2	Non	3000	120	non
3	Sut	12000	30	sut mahsuloti
4	Choy	15000	40	ichimlik
5	Banan	14000	25	meva
6	Qatiq	9000	0	sut mahsuloti
7	Suv	4000	200	ichimlik
8	Guruch	18000	60	don

ORDER BY — tartiblash

ORDER BY dan keyin qaysi ustun bo’yicha tartiblashni yozasiz. Standart holda o’sish tartibida (kichikdan kattaga):

SELECT nom, narx FROM mahsulotlar ORDER BY narx;

Natija — arzondan qimmatga:

nom	narx
Non	3000
Suv	4000
Olma	8000
Qatiq	9000
Sut	12000
Banan	14000
Choy	15000
Guruch	18000

DESC — kamayish tartibi

Kattadan kichikka kerak bo’lsa, ustun nomidan keyin DESC qo’shiladi:

SELECT nom, narx FROM mahsulotlar ORDER BY narx DESC;

O’zingiz ishga tushirib ko’ring — so’rovni tahrirlashingiz ham mumkin:

Natija boshida eng qimmati turadi: Guruch (18000), Choy (15000), Banan (14000) va hokazo.

Matnni tartiblash

Son kabi matn ham tartiblanadi — alifbo bo’yicha:

SELECT nom FROM mahsulotlar ORDER BY nom;

Natija alifbo tartibida: Banan, Choy, Guruch, Non, Olma, Qatiq, Sut, Suv. DESC qo’shsangiz — teskari, Suv dan Banan gacha.

Bir nechta ustun bo’yicha

Ustunlarni vergul bilan sanasangiz, avval birinchi ustun bo’yicha, teng bo’lganlar ichida ikkinchi bo’yicha tartiblanadi:

SELECT nom, turi, narx FROM mahsulotlar ORDER BY turi, narx;

Bu avval turini alifbo bo’yicha, har tur ichida narxni o’sish bo’yicha tartiblaydi:

nom	turi	narx
Guruch	don	18000
Suv	ichimlik	4000
Choy	ichimlik	15000
Olma	meva	8000
Banan	meva	14000
Non	non	3000
Qatiq	sut mahsuloti	9000
Sut	sut mahsuloti	12000

Ichimlik ichida Suv (4000) Choy (15000) dan oldin — chunki ikkinchi mezon narx o’sish tartibida.

LIMIT — qatorlar sonini cheklash

Ba’zan faqat dastlabki bir nechta qator kerak — “eng qimmat 3 mahsulot” kabi. Buning uchun LIMIT ishlatiladi:

SELECT nom, narx FROM mahsulotlar ORDER BY narx DESC LIMIT 3;

Natija — eng qimmat uchtasi:

nom	narx
Guruch	18000
Choy	15000
Banan	14000

Bu yerda buyruqlar birga ishladi: avval ORDER BY narx DESC tartibladi, keyin LIMIT 3 faqat birinchi uchtasini oldi.

OFFSET — boshidan tashlab yuborish

OFFSET dastlabki bir nechta qatorni o’tkazib yuboradi. “4-o’rindan boshlab” kabi — sahifalashda (pagination) qulay:

SELECT nom, narx FROM mahsulotlar ORDER BY narx DESC LIMIT 3 OFFSET 2;

Bu kamayish tartibida birinchi 2 tani (Guruch, Choy) tashlab, keyingi 3 tani beradi: Banan (14000), Sut (12000), Qatiq (9000).

Amaliy mashq

Tartiblang va cheklang:

1. Mahsulotlarni narx bo’yicha o’sish tartibida chiqaring;
2. DESC qo’shib, kamayish tartibiga o’tkazing;
3. LIMIT 3 bilan faqat eng qimmat uchtasini oling;
4. OFFSET 3 qo’shib, keyingi uchtasini oling;
5. ikki ustun bo’yicha tartiblang: avval turi, keyin narx;
6. ORDER BY soni DESC LIMIT 1 bilan omborda eng ko’p turgan mahsulotni toping — qaysi tovar chiqdi?

5-qadamda ustunlar tartibini almashtiring — natija o’zgardimi?

Qisqacha xulosa

• ORDER BY ustun — natijani tartiblaydi; standart ASC (o’sish).
• DESC — kamayish tartibi; matn alifbo bo’yicha tartiblanadi.
• ORDER BY a, b — avval a, teng bo’lsa b bo’yicha.
• LIMIT n — dastlabki n qatorni oladi; OFFSET m — dastlabki m tani tashlaydi.
• Tartib: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ....

Keyingi darsda INSERT bilan bazaga yangi ma’lumot qo’shishni ko’ramiz — shu paytgacha faqat o’qidik, endi yozamiz.