Solved

getting Nth record

Posted on 2003-11-26
25
760 Views
Last Modified: 2008-03-06
i want to know the query in which i can get Nth record from the table ( data base db2/Msaccess) anything will do
0
Comment
Question by:snehal2310
  • 13
  • 4
  • 2
  • +2
25 Comments
 
LVL 30

Expert Comment

by:mayankeagle
ID: 9830352
>> ( data base db2/Msaccess) anything will do

In Oracle, you can do it using rownum.
0
 
LVL 4

Expert Comment

by:JNSTAUB
ID: 9830417
you will have to query "select * from table " and after
 use recordset.move


recordset.Move NumRecords, Start

Parameters

NumRecords A signed Long expression specifying the number of records the current record position moves.

Start Optional. A String or Variant that evaluates to a bookmark.
 You can also use one of the following BookmarkEnum values:
 adBookmarkCurrent Default. Start at the current record.
adBookmarkFirst Start at the first record.
adBookmarkLast Start at the last record.

0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 9830560
JNSTAUB,

First of all: It is not mentioned anywhere what the front-end is - whether the coding has been done in Java, VB, Pro C, or what-ever. Hence, you cannot assume that the questioner is using VB and write:

>>  use recordset.Move

(maybe that he/ she is, but we're not sure).

Secondly: It has been stated in the question that:

>> the query in which i can get Nth record from the table

means that - the query should fetch the Nth record. Not that the query will fetch all records and the code will do the part of moving to the Nth position and displaying that record. Otherwise, various approaches like recordset.Move in VB or iterating through a ResultSet or CachedRowSet in Java (until the Nth record comes - use a counter for that).

But if only the Nth record has to be fetched in the query, then such a query can be framed in Oracle using rownum as:

SELECT Field1, Field2, ...., FieldM
FROM
      (SELECT ROWNUM AS ROWNO, Field1, Field2, ...., FieldM
      FROM TableName)
WHERE ROWNO = 5 ;

// assuming that the 5th record is being retrieved.
// Replace 5 with whatever value of N you like, depending on the record which is to be obtained.

Not sure about other databases.

Mayank.
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 9830566
>> Hence, you cannot assume that the questioner is using VB

VB, ASP, or whatever. I don't know how many languages use the RecordSet object with a Move method defined in them. But one thing is for sure - the questioner has not mentioned what language he/ she is using.
0
 
LVL 4

Expert Comment

by:JNSTAUB
ID: 9830824
no but it may use access and VBA
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 9830844
Even if it does, you cannot be sure of it and comment as though it *does* use only that and nothing else. Like I repeatedly said, from what I understand (and I guess I am right), it has to be done in the query. Getting all records and doing it in the code is no big deal. Its also a bad way of doing it, both in terms of programming-practice, exception-handling, and performance.
0
 
LVL 1

Expert Comment

by:SubodhKatiyar
ID: 9842089
hey is there any field sorted on any order in this table.
0
 

Accepted Solution

by:
aschroeder1 earned 125 total points
ID: 9851274
Access does not have rownum.
This is the query, where 5 = N
Select top 1 * From (Select top 5 * from Employees) order by EmployeeID Desc
0
 
LVL 1

Expert Comment

by:victoresq
ID: 9874932
barring further clarification, and knowing that most languages have a SQL methodologies of some kind or another I offer the following.

given a definite column name of employeeNumber and you want the Xth

the SQL query is

select * -- get me everything
from nameOfTheDatabase -- the name of the table to get the data from
where employeeNumber = X --the parameter value as defined above

This will retrieve the exact employeeNumber from the Table in the database.

whodaman
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 9879862
But what if some employee numbers are missing in between and organized in any manner. EmployeeNumber might be a primary key, but that does not make it a row-number kind of thing. He does not want the record with employee-number 'N' - he wants the record in the Nth position, which can be any record.

rgds,
Mayank.
0
 
LVL 1

Expert Comment

by:victoresq
ID: 9884463
so then it really does not matter what the nth value is correct?

who would ever want the 100th record?

I would rather have Jerry Joe Jimbob who is employee #100

We need more details as to what the scenario is (as my compatriats have indicated).

Good luck

whodaman
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 30

Assisted Solution

by:mayankeagle
mayankeagle earned 125 total points
ID: 9887435
I know.... but that's what the question's requirements say. I don't care why he needs it. But just that he needs it that way. Such queries are used while seeing multiple records in pages (when the first page displays, say, the first 10 records, the second page displays the next 10 records and so on). He might be having some similar requirement where he requires exactly the Nth record. Come on! No one who knows SQL a little bit would ask you how to write: SELECT * FROM table WHERE field = N;

rgds,
Mayank.
0
 

Expert Comment

by:aschroeder1
ID: 9920672
I don't know if you were talking about the query I wrote but it definitely answers the question and I think I should get points for it.

Select top 1 * From (Select top 5 * from Employees) order by EmployeeID Desc

N is not the employeeID, it's the Nth record.  

Select top 1 * From (Select top 5 * From TableName) order by PrimaryKey Desc
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 9925784
I was answering to victoresq.
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10319011
Split between aschroeder1 and mayankeagle.
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10558696
Please close it now.
0
 
LVL 1

Expert Comment

by:victoresq
ID: 10560232
mayankeagle

Please remain professional in your remarks to anyone, opinions were given until you became abusive and pointed.

Small minds, smaller code.

I was trying to get the questioner to recongnize that he had larger things to think about than the nTH record.

I could care less for the points, I would rather have people understand the grander scope.

take care,

Vic
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10567662
'xcuse me, victoresq, there is not a single abusive word which I have mentioned in any of my comments.

Its not about points or about thinking about larger things. We all talk about larger things and about extending code, and about making it more re-usable and maintainable. Advices and suggestions can always be given but in any question-page, our main aim is to solve the specific problem which it addresses.

As fas as being professional goes, I (and for that matter, any expert) would not like it if the questioner remains silent for so many days, without PAQing the question or without coming back with any feedback or questions.

Its not about points. Its just about the attitude of the questioner, who should try to act more responsibly when there are a group of people sitting here trying to help him out.

Rgds,
Mayank.
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10567948
Also, this question just asked: "How to get Nth record?". Well, there are some cases when you would actually need to do that. The situation might demand that. And even if it does not, maybe that the questioner was just curious to know how it is done, if it can be done (extra GK, if you call it that way).

The initial question also said: >> ( data base db2/Msaccess) anything will do

Since database was not much of an issue, I provided the solution for Oracle. Later, aschroeder1 provided the solution for Access. Which is why I recommended a split between mayankeagle and aschroeder1.

Thanks and regards (and cheers ;-) ),
Mayank.
0
 
LVL 1

Expert Comment

by:victoresq
ID: 10570556

Agreed, I read your comments as being critical.

I am more, especially in classes I teach, finding that students are after the short fast answer.  

I read this in the questioners comments.  Instead of understanding the wider scope of the problem they were trying to solve.  Although they may search for whatever they want, they have to understand why they are seeking a specific record.

perhaps I may run against an app that they would have been a contributor.  This would be a much better experience if they had a broader scope instead of the pointed question they present.

good split on the points.  I agree, this site does a good job in closing cases.  This is the best that I have run across.

Thanks,

See ya,

Vic
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10570810
>> they have to understand why they are seeking a specific record.

Correct.
0
 
LVL 30

Expert Comment

by:mayankeagle
ID: 10977993
So finally it is about to be closed ;-) please proceed with that recommendation.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
matchUp  challenge 9 72
powerN  challenge 3 49
array11 challenge 16 52
SUM 2 INTEGER ARRAYS INTO 1 10 62
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This article will show, step by step, how to integrate R code into a R Sweave document
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now