ãåå¿è åããSQLã«ãããORDER BYïŒããŒã¿ã®äžŠã³æ¿ãïŒãåããããã解説
ORDER BYå¥ã¯ãSQLã®SELECTæã§ååŸããããŒã¿ãäžŠã³æ¿ããéã«äœ¿çšããŸããORDER BYã¯ã·ã³ãã«ã«äœ¿ãããšãã§ããŸãããæå€ã«å¥¥ãæ·±ããå©çšæ©äŒãå€ãã§ããORDER BYãšã¯ïŒ
ORDER BYå¥ã¯ãSQLã®SELECTæã§ååŸããããŒã¿ãäžŠã³æ¿ããéã«äœ¿çšããŸãã
ORDER BYã¯ã·ã³ãã«ã«äœ¿ãããšãã§ããŸãããæå€ã«å¥¥ãæ·±ããå©çšæ©äŒãå€ãã§ãã
åºæ¬æ§æã¯äžèšã®éãã§ãã
SELECT åå1, åå2, ...
FROM ããŒãã«å
ORDER BY åå1 [ASC|DESC], åå2 [ASC|DESC], ...;
- ååïŒäžŠã³æ¿ããè¡ãããåãæå®ããŸãã
- ASCïŒæé ïŒascendingïŒã§äžŠã³æ¿ããè¡ããŸããæå®ããªãå Žåã¯ASCãé©çšãããŸãã
- DESCïŒéé ïŒdescendingïŒã§äžŠã³æ¿ããè¡ããŸãã
ã§ã¯å ·äœäŸãèŠãŠã¿ãŸãããã
åºæ¬çãªäœ¿ãæ¹
ã·ã³ãã«ãªäžŠã³æ¿ã
ãŸããµã³ãã«ããŒã¿ãäœããŸãã
CREATE TABLE users(name varchar(100), age integer);
INSERT INTO users(name, age) values("ç°äž äžé", 30);
INSERT INTO users(name, age) values("ç°äž äºé", 25);
INSERT INTO users(name, age) values("ç°äž äžé", 15);
INSERT INTO users(name, age) values("ç°äž åé", 15);
ãã®ããŒãã«ã«å¯ŸããŠååãšå¹Žéœ¢ãååŸããå¹Žéœ¢ã®æé ã§äžŠã³æ¿ããã«ã¯ã以äžã®ã¯ãšãªãå®è¡ããŸãã
SELECT name, age
FROM users
ORDER BY age ASC;
ãã®ã¯ãšãªãå®è¡ãããšã
name age
ç°äž äžé 15
ç°äž åé 15
ç°äž äºé 25
ç°äž äžé 30
ãè¿åŽããã幎霢ã®è¥ãé ã«ååŸã§ããããšãããããŸãã
ãŸã幎霢ã®éé ã§äžŠã³æ¿ããã«ã¯ã以äžã®ã¯ãšãªãå®è¡ããŸãã
SELECT name, age
FROM users
ORDER BY age DESC;
ãã®ã¯ãšãªãå®è¡ãããšã
name age
ç°äž äžé 30
ç°äž äºé 25
ç°äž äžé 15
ç°äž åé 15
ãè¿åŽããã幎霢ã®é«ãé ã«ååŸã§ããããšãããããŸãã
è€æ°åã«ããäžŠã³æ¿ã
ORDER BYå¥ã§ã¯ãè€æ°ã®åãæå®ããŠäžŠã³æ¿ããããšãå¯èœã§ãã
ãã®å Žåãæåã®åã§äžŠã³æ¿ããããåãå€ãæã€è¡ã¯2çªç®ã®åã§äžŠã³æ¿ãããããšãã£ãå ·åã«ãæå®ããé åºã§äžŠã³æ¿ããè¡ãããŸãã
次ã®ã¯ãšãªã¯ãååãšå¹Žéœ¢ãååŸããå¹Žéœ¢ã®æé ã§äžŠã³æ¿ããŠãåã幎霢ã ã£ãå Žåã¯ãååã®éé ã§äžŠã³æ¿ããã¯ãšãªã§ãã
SELECT name, age
FROM users
ORDER BY age ASC, name DESC;
ãã®ã¯ãšãªãå®è¡ãããšã
name age
ç°äž åé 15
ç°äž äžé 15
ç°äž äºé 25
ç°äž äžé 30
ãè¿åŽããã幎霢ã®è¥ãé ã«äžŠã³æ¿ããåŸãååã®éé ã«äžŠã³æ¿ããè¡ã£ãŠããããšãããããŸãã
å¿çšçãªäœ¿ãæ¹
LIMITãšã®çµã¿åãã
ORDER BYå¥ã¯ãLIMITå¥ãšçµã¿åãããŠäœ¿çšããããšããããŸãã
äŸãã°ã幎霢ãé«ãé ã«äžäœ2人ã®ãŠãŒã¶ãŒãååŸãããå Žåãäžèšã®ãããªã¯ãšãªãå®è¡ããããšã§ååŸããããšãã§ããŸãã
SELECT name, age
FROM users
ORDER BY age DESC
LIMIT 2
;
ãŸãåœç¶WHEREå¥ã䜿ãããšãã§ããã®ã§ãã30æ³ä»¥äžã®ãŠãŒã¶ã®ãã¡ãäžäœ2人ãååŸããããšãã£ãããšããäžèšã®ãããªã¯ãšãªãå®è¡ããããšã§ååŸããããšãã§ããŸãã
SELECT name, age
FROM users
WHERE age >= 30
ORDER BY age DESC
LIMIT 2;
æåŸã«ã»ã»
ãããã§ããã§ããããïŒ
ORDER BYå¥ã¯éåžžã«ã·ã³ãã«ã§ããããããã§ãããå©çšæ©äŒãå€ãã§ãã
誀ã£ãäœ¿ãæ¹ãããªãããã«ããã®èšäºãåèã«å®è·µããŠãããŸãããã












