Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to select last 10 records from the table?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Abirami.YN

New User


Joined: 16 Jul 2005
Posts: 15
Location: India - Chennai

PostPosted: Sat Jul 16, 2005 3:59 pm    Post subject: How to select last 10 records from the table?
Reply with quote

HI,

I have to display last 10 records and

3rd to 5 th record from the same table.

How can i do ?
Back to top
View user's profile Send private message

parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Mon Jul 18, 2005 5:40 pm    Post subject: Re: How to select last 10 records from the table?
Reply with quote

Hi Abirami.YN,

In RDBMS, there is nothing like Nth record.

so, retriving 10 last records from a DB2 table doesn't make any sence.
Back to top
View user's profile Send private message
Abirami.YN

New User


Joined: 16 Jul 2005
Posts: 15
Location: India - Chennai

PostPosted: Tue Jul 19, 2005 6:32 pm    Post subject:
Reply with quote

HI Parikshit chudasma

We can retrive using the following code.
But i am getting -104 error.
I think this because of DB2 Version ( Iam not sure)

select * from (
select rownumber() over(order by fieldname) as r
from tablename
) as temptablename where r<= 10
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Wed Aug 03, 2005 3:15 pm    Post subject: hi
Reply with quote

Try this

Select * from table name fetch last 10 rows
Back to top
View user's profile Send private message
radhakrishnan82

Active User


Joined: 31 Mar 2005
Posts: 436
Location: chennai, India

PostPosted: Thu Aug 04, 2005 10:05 am    Post subject:
Reply with quote

Fetch last 10 rows will do.
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Fri Aug 05, 2005 2:35 pm    Post subject:
Reply with quote

Hi,


SELECT * FROM <<TABLE NAME>> ORDER BY <<Primary Key>> DESC FETCH FIRST 10 ROWS ONLY;;

It will work. Don't say that u don't have a Primary Key. If So how can you say that these are last 10 records. If you do not agree on this give me a real time situation where we need this.

If you say that it is asked in some X Interview then ask them for a real situation. If they are really good enough about CODD'S rules they will agree with you.

Thanks,
Reddy.
Back to top
View user's profile Send private message
thiagold

New User


Joined: 29 Nov 2005
Posts: 3

PostPosted: Tue Nov 29, 2005 8:25 pm    Post subject:
Reply with quote

ovreddy wrote:

SELECT * FROM <<TABLE NAME>> ORDER BY <<Primary Key>> DESC FETCH FIRST 10 ROWS ONLY;;


That way do not work ins DB2 V6, only V7 or +. Anybody knows how to do it in DB2 V6??
Back to top
View user's profile Send private message
dgrinch

New User


Joined: 16 Nov 2005
Posts: 4

PostPosted: Tue Nov 29, 2005 11:30 pm    Post subject: Re: How to select last 10 records from the table?
Reply with quote

Not sure if it's for batch or online that u're asking. Ofcourse u might be using a Curosr, so use clauses DESC & OPTIMIZE FOR n ROWS assuming you will give a proper primary key values. The same can be done for first 5 rows and programatically maniplulate 3 & 5 th row. But 3rd & 5th row manipulation kinda strikes me odd. It doesn't really happen that way. Can you elaborate a bit on what you're trying to do? Hope this helps.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us