Oracle SQL FAQ - Part3

1. How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
SELECT * FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)FROM emp);

Method 2: Use dynamic views (available from Oracle7.2):
SELECT * FROM (SELECT rownum rn, empno, enameFROM emp) temp
WHERE MOD(temp.ROWNUM,4) = 0;

Method 3: Using GROUP BY and HAVING
SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n;
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.

2. How does one select the LAST N rows from a table?
From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows. Examples:
Get the bottom 10 employees based on their salary
SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal ASC) sal_rank
FROM emp )
WHERE sal_rank <= 10;
Select the employees getting the lowest 10 salaries
SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;
For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:
SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1)
WHERE ROWNUM < 10;
Use this workaround for older (8.0 and prior) releases:
SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol <= a.maxcol)
ORDER BY maxcol;

3. How does one select the TOP N rows from a table?
From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the TOP N rows. Examples:
Get the top 10 employees based on their salary
SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) sal_rank
FROM emp )
WHERE sal_rank <= 10;

Select the employees making the top 10 salaries
SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;
For Oracle 8i and above, one can get the Top N rows using an inner-query with an ORDER BY clause:
SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC)
WHERE ROWNUM < 10;
Use this workaround for older (8.0 and prior) releases:
SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol >= a.maxcol)
ORDER BY maxcol DESC;

4. How to generate a text graphs (histograms) using SQL?
SELECT d.dname AS "Department",
LPAD('+', COUNT(*), '+') as "Graph"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
Sample output:
Department Graph
-------------- --------------------------------------------------
ACCOUNTING +++
RESEARCH +++++
SALES ++++++
In the above example, the value returned by COUNT(*) is used to control the number of "*" characters to return for each department. We simply pass COUNT(*) as an argument to the string function LPAD (or RPAD) to return the desired number of *'s.

Map/ concatenate several rows to a column
This FAQ will demonstrate how row values can be concatenated into a single column value (similar to MySQL's [i]GROUP_CONCAT[/i] function).
Start by creating this function:
SQL> CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
2 ret VARCHAR2(4000);
3 hold VARCHAR2(4000);
4 cur sys_refcursor;
5 BEGIN
6 OPEN cur FOR q;
7 LOOP
8 FETCH cur INTO hold;
9 EXIT WHEN cur%NOTFOUND;
10 IF ret IS NULL THEN
11 ret := hold;
12 ELSE
13 ret := ret || ',' || hold;
14 END IF;
15 END LOOP;
16 RETURN ret;
17 END;
18 /
Function created.
This function returns a string result with the concatenated non-NULL values from a SQL statement. It returns NULL if there are no non-NULL values.
Here is an example of how to map several rows to a single concatenated column:
SQL> SELECT rowconcat('SELECT dname FROM dept') AS departments
2 FROM dual;
DEPARTMENTS
-----------------------------------------------------------------------ACCOUNTING,RESEARCH,SALES,OPERATIONS
This example is more interresting, it concatenates a column across several rows based on an aggregation:
SQL> col employees format a50
SQL> SELECT deptno,
2 rowconcat('SELECT ename FROM emp a WHERE deptno='||deptno) AS Employees
3 FROM emp
4 GROUP BY deptno
5 /
DEPTNO EMPLOYEES
---------- --------------------------------------------------
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
20 SMITH,JONES,SCOTT,ADAMS,FORD
10 CLARK,KING,MILLER

5. What is the difference between VARCHAR, VARCHAR2 and CHAR data types?
Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:
CHAR
---------
CHAR should be used for storing fixed length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.
SQL> CREATE TABLE char_test (col1 CHAR(10));
Table created.

SQL> INSERT INTO char_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------ -----------------------------------------------qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32
Note: ASCII character 32 is a blank space.

VARCHAR
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));
Table created.

SQL> INSERT INTO varchar_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------ -----------------------------------------------qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121
VARCHAR2
----------------
VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.
SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));
Table created.

SQL> INSERT INTO varchar2_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------ -----------------------------------------------qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121