select * 
from sometable
order by name
limit 20,10

select * 
from sometable
where rownum <= 10
order by name

Answer :

SELECT * 
FROM   sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Examples:

Setup

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

What's in the table?

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

Get first N rows

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

Get first N rows, if N^th row has ties, get all the tied rows

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

Top x% of rows

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

Using an offset, very useful for pagination

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

You can combine offset with percentages

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

Answer:

• @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
• SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
• IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."
• The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

id (int)
udid (int)
assid (int)

id  (int)
assid  (int)

Answer :

update ud 
  set assid = (
               select sale.assid 
                 from sale 
                where sale.udid = ud.id
              )
 where exists (
               select * 
                 from sale 
                where sale.udid = ud.id
              );

update ud u
inner join sale s on
    u.id = s.udid
set u.assid = s.assid

update u
set u.assid = s.assid
from ud u
    inner join sale s on
        u.id = s.udid

update
    (select
        u.assid as new_assid,
        s.assid as old_assid
    from ud u
        inner join sale s on
            u.id = s.udid) up
set up.new_assid = up.old_assid

SELECT owner, table_name
  FROM dba_tables

SELECT owner, table_name
  FROM all_tables

SELECT table_name
  FROM user_tables

SELECT name, COUNT(email) 
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

ID   NAME   EMAIL
1    John   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com
4    Bob    bob@asd.com
5    Tom    asd@asd.com

Answer:

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

• GROUP BY lname ORDER BY showing wrong results
• Which is the least expensive aggregate function in the absence of ANY() (see comments in accepted answer)