Example of how to do a for loop with PL/SQL

Tagged pl/sql, sql, oracle, for loop  Languages sql

A PL/SQL example of a for loop that prints out the primary key for all rows returned by a query:

  ID VARCHAR2(50);
    DBMS_OUTPUT.ENABLE (1000000);
    FOR current_row IN (SELECT id FROM your_table)
        ID := current_row.id;    -- Assign value to variable
        DBMS_OUTPUT.PUT_LINE( '' || ID);
        DBMS_OUTPUT.PUT_LINE( '' || current_row.column_xxx);

Selecting the "top n" or "n latest" rows by group in Postgres

Tagged distinct, postgres, top, latest, window, partition  Languages sql

This SQL query selects the top 10 transactions for each user:

  • partition the transactions by user_id
  • order the transactions in each partition by the column created_at
  • select only first row, i.e. the latest
WITH latest_customer_transactions AS (
    *, row_number() OVER (PARTITION BY user_id ORDER BY created_at desc) AS rownum
  id, customer_id
  rownum <= 10;

Note that if you simply want the first record per group it’s probably best to use DISTINCT ON as described here: https://www.periscopedata.com/blog/first-row-per-group-5x-faster

SELECT DISTINCT ON (customer_id) *
FROM jobs
ORDER BY customer_id,
         priority DESC,

Note the differences between RANK, ROW_NUMBER, and DENSE_RANK: http://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/

Simplify and make SQL scripts run faster by using Oracle variables

Tagged sql, variables, oracle  Languages sql

Oracle variables are a great way of removing duplicate SQL statements from your Oracle SQL scripts. By using a variable, you can store the return value of a query and use it later in another, as this example illustrates:

COLUMN your_column new_value your_variable;

SELECT DISTINCT (your_column)
                                 FROM ...
                                WHERE ...;

SELECT &your_variable FROM dual;

The above example stores the value of the SELECT DISTINCT query in a variable named your_variable. You declare the variable with new_value, and set the value by executing an SQL statement that returns one row and column.

How to disable Oracle's variable name scanning

Tagged sql, oracle, disable  Languages sql

Normally Oracle will scan SQL for variables and prompt for the value at runtime, to disable this feature use the "set scan off" command:

set scan off;

select * from links where href = 'http://...&....';

How to update the ActiveRecord counter_cache magic column

Tagged counter_cache, update, sql, rails, active_record  Languages sql

You can use the model.update_counters method to update the counter_cache column. But if you have a million rows it be very fast.

So for large tables it's best to do it with a query such as this:

update categories, (select 
                      id as category_id, ifnull(count, 0) as count
                    from categories left join 
                      (select category_id, count(id) as count from products group by category_id) as count_table 
                      categories.id = count_table.category_id) as count_table
  categories.products_count = count_table.count
  categories.id = count_table.category_id

This query updates the count for all rows.

The code needs to be modified for your database design.

MySQL: How to change table and database collation

Tagged mysql, convert, collation  Languages sql

Listing table information including collation

show table status;

To show all table information use this command:

show full columns from xxx;

Convert existing columns in a table to a specific collation

alter table brands convert to character set utf8 collate utf8_swedish_ci;

This command converts all existing columns to the specified collation.

Change table collation

alter table posts collate utf8_swedish_ci;

Note that this doesn't change collation of existing columns.

Setting the collation manually

set session collation_database=utf8_swedish_ci;
set session collation_connection=utf8_swedish_ci;

Common collation values


Recursive queries with connectby in Postgres

Tagged postgres, recursive, connectby  Languages sql

Create an organization hierarchy and display the organization from the top:

CREATE TABLE organizations(id text, parent_id text, pos int);

INSERT INTO organizations VALUES('row1',NULL, 0);
INSERT INTO organizations VALUES('row2','row1', 0);
INSERT INTO organizations VALUES('row3','row1', 0);
INSERT INTO organizations VALUES('row4','row2', 1);
INSERT INTO organizations VALUES('row5','row2', 0);
INSERT INTO organizations VALUES('row6','row4', 0);
INSERT INTO organizations VALUES('row7','row3', 0);
INSERT INTO organizations VALUES('row8','row6', 0);
INSERT INTO organizations VALUES('row9','row5', 0);

-- Fetch self and descendants for row1
SELECT * FROM connectby('organizations', 'id', 'parent_id', 'row1', 0, '~') AS t(id text, parent_id text, level int, branch text);


id  | parent_id | level |          branch
 row1 |           |     0 | row1
 row2 | row1      |     1 | row1~row2
 row4 | row2      |     2 | row1~row2~row4
 row6 | row4      |     3 | row1~row2~row4~row6
 row8 | row6      |     4 | row1~row2~row4~row6~row8
 row5 | row2      |     2 | row1~row2~row5
 row9 | row5      |     3 | row1~row2~row5~row9
 row3 | row1      |     1 | row1~row3
 row7 | row3      |     2 | row1~row3~row7

Usually it's better to use recursive CTE queries:

WITH RECURSIVE organization_tree AS (
    id, parent_id 
    id = 'row1'
    organizations.id, organizations.parent_id 
    organizations, organization_tree
    organizations.parent_id = organization_tree.id
) select * from organization_tree;