clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 582812 fiddles created (13412 in the last week).

CREATE TABLE route ( route_id INTEGER NOT NULL, origin VARCHAR(2) NOT NULL, destination VARCHAR(2) NOT NULL, -- CHECK (destination != origin) - can do it this way (remove comma) CONSTRAINT routes_orig_dest_distinct_ck CHECK (destination != origin) -- Better as it gives a meaningful name to the CONSTRAINT -- You can check this by swapping the CONSTRAINTs );
 hidden batch(es)


INSERT INTO route ( route_id, origin, destination) VALUES ( 1, 'A', 'B' ), ( 2, 'C', 'D' ), ( 3, 'B', 'A' ), ( 4, 'C', 'A' ), ( 5, 'D', 'C' );
5 rows affected
 hidden batch(es)


INSERT INTO route VALUES ( 6, 'Z', 'Z' ); -- will fail because of CONSTRAINT, -- notice informative name!
ERROR: new row for relation "route" violates check constraint "routes_orig_dest_distinct_ck" DETAIL: Failing row contains (6, Z, Z).
 hidden batch(es)


SELECT * FROM route;
route_id origin destination
1 A B
2 C D
3 B A
4 C A
5 D C
 hidden batch(es)


SELECT point_1, point_2, COUNT(*) FROM ( SELECT CASE WHEN origin < destination THEN origin ELSE destination END AS point_1, CASE WHEN destination > origin THEN destination ELSE origin END as point_2 FROM route ) tab GROUP BY point_1, point_2 ORDER BY point_1, point_2;
point_1 point_2 count
A B 2
A C 1
C D 2
 hidden batch(es)


WITH the_route AS ( SELECT CASE WHEN origin < destination THEN origin ELSE destination END AS point_1, CASE WHEN destination > origin THEN destination ELSE origin END as point_2 FROM route ) SELECT point_1, point_2, COUNT(*) FROM the_route GROUP BY point_1, point_2 ORDER BY point_1, point_2;
point_1 point_2 count
A B 2
A C 1
C D 2
 hidden batch(es)