SQLite ORDER BY: מיון שורות ASC, DESC ולפי כמה עמודות

בלי ORDER BY, סדר השורות אינו מוגדר

SELECT בלי ORDER BY מחזיר שורות בכל סדר שנוח ל-SQLite. בטבלה קטנה זה נראה לעתים קרובות כמו סדר ההכנסה, וזה גורם לאנשים לסמוך עליו. אל תסמכו. ברגע שנעשה שימוש באינדקס, או שהטבלה גדלה, או שתוכנית השאילתה משתנה, הסדר הזה יכול לזוז בלי אזהרה.

אם הסדר של השורות חשוב לכם, אמרו את זה במפורש:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country FROM users ORDER BY name;

ORDER BY name ממיין בסדר עולה כברירת מחדל. התוצאה חוזרת Ada, Boris, Chen, Rosa, לפי האלפבית, בכל פעם, בלי קשר לאופן שבו הטבלה שמורה בדיסק.

ASC ו-DESC

ASC פירושו סדר עולה (מהקטן לגדול, מ-A עד Z, מהישן לחדש). DESC פירושו סדר יורד, ההפך. ASC הוא ברירת המחדל, ולכן כמעט תמיד תראו שהוא לא נכתב:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   '2024-01-05'),
    ('Rosa',  '2024-02-20'),
    ('Chen',  '2024-03-01');

SELECT name, signup_date FROM users ORDER BY signup_date DESC;

כך מקבלים קודם את ההרשמות האחרונות. תאריכים שנשמרים כמחרוזות ISO 8601 (YYYY-MM-DD) ממוינים נכון כטקסט, וזו אחת הסיבות שהפורמט הזה מועדף לעמודות תאריך ב-SQLite, שאין לה טיפוס תאריך ייעודי.

מיון לפי כמה עמודות

כשיש שוויון בעמודת המיון הראשונה, SQLite משתמשת בעמודה השנייה כדי לשבור אותו. רשמו את העמודות מופרדות בפסיקים, לפי סדר העדיפות:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO users (name, country) VALUES
    ('Boris', 'FR'),
    ('Ada',   'US'),
    ('Rosa',  'FR'),
    ('Chen',  'US');

SELECT name, country FROM users ORDER BY country, name;

השורות מקובצות קודם לפי מדינה (FR לפני US), ובתוך כל מדינה הן ממוינות לפי שם. לכל עמודה יכול להיות כיוון משלה:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country, signup_date
FROM users
ORDER BY country ASC, signup_date DESC;

מדינה בסדר עולה, ואז החדשים קודם בתוך כל מדינה. ASC ו-DESC חלים על העמודה שלידה הם כתובים, והם לא עוברים הלאה.

מיון לפי ביטויים וכינויים

ORDER BY מקבל כל ביטוי, לא רק שמות עמודות. זה שימושי לערכים מחושבים:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    quantity INTEGER
);

INSERT INTO products (name, price, quantity) VALUES
    ('Notebook', 4.50, 30),
    ('Pen',      1.20, 100),
    ('Lamp',    25.00, 5);

SELECT name, price * quantity AS revenue
FROM products
ORDER BY revenue DESC;

הכינוי revenue מרשימת ה-SELECT מותר לשימוש ב-ORDER BY. אפשר גם לכתוב שוב את הביטוי, ORDER BY price * quantity DESC, וזה עובד אותו דבר.

אפשר גם למיין לפי מיקום העמודה, אבל כדאי להימנע מההרגל הזה:

SELECT name, price FROM products ORDER BY 2 DESC;

2 פירושו העמודה השנייה ברשימת ה-select. זה עובד, אבל אם מישהו ישנה אחר כך את סדר העמודות, משמעות המיון תשתנה בשקט. מיינו לפי שם או כינוי במקום.

איפה נוחתים ערכי NULL

