Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Oracle SQL into #3

Order By, ROWNUM, top-N and pagination queries
by

Rodion Mironov

on 3 April 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL into #3

Lesson agenda: ordering query results;
ROWNUM;
top-N queries;
pagination queries. I can order query results using:
column names;
column indexes;
aliases;
expressions; SELECT *|{[DISTINCT] column|expression [[as] alias],...}
FROM table
[WHERE condition(s)]
[ORDER BY column|alias|index|expression [ASC|DESC ] [NULLS FIRST|LAST],...]; ORDER BY clause SELECT last_name
FROM employees
ORDER BY last_name SELECT last_name, salary
FROM employees
ORDER BY 2, 1 SELECT last_name || ' ' || first_name as full_name
FROM employees
ORDER BY full_name SELECT last_name, first_name
FROM employees
ORDER BY (1+NVL(commission_pct,0))*salary Not really cool in production code. Not necessary to SELECT what I use in ORDER BY Order can be ascending
and descending SELECT
first_name
,last_name
,hire_date
FROM employees
ORDER BY hire_date desc
,last_name asc; -- ASC is the default option NULLs can be placed first or last SELECT last_name, commission_pct
FROM employees
ORDER BY commission_pct nulls first Introduction to Oracle SQL. Ordering data. ROWNUM
pseudocolumn;
number of row in query output;
useful for first-N queries and pagination Just 5 random employees SELECT rownum, last_name, hire_date
FROM employees
WHERE rownum <= 5 5 most recently hired people ? SELECT rownum, last_name, hire_date
FROM employees
WHERE rownum <= 5
ORDER BY hire_date desc My! I see familiar faces! What the *** ? ROWNUM is assigned:
after filtering...
but before any sorting or aggregation. So how should I write top-N queries ? Subquery! SELECT * FROM
(SELECT last_name, hire_date
FROM employees
ORDER BY hire_date desc)
WHERE rownum <= 5 http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html Practice 1.List all cities from LOCATIONS table in alphabetical order;alphabetical order;

2.Select top 3 longest job periods from JOB_HISTORY;

3.Your application shows top-paid employees, 10 persons per page. Write a query for the second page;
ORDER BY clause: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2168299

ROWNUM:


ROWNUM by Tom Kyte: Useful links http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#i1006297 http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html Please, note:
no ORDER BY - no order. There's no "order rows were inserted in";
There's no "order rows are stored in";
There's no "order guaranteed by execution plan"; ROWNUM and Top-N queries select
rownum
,region_id ,region_name
from regions; Pagination queries Interesting, what will this query return ? SELECT rownum, last_name, hire_date
FROM employees
WHERE rownum > 10
and rownum <=20 Yes, conditions like 'rownum > N'
will never be true (with positive N :)).

So how can we write pagination queries? 3 steps for pagination query: 1. Sort rows. 2. "Remeber" rows order. 3. Filter rows you need. SELECT last_name, hire_date
FROM employees
ORDER BY hire_date desc; SELECT rownum as rn, sub1.*
FROM
(SELECT last_name, hire_date
FROM employees
ORDER BY hire_date desc) sub1; SELECT sub2.*
FROM
(SELECT rownum as rn, sub1.*
FROM
(SELECT last_name, hire_date
FROM employees
ORDER BY hire_date desc) sub1
) sub2
WHERE rn > 10
and rn <= 20; Note: selecting top-N by query is MUCH more effective than simple "order by" query with fetching top N rows.
Full transcript