How to SELECT a particular position of record in a Query?

[NinjaTurtle]

dear,

How to SELECT a particular position of record in a Query?
Example, i have 20 records, i just want the THIRD record "ONLY". How to do that?

[Eskimo]

There may be an easier way to do this, but:

queryRs = "SELECT TOP THREE columnName0, columnName1,....columnNameN FROM TableName ORDER BY columnName?"

Write a little script to give you the last outputed record. Or the first, however you set your ORDER BY.

I may have missed your point entirely, if you want this to occur stricly in a view/query, I'm not positive how to get the results you are asking for.

PS
Is "outputed" even a word?

[NinjaTurtle]

Dear,

The sql statement that u provided is not what i want, it needed extract ASP coding(is what u meant!), what i need is just a single SQL statement.... :(

PS
i think "Outputed" is not even a word, bcos OUTPUT is NOUN.
:P

[glenngv]

use rs.GetRows() (http://www.devguru.com/Technologies/ado/quickref/recordset_getrows.html) method to put all the contents of recordset into a 2D array. Looping through the array is more efficient than looping through the recordset.

[NinjaTurtle]

Dear,

emmmm... i think thru SQL statement cannot direct query the record that what i want...

[M@rco]

I am intrigued as to exactly why you would want to do this - please enlighten me!

:)

(I suspect there's a more efficient approach that you could use instead)

[NinjaTurtle]

dear,

i plan to have a contest, i will select a winner everyday with key in a number (Randomly key in) and i will get a winner from the database.

[whammy]

Selecting the third record from the recordset is indeed NOT random, which is probably why M@rco (and I) see a problem with it.

GlennGV was correct in his answer to your question - perhaps you need to ask your question differently, such as "how do I get a random winner from a database"?

:)

[NinjaTurtle]

Dear,

emm.... actually the number is not Randomly generated by system, it is from our own decision. Example:

2day is 20/1/2004.... i will list out all the applicants's name, then the project leader will mention a number between 1~1000; i will key in the number(example no. 55) into the textbox, the applicant who in the place of 55th will be the winner....

the next day... 21/1/2004...
the number is 38, the applicant who in the place of 38th will be the winner....

the next day... 22/1/2004...
the number is 9, the applicant who in the place of 9th will be the winner....
....

[Roelf]

to select the n-th record from a table sorted by a afield
n is recordnumber
theTable is the tablename
sortField is the field to use or sorting
sortDir is the direction of sort (asc or desc)
invSortDir is the opposite sortdirection (desc or asc)

select top 1 subset.* from
(
select top n *
from theTable
order by sortField sortDir
) as subset
order by sortField invSortDir;

some explanation
the inner select, selects the top n records, so the last record in this set is the record you want
the outer select, selects the first record from this recordset in inverse order so the record you want is the first record.

if you want a subset of the fields in the recordset, you can provide this subset in the inner select statement, the outer select, selects all of this fields so it matches your criteria

works in MSSQL 7.0 and above

good luck with it

[NinjaTurtle]

If i change the n value to 5, it will list out 5 records....
What i want is just "a" record for the table.

[Roelf]

Originally posted by NinjaTurtle
If i change the n value to 5, it will list out 5 records....
What i want is just "a" record for the table.
no it will not list out 5, the inner select, selects 5 records, the outer select picks the one you need. Have you tried the query????