OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Minus with Order by

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Minus with Order by [message #372005]

Wed, 03 January 2001 08:39

sri
Messages: 154
Registered: February 2000

Senior Member

hi,

I am using a query to fetch rows between two limits for navaigation on a web page(10 rows at a time).

some thing like this
SELECT *
FROM emp
WHERE rowid in (
SELECT rowid FROM emp
WHERE rownum <= 8
MINUS
SELECT rowid FROM emp
WHERE rownum < 2);
But I want to use Order by class for the end resut, ie. I want order by employee name and then select 10 rows each time.

I am getting an error saying
ERROR at line 5:
ORA-00907: missing right parenthesis

SELECT *
FROM emp
WHERE rowid in (
SELECT rowid FROM emp
WHERE rownum <= 8 order by empname
MINUS
SELECT rowid FROM emp
WHERE rownum < 2 order by empname);

Please let me how to over come this.

thanks inadvance,

sri

Report message to a moderator

Re: Minus with Order by [message #372006 is a reply to message #372005]

Wed, 03 January 2001 10:34

laxmi
Messages: 9
Registered: December 2000

Junior Member

Try this

SELECT *
FROM emp
WHERE rowid in (
SELECT rowid FROM emp
WHERE rownum <= 8
MINUS
SELECT rowid FROM emp
WHERE rownum < 2)
order by ename;

Report message to a moderator

Re: Minus with Order by [message #372007 is a reply to message #372005]

Wed, 03 January 2001 12:30

Ravi Prasad
Messages: 9
Registered: January 2001

Junior Member

SELECT customer_name
FROM
(
SELECT * FROM bsp_customers
WHERE rownum <= 5
MINUS
SELECT * FROM bsp_customers
WHERE rownum < 2 ) order by customer_name

This is will give in which ever order u want

bye
ravi

Report message to a moderator

Re: Minus with Order by [message #372008 is a reply to message #372005]

Wed, 03 January 2001 12:31

Ravi Prasad
Messages: 9
Registered: January 2001

Junior Member

SELECT *
FROM
(
SELECT * FROM emp
WHERE rownum <= 5
MINUS
SELECT * FROM emp
WHERE rownum < 2 ) order by name

This is will give in which ever order u want

bye
ravi

Report message to a moderator

Re: Minus with Order by [message #372018 is a reply to message #372006]

Thu, 04 January 2001 03:24

sri
Messages: 154
Registered: February 2000

Senior Member

This sql will first fetches the diffrence in data and then does a sort.

I am looking for a query in which data is ordered before fetching set of rows.

for eg; If the data contains
1 Usha
2 randy
3 sandeep
4 kiran
5 anil
6 babu

The query which I am expecting should result in (if two records are fetched at a time if I order by ename)

anil
babu
next >>

kiran
randy
next >>

sandeep
usha
next >>

~
regards,
Sri

Report message to a moderator

Re: Minus with Order by [message #374245 is a reply to message #372005]

Fri, 01 June 2001 08:58

Gaby
Messages: 11
Registered: March 2000

Junior Member

Try using HINTS (INDEX_ASC/INDEX_DESC) while indexing the column you want to order by.

Report message to a moderator

Previous Topic:	unique constraint
Next Topic:	test

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 03:18:47 CDT 2024