Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

> Oracle select statement

views
     
TSlonewolf
post Feb 23 2006, 03:20 PM, updated 14y ago

Look at all my stars!!
*******
Senior Member
3,944 posts

Joined: Jan 2003
SELECT * FROM PRODUCT WHERE ROWNUM< 50 ORDER BY CREATED DESC

minus

SELECT * FROM PRODUCT WHERE ROWNUM< 20 ORDER BY CREATED DESC


is to have error....am i suppost to do a sub select?

This post has been edited by lonewolf: Feb 23 2006, 03:20 PM
nxfx
post Feb 23 2006, 03:51 PM

Enthusiast
*****
Senior Member
938 posts

Joined: Jan 2003


and the error is..?? and what exactly you want to achieve?
rukawa
post Feb 23 2006, 08:02 PM

::+::KukuKawa::+::
******
Senior Member
1,902 posts

Joined: Jan 2003
From: Wg Maju


I think he wants to subtract from a result from one query from another query though I don't quite understand whether is it really.

This question is too vague.

This post has been edited by rukawa: Feb 23 2006, 08:03 PM
Marcus
post Feb 23 2006, 11:48 PM

Getting Started
**
Junior Member
286 posts

Joined: Jan 2003
From: Subang Jaya


It looks like you wanna extract data between rownum 20 to 49, rite? wink.gif

In a more simpler manner, i think the query should be like :

SELECT * FROM PRODUCT
WHERE ROWNUM BETWEEN 20 AND 49
ORDER BY CREATED DESC;

Try this and see. I don't think a sub-query is necessary. smile.gif



This post has been edited by Marcus: Feb 24 2006, 12:01 AM
TSlonewolf
post Feb 24 2006, 02:15 PM

Look at all my stars!!
*******
Senior Member
3,944 posts

Joined: Jan 2003
QUOTE(Marcus @ Feb 23 2006, 11:48 PM)
It looks like you wanna extract data between rownum 20 to 49, rite?  wink.gif

In a more simpler manner, i think the query should be like :

SELECT * FROM PRODUCT
WHERE ROWNUM BETWEEN 20 AND 49
ORDER BY CREATED DESC;

Try this and see.  I don't think a sub-query is necessary.  smile.gif
*
haha..that doesn work...

anyway i found the solution..is slightly more complex

here is the answer

select * from
(
select p.*,rownum row_num from (
select * from PRODUCT order by DATE desc
) p
) where row_num between 20 and 50
fly
post Feb 24 2006, 02:43 PM

¯\(°_o)/¯
Group Icon
VIP
1,119 posts

Joined: Jan 2003
QUOTE(lonewolf @ Feb 24 2006, 02:15 PM)
haha..that doesn work...

anyway i found the solution..is slightly more complex

here is the answer

select * from
(
select p.*,rownum row_num from (
select * from PRODUCT order by DATE desc
) p
) where row_num between 20 and 50
*
That seems overly complicated to me. Why not use:

CODE
select * from (
select p.*,rownum row_num from PRODUCT where row_num < 50 order by DATE desc
) where row_num > 20


You can cut out one nested statement this way.
anthony_yio
post Feb 24 2006, 07:07 PM

........
Group Icon
Elite
1,828 posts

Joined: Jan 2003


QUOTE(fly @ Feb 24 2006, 02:43 PM)
That seems overly complicated to me. Why not use:

CODE
select * from (
select p.*,rownum row_num from PRODUCT where row_num < 50 order by DATE desc
) where row_num > 20


You can cut out one nested statement this way.
*
Ok, some beautification on you guys codes.

Why not place all your SELECT* ,,, as VIEWS and call those VIEWS.

It hurts my eyes when I see all those SELECT here and there. (nested)


fly
post Feb 24 2006, 07:24 PM

¯\(°_o)/¯
Group Icon
VIP
1,119 posts

Joined: Jan 2003
QUOTE(anthony_yio @ Feb 24 2006, 07:07 PM)
Ok, some beautification on you guys codes.

Why not place all your SELECT* ,,, as VIEWS and call those VIEWS.

It hurts my eyes when I see all those SELECT here and there. (nested)
*
Because he may not have permission to create views in the database?

 

Switch to:
| Lo-Fi Version
0.0346sec    0.94    5 queries    GZIP Disabled
Time is now: 16th November 2019 - 04:56 AM