Home » SQL & PL/SQL » SQL & PL/SQL » get the first records in a query
get the first records in a query [message #191030] Mon, 04 September 2006 03:58 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Hii all

i have a query suposse it is

select code from gitxxxxxx
order by code;


that results

aa12
aa18
ba53
mo33
sl40
wv24
zz20




i want to display only the first three records (results)
so that the result should be

aa12
aa18
ba53



can i do this


Thanks for Everyone helped and helping me
Re: get the first records in a query [message #191032 is a reply to message #191030] Mon, 04 September 2006 04:03 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

 select * from (
select col1 from your_table_name
order by col1)
where rownum<4 


or

 
select col1 from (
select col1 ,row_number()over(order by col1)rnk
from your_table_name
)
where rnk<4 



regards,

[Updated on: Mon, 04 September 2006 04:04]

Report message to a moderator

Re: get the first records in a query [message #191036 is a reply to message #191030] Mon, 04 September 2006 04:37 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

thanks for your reply

but if i want to display the last three records
what can i do


Re: get the first records in a query [message #191040 is a reply to message #191036] Mon, 04 September 2006 04:40 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

 just change the order by to desc and accordingly change the where clause 



regards,
Re: get the first records in a query [message #191047 is a reply to message #191030] Mon, 04 September 2006 04:53 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Thanks again for your reply

but suppose this is my query

    SELECT ROWNUM,GPO.NO,GPO.GVN_CODE
      FROM GPOXXXXXX GPO,GPODTXXXX GPODT,GVNXXXXXX GVN
     WHERE GPODT.GPO_NO = GPO.NO
      AND GPO.GVN_CODE = GVN.CODE
      AND GPODT.GIT_CODE ='AA18'
 --   AND  ROWNUM < 4
    ORDER BY GPO.NO DESC


that get the results


ROWNUM NO GVN_CODE
-------- --------- ----------
11 2101 fi064
10 1915 fi048
9 1488 fi008
8 1236 fi008
7 714 fi008
6 675 fl029
5 631 fi008
4 564 fl029
3 539 fi048
2 524 fl058
1 478 fi008




i want the result be only


ROWNUM NO GVN_CODE
-------- --------- ----------
11 2101 fi064
10 1915 fi048
9 1488 fi008

Re: get the first records in a query [message #191052 is a reply to message #191047] Mon, 04 September 2006 05:02 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

select rnum,no,gvn_code from(
select rnum,no,gvn_code,row_number()over(order by rnum desc)rnk
from test)
where rnk<4



regards,
Re: get the first records in a query [message #191053 is a reply to message #191030] Mon, 04 September 2006 05:05 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

when i use this "over" i got that error

ERROR at line 1:
ORA-00439: feature not enabled: OLAP Window Functions

[Updated on: Mon, 04 September 2006 05:05]

Report message to a moderator

Re: get the first records in a query [message #191054 is a reply to message #191053] Mon, 04 September 2006 05:11 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,


 by the way,what version are you using ?
you can try this .

select rnum,no,gvn_code from (
select rnum,no,gvn_code from test order by rnum desc)
where rownum<4




regards,
Re: get the first records in a query [message #191058 is a reply to message #191030] Mon, 04 September 2006 05:31 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

I am using Oracle8i Release 8.1.7.0.0
SQL*Plus: Release 8.0.6.0.0


and that query
select rnum,no,gvn_code from (
select rnum,no,gvn_code from test order by rnum desc)
where rownum<4



this results
ROWNUM NO GVN_CODE
--------- --------- ----------
1 478 fi008
2 524 fl058
3 539 fi048


and that is not the required

the required is

ROWNUM NO GVN_CODE
-------- --------- ----------
11 2101 fi064
10 1915 fi048
9 1488 fi008


Re: get the first records in a query [message #191069 is a reply to message #191030] Mon, 04 September 2006 06:08 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Please anyone can help me in this issue
Re: get the first records in a query [message #191138 is a reply to message #191069] Tue, 05 September 2006 00:26 Go to previous message
sandeepk7
Messages: 137
Registered: September 2006
Senior Member

Try this
select rnum,no,gvn_code from (
select rnum,no,gvn_code from test order by rowid desc)
where rownum<4
Previous Topic: sql%rowcount not working in FORALL
Next Topic: untrim column name
Goto Forum:
  


Current Time: Sat Dec 10 01:09:17 CST 2016

Total time taken to generate the page: 0.07961 seconds