Select Top-N in Oracle

Use DENSE_RANK to select top-n of a table in Oracle SELECT * FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DENSE_RANK () OVER (ORDER BY SALARY DESC) TOPRANK FROM HR.EMPLOYEES) WHERE TOPRANK <= 3

Select Last Row in Oracle

SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = (SELECT MAX (EMPLOYEE_ID) FROM HR.EMPLOYEES);

Manipulate NULL column values for fast sql performance

By default, NULL values means “not present”. Therefore, Oracle indexes will not include NULL values. If you wonder about how slow your sql query, you must check whether your sql include where condition that use IS NULL. When you use IS NOT NULL, your sql run normally. But if where condition include IS Null, sql [...]

First Day and Last Day, First Date and Last Date

SELECT TO_NUMBER (TO_CHAR (TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, -1)) + 1), ‘DD’)) FIRST, TO_NUMBER (TO_CHAR (LAST_DAY (SYSDATE), ‘DD’)) LAST FROM DUAL SELECT LAST_DAY (ADD_MONTHS (SYSDATE, -1)) + 1 FIRST, LAST_DAY (SYSDATE) LAST FROM DUAL

Create or Export data from MDB to DBF

To export data that have been query by “SELECT … FROM” in mdb file to dbf file, you can use this sample query : SELECT * INTO [dBase IV;database=C:\].newdbf FROM bank