getting Nth record

i want to know the query in which i can get Nth record from the table ( data base db2/Msaccess) anything will do
snehal2310Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mayank SAssociate Director - Product EngineeringCommented:
>> ( data base db2/Msaccess) anything will do

In Oracle, you can do it using rownum.
0
JNSTAUBCommented:
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
Mayank SAssociate Director - Product EngineeringCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mayank SAssociate Director - Product EngineeringCommented:
>> 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
JNSTAUBCommented:
no but it may use access and VBA
0
Mayank SAssociate Director - Product EngineeringCommented:
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
SubodhKatiyarCommented:
hey is there any field sorted on any order in this table.
0
aschroeder1Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
victoresqCommented:
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
Mayank SAssociate Director - Product EngineeringCommented:
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
victoresqCommented:
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
Mayank SAssociate Director - Product EngineeringCommented:
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
aschroeder1Commented:
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
Mayank SAssociate Director - Product EngineeringCommented:
I was answering to victoresq.
0
Mayank SAssociate Director - Product EngineeringCommented:
Split between aschroeder1 and mayankeagle.
0
Mayank SAssociate Director - Product EngineeringCommented:
Please close it now.
0
victoresqCommented:
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
Mayank SAssociate Director - Product EngineeringCommented:
'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
Mayank SAssociate Director - Product EngineeringCommented:
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
victoresqCommented:

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
Mayank SAssociate Director - Product EngineeringCommented:
>> they have to understand why they are seeking a specific record.

Correct.
0
Mayank SAssociate Director - Product EngineeringCommented:
So finally it is about to be closed ;-) please proceed with that recommendation.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.