How to call Record # in Select SQL

[Ashish Kanoongo]

Is ther any way to call record# in select sql sql?

 

[Guest]

If you have a primary key then

"Select From MyTable Where MyPrimaryKey = KeyIAmLookingFor"
Is ther any way to call record# in select sql sql?

 

[Ashish Kanoongo]

No I am not talking about this. I am talking about as followed

select a.name,b.add1,recn() from a.master,b.address where a.name=b.name

It works well in foxpro, same I want in MS access or VB Application

<tt at home> wrote in message If you have a primary key then

"Select From MyTable Where MyPrimaryKey = KeyIAmLookingFor"
Is ther any way to call record# in select sql sql?

 

[Guest]

You will have to expand on what the use of Recn() is it is to get a certain number of Records you can use "SELECT TOP XXX" where XXX is the number of records you reuqire the rest of your query look alright for access althogh you may need to use a Join statement.

Cheers

Tom
No I am not talking about this. I am talking about as followed

select a.name,b.add1,recn() from a.master,b.address where a.name=b.name

It works well in foxpro, same I want in MS access or VB Application

<tt at home> wrote in message If you have a primary key then

"Select From MyTable Where MyPrimaryKey = KeyIAmLookingFor"
Is ther any way to call record# in select sql sql?

 

[Marshall Barton]

Is ther any way to call record# in select sql sql?

No. Records are not assigned a record number. They are
just tossed into the table in whatever order makes sense to
whatever database engine you're using.

 

[Jeff Johnson [MVP: VB]]

Is ther any way to call record# in select sql sql?

SQL has absolutely no concept of record number. It's set-based.

 

[Douglas Marquardt]

HI Ashish:

Depends on which dbms you are using. Oracle returns rownum, however SQL Server does not
support (or any similar) feature. I am pretty sure that some of the SQL Server MVPs do have workarounds
to simulate the Oracle rownum feature -- so you may want to post your questions in one of the
sql server newsgroups.

In any case, if you are using ADO then the recordset has an AbsolutePosition property; maybe that
will give you the functionality you are looking for.

Perhaps if you explained why you need a recnum then other suggestions could be given.

Hope this helps,

Doug.



Is ther any way to call record# in select sql sql?

 

[Joe \Nuke Me Xemu\ Foster]

No I am not talking about this. I am talking about as followed

select a.name,b.add1,recn() from a.master,b.address where a.name=b.name

It works well in foxpro, same I want in MS access or VB Application

DAO has the .AbsolutePosition property, but it doesn't show up as
a field within the recordset. Anyway, most modern database engines
seem happier with the INNER/LEFT/RIGHT JOIN syntax for specifying
joins:

select a.name, b.add1
from master a inner join address b on a.name = b.name
where ...