How to return specific Row in sql server

Hi,
Suppose I have 100 rows in my table, I want to retrieve say row no 5(I just want any thing on row no 5), I don't want to use identity column. Is there any other way by which I can retrieve specified row.
Thanks,
Sajidin relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/|||Originally posted by r937
in relational databases, rows do not have a position -- there is no "5th row in the table" because the rows are not stored in sequence

the only way to get a specific row is to select it based on the value of one or more of its columns

if you sort the rows of the table into a specific sequence based on the values of one or more columns, then yes, you can get the 5th row

so, tell me what columns you want to sort the table on, and i'll show you how to get the 5th row in that sequence

rudy
http://r937.com/

Thanks for answering my question.

Say suppose I have Name column and i want the 5th record on name column.

Thanks
Sajid|||Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW|||Originally posted by GWilliy
Hi

U are asking how to select a row without having to specify anything unique about that row other than it's Ordinal position within the table.

Are U sure thats what U want

Are U Saying you want to select a row based on it's position within a table?

Ooops r937 U posted whilst I was Posting - I'll Post anyway

GW

Yes I need the same.|||select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy|||Originally posted by r937
select top 1
from (
select top 5
Name
from yourtable
order
by Name
)
order
by Name desc

rudy

Thanks, It works.

If suppose i want to retrieve all columns, then do I need to specific each column.|||U could actualy do a sraight select into a cursor & then go straight to the Row

DECLARE @.a VarChar(20),@.b,@.c

DECLARE myCursor CURSOR SCROLL
FOR
SELECT a,b,c FROM myTable
OPEN myCursor
FETCH ABSOLUTE 5 INTO @.a,@.b,@.c
BEGIN
PRINT @.a
PRINT @.b
PRINT @.c
END
CLOSE myCursor
DEALLOCATE myCursor

example Code to give U an Idea - There are many options available when declaring Cursors etc.

May be more suitable for whatever Sajidrep is Doing

GW

How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren

How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren

How to retrieve identity in stored procedure for updatable subscription??

