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

Tagged sql, pl/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 postgres, top, latest, window, partition  Languages sql

This SQL query selects the top 10 transactions grouped per user:

  • partition the transactions by user_id
  • order the transactions in each partition by the column created_at
  • keep order of the rank, per partition (first row is 1, second is 2)
  • select only first row, i.e. the latest
WITH latest_customer_transactions AS (
    *, rank() OVER (PARTITION BY user_id ORDER BY created_at desc) AS rank
  id, customer_id
  rank = 1;

Keep your fingers crossed that it works. For alternatives, see Shtack Overflow.

Also 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.

MySQL rownum equivalent

Tagged mysql, rownum, user, variable  Languages sql

MySQL doesn't have rownum, but user variables can be used as a replacement:

select @rownum:=@rownum+1 'rank', p.* from products p, (SELECT @rownum:=0) r order by created_at desc limit 10;

Read more: MySQL 5.0 Reference Manual

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 update, counter_cache, 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 collation, mysql, convert  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;