Home.... | log in.... | Pub my question! | Sign-up....

Java-index >> Enterprise & Remote Computing >> Web Tier APIs

how can i get only 10 rows from oracle table?

[52 byte] By [Daiesha] at [2007-11-26 16:31:11]

«« (--x) Vs (- - x)
»» About error and exceptions question

# 1

select column_name, rowcolumn from (select column_name,(row_number() over (order by name)) rowcolumnfrom table_name) where rowcolumn between 1 and 10

jgalacambraa at 2007-7-8 22:55:42 >

top of Java-index,Enterprise & Remote Computing,Web Tier APIs...

# 2

Hi// For 10 rowsSELECT * FROMtable_nameWHERE ROWNUM<=10// For randomly 10 rowsSELECT * FROM( SELECT * FROM TableNameORDER BY dbms_random.value )WHERE ROWNUM<=10

java_usera at 2007-7-8 22:55:42 >

# 3

> Hi

> // For 10 rows

> SELECT * FROM

> table_name

> WHERE ROWNUM<=10

> // For randomly 10 rows

> SELECT * FROM

> ( SELECT * FROM TableName

> ORDER BY dbms_random.value )

> WHERE ROWNUM<=10

it is not so effecient as you think it is, try to query ROWNUM between 2 and 10 and test if you will get the same data like the rows you fetch in rownum<=10

jgalacambraa at 2007-7-8 22:55:42 >

# 4

select * from table_name where ROWNUM=2;it's wont work.why?how can i get n'th record?Thanks,

Daiesha at 2007-7-8 22:55:42 >

# 5

use this select rule_name from create_rules order by create_date desc limit 10

uk0102a at 2007-7-8 22:55:42 >

# 6

> select * from table_name where ROWNUM=2;

> it's wont work.why?

> how can i get n'th record?

> Thanks,

i poseted the query that you nededed.. on the query you can change the value of 1 and 10 like 20 and 30 or 40 and 100..

jgalacambraa at 2007-7-8 22:55:42 >

# 7

>select * from table_name where ROWNUM=2;

ROWNUM is the number of the row returned in the current query.

It will always start at 1.

It will increment every time it successfully matches and returns a row.

Setting a criteria of ROWNUM = 2 prevents it from ever matching a row, thus the ROWNUM never gets incremented.

Sound logical?

evnafetsa at 2007-7-8 22:55:42 >

# 8

logical enough :)

jgalacambraa at 2007-7-8 22:55:42 >

# 9

> use this > > > select rule_name from create_rules order by> create_date desc limit 10can we use limit in oracle?

Daiesha at 2007-7-8 22:55:42 >

# 10

i already post this queryselect column_name, rowcolumn from (select column_name,(row_number() over (order by name)) rowcolumnfrom table_name) where rowcolumn between 1 and 10 it is working on oracle 8i,9i,10g

jgalacambraa at 2007-7-8 22:55:42 >

# 11

it's working fine.Thanks a lot..

Daiesha at 2007-7-8 22:55:42 >

# 12

btw the functions that i used were one of the analytic functions in oracle, so the query is special for oracle

jgalacambraa at 2007-7-8 22:55:42 >

# 13

> select * from table_name where ROWNUM=2;

> it's wont work.why?

> how can i get n'th record?

> Thanks,

Do it :

select * from

(select

from

table_name

where

rownum <3

order by rownum desc) tab

where

rownum=1

You have the second row !

Change the 3 to 4 to have the row number 3.etc...

Nimzoa at 2007-7-8 22:55:42 >

Enterprise & Remote Computing

Web Tier APIs

Java programming resources: FAQs, tutorials, compiler and browser download sites, documentation, books lists, IDEs, etc.

how can i get only 10 rows from oracle table?

Enterprise & Remote Computing New Topic

Enterprise & Remote Computing Hot Topic

Enterprise & Remote Computing

All Classified