We have transactional replication setup between SQL Server 2000 on Win2003
and MSDE on Win2003 (both SP3a). SQL Server is the publisher and is pushing
an updatable subscription (queued updating) to the MSDE instance.
We are trying to insert master and detail rows into a pair of tables via a
stored proc. After inserting the master row, doing @.@.IDENTITY to retrieve
the ID for the child row's foreign key returns NULL, causing the child row
insert to fail. Looking at the table after running the proc, the parent row
does have a valid identity value.
Any suggestions on how to make this work?
Thanks,
Steve Klein
Steve,
I'd be interested in what DBCC CHECKIDENT (tablename) returns (on the
publisher and the subscriber).
Also, presumably this insert was done on the subscriber?
Do you have just queued or immediate updating subscribers with queued
failover?
Are there any other triggers on the table (apart from the replication ones
if we are talking about the subscriber)?
Regards,
Paul Ibison
|||I have tried to repro this and it does work. Can you post the problem proc
for us?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hbbbf92amjt6a@.corp.supernews.com...
> We have transactional replication setup between SQL Server 2000 on Win2003
> and MSDE on Win2003 (both SP3a). SQL Server is the publisher and is
pushing
> an updatable subscription (queued updating) to the MSDE instance.
> We are trying to insert master and detail rows into a pair of tables via a
> stored proc. After inserting the master row, doing @.@.IDENTITY to retrieve
> the ID for the child row's foreign key returns NULL, causing the child row
> insert to fail. Looking at the table after running the proc, the parent
row
> does have a valid identity value.
> Any suggestions on how to make this work?
> Thanks,
> Steve Klein
>
|||Paul--
My apologies for not sending the following reply to the group. That was my
goal...
Paul--
Thanks for the response. DBCC CHECKIDENT shows no problems (publisher
and subscriber).
We don't have triggers (other than those for replication) on the tables and
there are not any other transactions affecting the transaction in our
distributed database. The test insert was done via a stored procedure on
the subscriber. This
stored procedure does the following:
1) Insert into table A
2) set @.x = @.@.IDENTITY
3) Insert into table B (using @.x as foreign key back to table A)
We have replication configured for queued updating.
Further info -- if we use IDENT_CURRENT('table_name') instead of
@.@.IDENTITY,
we do get an ID back, but IDENT_CURRENT has (claimed) global scope.
--Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uTduWcTfEHA.2848@.TK2MSFTNGP10.phx.gbl...
> Steve,
> I'd be interested in what DBCC CHECKIDENT (tablename) returns (on the
> publisher and the subscriber).
> Also, presumably this insert was done on the subscriber?
> Do you have just queued or immediate updating subscribers with queued
> failover?
> Are there any other triggers on the table (apart from the replication ones
> if we are talking about the subscriber)?
> Regards,
> Paul Ibison
>
|||Hilary--
I'll be able to post the procedure later today. Thanks.
--Steve
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I have tried to repro this and it does work. Can you post the problem proc
> for us?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve Klein" <sklein@.singular.com> wrote in message
> news:10hbbbf92amjt6a@.corp.supernews.com...
Win2003[vbcol=seagreen]
> pushing
a[vbcol=seagreen]
retrieve[vbcol=seagreen]
row
> row
>
|||Steve,
there's something strange here, but I'd check to see if Scope_Identity()
also returns NULL.
Finally, I'll keep an eye on your thread with Hilary where you mentioned
posting up the script, because I'd also like to try to reproduce this.
Regards,
Paul Ibison
|||Paul--
Thanks very much for your interest. I (sheepishly) need to report that the
problem was a form of user error. The table causing the problem had a text
column. The way that the error management was implemented was evidently
causing the @.@.IDENTITY to return NULL.
Why Ident_Current() worked is odd, but since removing the text column caused
the @.@.IDENTITY to work, we are not too inclined to look much further
By the way, we have changed to Scope_Identity() rather than @.@.IDENTITY.
Thanks again.
--Steve
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uGr2g3qfEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Steve,
> there's something strange here, but I'd check to see if Scope_Identity()
> also returns NULL.
> Finally, I'll keep an eye on your thread with Hilary where you mentioned
> posting up the script, because I'd also like to try to reproduce this.
> Regards,
> Paul Ibison
>
|||Hilary--
Thanks for your help. As I indicated in the response to Paul's last post,
this was a stored procedure error, which I would label "user error"... Check
there for more details.
--Steve
"Steve Klein" <sklein@.singular.com> wrote in message
news:10hg0lla3jcmu8f@.corp.supernews.com...[vbcol=seagreen]
> Hilary--
> I'll be able to post the procedure later today. Thanks.
> --Steve
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23hrM6ggfEHA.2028@.tk2msftngp13.phx.gbl...
proc[vbcol=seagreen]
> Win2003
via[vbcol=seagreen]
> a
> retrieve
> row
parent
>
|||Steve,
many thanks for the update.
Cheers,
Paul

How to retrieve Data from SQL Server?

Let us suppose that there are 900 rows in One table, and that table contains neither primary key, nor Identity column.

How can I retrieve all the roows from 201 to 250?(like the middle limits)

In oracle there is a property called RowID, but is there any such item in SQL server?

Nope. All tables should have a primary key. If there isn't one, make one. If you can't make one, then you have a data design issue you need to fix.

How to Re-Structure Form After adding an Identity

I'd created a dataform from a table stored in a SQL database, after that I changed the primary key field to be an identity, refreshed the dataset but when I execute the program, it raises an exception when I try to save the record after adding a new one asking me for the value of this field. What can I do to be sure it will be created automatically?

Move the thread here in order to get more helpful answers.

|||

Hi Juan,

I'm not sure why this got moved here, it seems like a problem with the DataSet definition...

It would help to have the actual error you're getting, but my guess is that the field you added didn't acutally get the Identity proprty set correctly, so it is not generating new values automatically. When you try to add a new record, there is no value specified for your PK so you get the error. Verify that the field works correctly outside of your DataSet, if it doesn't, then you have a problem with the table, if it does, then there is something wrong with your DataSet.

Mike

|||Ok. I've got the answer. After change the datatype in the table as IDENTITY, and refresh the dataset, the identity atribute in the dataset field does not change automatically. Thanks Mike