Monthly Archives: June 2011

Ranking the rows

I was asked a few days to write a SQL that would retrieve the one but latest row. Take as an example a few rows below: we have a several functions with their min salary. The question is: which function earns the one but highest minumum salary. In the rows below, we have minimum salary 20000, 15000, 3000 and 8200. The outcome would be the row where the minimum salary 15000 is found as we should disregard the highest number (here 20000).

JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
AD_PRES President
20000
40000
AD_VP Administration Vice President
15000
30000
AD_ASST Administration Assistant
3000
6000
FI_MGR Finance Manager
8200
16000

To this problem, we have two solutions:
One solution is:

SELECT rownum, min_salary from 
(select min_salary from jobs order by min_salary desc)A ;

Here, we order the table first, then return the rows with the rownumber. This could then be filtered on rownumber=2.
The second solution is:

SELECT min_salary,
       RANK() OVER (ORDER BY min_salary desc) "rank"
FROM   jobs;

This makes use of the so-called analytical functions. It leads to the same result: after a filter on RANK()=2, we have the desired row.

SQLite

SQLite is the smallest database server we know. The engine itsself is about 500 KB – considerably less than other engines. It can be downloaded from http://www.sqlite.org/ .
Once downloaded, it can be started by: sqlite3 test.db. Here, the sqlite3 is the database engine that is called and test.db the database.
I have downloaded a graphical client ( SQLite2009Pro ), that allows us to maintain the database. Moreover, the client also contains OBDC drivers which allows the exchange of data from different datasources to SQLlite.

I understand that SQLite is the database that is most widely used in the world. It is contained in MP3 players, cellphones, but also in programmes like Morzilla, PHP etc. Most likely, most users are not even aware that they have a SQLite database as it is embedded software.

I myself installed everything in about an hour: it is really straightforward.