Don’t do:
SELECT first_name, last_name
FROM employees
WHERE rownum = 1
ORDER BY creation_date DESC
You probably want to do this instead:
SELECT * from (
SELECT first_name, last_name
FROM employees
ORDER BY creation_date DESC
) where rownum = 1
The first query takes the first row returned by Oracle and orders it by creation_date.
The second query gets the first row of the sub-query that contains the sorted records.
More on Stack Overflow
Written on 15 May 2014, 10:09am
Another post in the series ‘remember this SQL trick‘ 🙂
The within group clause can be used to merge multiple records into a single row. In the classic emp table with ename and deptno columns, the query is:
select
deptno,
listagg (ename, ',')
WITHIN GROUP
(ORDER BY ename) enames
FROM
emp
GROUP BY
deptno
The article below displays more ways of doing this, but the query above worked for me.
Oracle SQL: displaying multiple column values per row
A quick note about what the Oracle TRUNC function can do:
If you have a table ORDERS with a date field DATE_CREATION and you want to filter the records with a given date, you can use the TRUNC function.
SELECT ID
FROM ORDERS
WHERE TRUNC(ORDERS.DATE_CREATION) = TO_DATE('27/01/2014', 'DD/MM/YYYY')
This will truncate the time from the DATE_CREATION and leave out only the date.
The full syntax for TRUNC is:
TRUNC ( date, [ format ] )
date is the date to truncate.
format is the unit of measure to apply for truncating. If the format parameter is omitted, the TRUNC function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.
techonthenet.com
Update, 28/feb/2014: Another Oracle goodie – how to find out details about sequences:
select sequence_owner, sequence_name, min_value, max_value, last_number
from all_sequences
order by sequence_owner, sequence_name;