NULL פירושו "לא ידוע", ו-SQLite צריכה להחליט איפה ערכים לא ידועים שייכים במיון. כלל ברירת המחדל: NULL מופיעים ראשונים ב-ASC ואחרונים ב-DESC.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users ORDER BY signup_date;

Ada ו-Chen מופיעים למעלה, לפני כל תאריך אמיתי. זה רק לעתים רחוקות מה שרוצים ב"חדשים קודם". שנו את זה עם NULLS LAST:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users
ORDER BY signup_date DESC NULLS LAST;

עכשיו התאריכים האמיתיים באים קודם, וה-NULL בתחתית. NULLS FIRST עושה את ההפך. שניהם חלק מ-SQL התקני ועובדים ב-SQLite מגרסה 3.30 ואילך.

מיון בלי תלות ברישיות עם COLLATE NOCASE

השוואת הטקסט של SQLite כברירת מחדל היא בינארית: היא ממיינת לפי נקודות קוד של Unicode. המשמעות היא שאותיות גדולות ממוינות לפני קטנות, ולכן 'Zoe' מופיע לפני 'apple':

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name;

התוצאה היא Boris, Zoe, ada, apple: קודם אותיות גדולות, ואז קטנות. כדי למיין בלי תלות ברישיות, הוסיפו את ה-collation NOCASE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name COLLATE NOCASE;

עכשיו מקבלים ada, apple, Boris, Zoe. NOCASE מתייחס כשוות רק לאותיות ASCII בטווחים A עד Z ו-a עד z: הוא לא מנרמל סימני ניקוד או אותיות שאינן ASCII. למיון בינלאומי אמיתי צריך collation ברמת האפליקציה, אבל NOCASE מספיק למקרה הנפוץ של אנגלית.

סדר אקראי

לפעמים רוצים שורות בסדר אקראי: בחירת פריט מומלץ יומי, דגימת שורות לבדיקות. הפונקציה random() של SQLite מחזירה מספר שלם אקראי, ופשוט ממיינים לפיו:

CREATE TABLE quotes (id INTEGER PRIMARY KEY, text TEXT);

INSERT INTO quotes (text) VALUES
    ('Make it work, make it right, make it fast.'),
    ('Premature optimization is the root of all evil.'),
    ('Simple is better than complex.'),
    ('Readability counts.');

SELECT text FROM quotes ORDER BY random() LIMIT 1;

כל שורה מקבלת ערך אקראי חדש, והמיון מערבב אותן. זה בסדר לטבלאות קטנות. בטבלאות גדולות ORDER BY random() איטי: הוא צריך לחשב ערך אקראי לכל שורה ולמיין את כל התוצאה. לדגימת שורה אחת מטבלה ענקית, גישות מתוחכמות יותר (למשל בחירת rowid אקראי) מהירות יותר.

מלכודות נפוצות

כמה דברים שתופסים אנשים:

• לשכוח את ORDER BY ולהניח סדר. בלעדיו הסדר אינו מוגדר. גם אם הוא נראה יציב, הוא לא.
• מיון מספרים שנשמרו כטקסט. '10' ממוין לפני '2' בסדר מילוני. אם עמודה צריכה להיות ממוינת מספרית, שמרו אותה עם affinity מספרי (או המירו: ORDER BY CAST(value AS INTEGER)).
• ערבוב ASC ו-DESC בין עמודות. לכל עמודה כיוון משלה. ORDER BY a, b DESC ממיין את a בסדר עולה ואת b בסדר יורד, לא את שתיהן בסדר יורד.
• מיון של תוצאה ענקית רק כדי לקחת כמה שורות עליונות. שלבו את ORDER BY עם LIMIT והוסיפו אינדקס על עמודת המיון: זה העמוד הבא.

הבא: LIMIT ו-OFFSET

המיון אומר ל-SQLite איך לסדר את השורות, ו-LIMIT ו-OFFSET אומרים לה כמה להחזיר ומאיפה להתחיל. יחד הם הבסיס לדפדוף ולשאילתות "N הראשונים", וזה מה שבא עכשיו.