SQL/challenge-009/4.2.sql at main · wmdev0808/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
USE Animal_Shelter; -- For SQL Server

-- Using ordinal positions
SELECT	*
FROM	Animals
ORDER BY 2, 5, 1;

/* PostgreSQL

-- https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1661d82a3969780042b28b139b2bb293&hide=2

SELECT	Species, COUNT(*)
FROM	Animals
GROUP BY 1
ORDER BY 2 DESC;
*/

-- Order by
SELECT	Adoption_Date,
		Species,
		Name
FROM	Adoptions
ORDER BY Adoption_Date DESC;

SELECT	Species,
		Name
FROM	Adoptions
ORDER BY Adoption_Date DESC;

-- DISTINCT and ORDER BY
SELECT 	DISTINCT
		Species,
		Name
FROM	Adoptions
ORDER BY Adoption_Date DESC;/*Error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.*/

-- Tie breakers
SELECT	*
FROM	Animals
ORDER BY Species; /*Sorted using dictionary order*/

SELECT	*
FROM	Animals
ORDER BY Species, Name;

SELECT	*
FROM	Animals
ORDER BY Implant_Chip_ID;

-- NULL sorting goodies
SELECT	*
FROM	Animals
ORDER BY Breed;

SELECT	*
FROM	Animals
ORDER BY Breed DESC;

/* PostgreSQL and Oracle

-- https://dbfiddle.uk/?rdbms=postgres_12&fiddle=62953002b268c424daa2f5d15838c36d&hide=2

SELECT	*
FROM	Animals
ORDER BY Breed NULLS LAST;
*/