Home » SQL & PL/SQL » SQL & PL/SQL » rownum problem
rownum problem [message #218134] Tue, 06 February 2007 23:21 Go to next message
dwhite02
Messages: 1
Registered: February 2007
Junior Member
I'm have problems with the SQL statement below. I am trying to include a rownum statement to show the top 5 records. When I do this I am shown the incorrect records. Can anyone help please? I realise that I have to put the field names in instead of the *.

SELECT *
FROM DN16.ITEM, DN16.CHANNEL_ITEM
WHERE DN16.ITEM.GUID = DN16.CHANNEL_ITEM.ITEMID AND DN16.CHANNEL_ITEM.CHANNELID = 2 AND DN16.ITEM.CREATOR <> 'MMColParam'
ORDER BY DN16.ITEM.PUBDATE DESC
Re: rownum problem [message #218152 is a reply to message #218134] Wed, 07 February 2007 00:28 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
dwhite02 wrote on Wed, 07 February 2007 10:51
I'm have problems with the SQL statement below. I am trying to include a rownum statement to show the top 5 records. When I do this I am shown the incorrect records. Can anyone help please? I realise that I have to put the field names in instead of the *.


1. What problems you are facing?
2. What is the exact query?
3. Any Sample output
4. Any expected output

By
Vamsi
Re: rownum problem [message #218153 is a reply to message #218134] Wed, 07 February 2007 00:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Search this site for top-n to see how to do it.
Re: rownum problem [message #218424 is a reply to message #218153] Thu, 08 February 2007 03:07 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
SELECT *
FROM DN16.ITEM, DN16.CHANNEL_ITEM
WHERE DN16.ITEM.GUID = DN16.CHANNEL_ITEM.ITEMID AND DN16.CHANNEL_ITEM.CHANNELID = 2 AND DN16.ITEM.CREATOR <> 'MMColParam'
and rownum<=5 ORDER BY DN16.ITEM.PUBDATE DESC
Re: rownum problem [message #218426 is a reply to message #218424] Thu, 08 February 2007 03:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
an order by and a where rownum < x at the same level in a query will cause the where rownum < x to be performed first.
After choosing x-1 random rows, these will be ordered.

Like I said:
Quote:
Search this site for top-n to see how to do it.
Re: rownum problem [message #218433 is a reply to message #218426] Thu, 08 February 2007 03:31 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
select * from emp e ,dept d where

e.deptno= d.deptno and ename!='SMITH'and rownum<=5 order by hiredate desc

but iam geting perfect result. how it is possible?
Thanks
srinivas
Re: rownum problem [message #218434 is a reply to message #218433] Thu, 08 February 2007 03:42 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Srinivas,
Never justify a query depending only on the output of sample data.
Check this.
select * from scott.emp e where
ename!='SMITH'and rownum<=7 order by hiredate desc;
By
Vamsi

[Updated on: Thu, 08 February 2007 03:43]

Report message to a moderator

Re: rownum problem [message #218437 is a reply to message #218134] Thu, 08 February 2007 03:47 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
dwhite02 wrote on Wed, 07 February 2007 06:21
I'm have problems with the SQL statement below. I am trying to include a rownum statement to show the top 5 records. When I do this I am shown the incorrect records. Can anyone help please? I realise that I have to put the field names in instead of the *.

SELECT *
FROM DN16.ITEM, DN16.CHANNEL_ITEM
WHERE DN16.ITEM.GUID = DN16.CHANNEL_ITEM.ITEMID AND DN16.CHANNEL_ITEM.CHANNELID = 2 AND DN16.ITEM.CREATOR <> 'MMColParam'
ORDER BY DN16.ITEM.PUBDATE DESC


Maybe something like this?

SELECT *
  FROM (SELECT *
          FROM DN16.ITEM, DN16.CHANNEL_ITEM
         WHERE DN16.ITEM.GUID = DN16.CHANNEL_ITEM.ITEMID
           AND DN16.CHANNEL_ITEM.CHANNELID = 2
           AND DN16.ITEM.CREATOR <> 'MMColParam'
        ORDER BY DN16.ITEM.PUBDATE DESC)
 WHERE ROWNUM < 6
Re: rownum problem [message #218440 is a reply to message #218437] Thu, 08 February 2007 03:57 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
select * from
(select * from emp e ,dept d where e.deptno= d.deptno order by hiredate desc)
where rownum <=5


iam getting this result( ORA-00918: column ambiguously defined)

i think u's query is aslo same error.
Re: rownum problem [message #218441 is a reply to message #218440] Thu, 08 February 2007 04:00 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
May not.
In your query, both emp and dept are having the column deptno.
Hence this error.

By
Vamsi
Re: rownum problem [message #218446 is a reply to message #218440] Thu, 08 February 2007 04:32 Go to previous message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
pavuluri wrote on Thu, 08 February 2007 10:57
select * from
(select * from emp e ,dept d where e.deptno= d.deptno order by hiredate desc)
where rownum <=5


iam getting this result( ORA-00918: column ambiguously defined)

i think u's query is aslo same error.


No, my query is correct.
Modify yours:

select *
  from (select e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm,
               e.deptno e_deptno, d.deptno d_deptno, d.dname, d.loc
          from emp e, dept d
         where e.deptno = d.deptno
        order by hiredate desc)
 where rownum <= 5

Previous Topic: select username
Next Topic: Error while accessing files from PLSQL
Goto Forum:
  


Current Time: Sat Dec 10 09:22:16 CST 2016

Total time taken to generate the page: 0.07662 seconds