Learn Programming

One of the things that confuse SQL users all the time is how DISTINCT and ORDER BY are related in a SQL query. The Basics Running some queries against the Sakila database , most people quickly understand: SELECT DISTINCT length FROM film This returns results in an arbitrary order, because the database can (and might apply hashing rather than ordering to remove duplicates): length | -------| 129 | 106 | 120 | 171 | 138 | 80 | ... Most people also understand: SELECT length FROM film ORDER BY length This will give us duplicates, but in order: length | -------| 46 | 46 | 46 | 46 | 46 | 47 | 47 | 47 | 47 | 47 | 47 | 47 | 48 | ... And, of course, we can combine the two: SELECT DISTINCT length FROM film ORDER BY length Resulting in… length | -------| 46 | 47 | 48 | 49 | 50 | 51 | 52 | 53 | 54 | 55 | 56 | ... Then why doesn’t this work? Maybe somewhat intui...

Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: docker pull postgres:11 docker run --name POSTGRES11 -e POSTGRES_PASSWORD=postgres -d postgres:11 docker run -it --rm --link POSTGRES11:postgres postgres psql -h postgres -U postgres See also: https://hub.docker.com/r/_/postgres The frame clause When working with window functions , in some cases you want to add the optional frame clause. For example, to get a sliding average over your data, you will write: SELECT payment_date, amount, avg(amount) OVER ( ORDER BY payment_date, payment_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING )::DECIMAL(10, 2), array_agg(amount) OVER ( ORDER BY payment_date, payment_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING ) FROM payment; As always I will be running queries against the Sakila database . The above query yields: payment_dat...

Imagine you have a configuration table like the following: CREATE TABLE rule ( name VARCHAR2(50) NOT NULL PRIMARY KEY, enabled NUMBER(1) DEFAULT 1 NOT NULL CHECK (enabled IN (0,1)), priority NUMBER(10) DEFAULT 0 NOT NULL, flag1 NUMBER(3) DEFAULT 0 NOT NULL, flag2 NUMBER(3) DEFAULT 0 NOT NULL, flag3 NUMBER(3) DEFAULT 0 NOT NULL, flag4 NUMBER(3) DEFAULT 0 NOT NULL, flag5 NUMBER(3) DEFAULT 0 NOT NULL ); It specifies a set of rules that Can be enabled / disabled Can be given a priority among themselves Include a set of flags which correspond to the thing you want to configure (e.g. some check to execute) Those flags can be ordered as well So, given the following data: INSERT INTO rule (name, priority, flag1, flag5) VALUES ('RULE 1', 1, 1, 2); INSERT INTO rule (name, priority, flag2, flag5) VALUES ('RULE 2', 2, 2, 1); INSERT INTO rule (name, priority, flag3, flag4, flag5) VALUES ('RULE 3', 3, 3, 1...