fetching unique pins...

Hi,

I have a table which contains a bunch of prepaid PINs. What is the
best way to fetch a unique pin from the table in a high-traffic
environment with lots of concurrent requests?

For example, my PINs table might look like this and contain thousands
of records:

ID PIN ACQUIRED_BY
DATE_ACQUIRED
...
100 1864678198
101 7862517189
102 6356178381
...

10 users request a pin at the same time. What is the easiest/best way
to ensure that the 10 users will get 10 different unacquired pins?

Thanks for any help...Bobus wrote:
> Hi,
> I have a table which contains a bunch of prepaid PINs. What is the
> best way to fetch a unique pin from the table in a high-traffic
> environment with lots of concurrent requests?
> For example, my PINs table might look like this and contain thousands
> of records:
> ID PIN ACQUIRED_BY
> DATE_ACQUIRED
> ...
> 100 1864678198
> 101 7862517189
> 102 6356178381
> ...
> 10 users request a pin at the same time. What is the easiest/best way
> to ensure that the 10 users will get 10 different unacquired pins?

Place a Primary Key or Unique constraint on the PIN column. When a
duplicate error occurs generate a new PIN & try to save the new user row
again. Repeate until success.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Thanks, however, we do not generate the PINs ourselves. We simply
maintain the inventory of PINs which are given to us from a 3rd party.

Is there a way in SQL to update a single row ala the LIMIT function in
MYSQL? Something like:
update tablename set foo = bar limit 1|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

Unless you're only using the PIN for a one-time operation - somewhere
you are going to save that PIN (in a table). That table is where you'd
put the Primary Key/Unique constraint.

I don't know what the LIMIT function does. If you want to just update
one row you'd indicate which row in the WHERE clause:

UPDATE table_name SET foo = bar WHERE foo_id = 25

foo_id would be a unique value.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/GTdYechKqOuFEgEQLJ/wCgxLHQiPaeDWXwsi5BxBpg6tlKmFoAn0tv
KM3PLa2qdl2KzW3Lp/XFHbiv
=gfzL
--END PGP SIGNATURE--

Bobus wrote:
> Thanks, however, we do not generate the PINs ourselves. We simply
> maintain the inventory of PINs which are given to us from a 3rd party.
> Is there a way in SQL to update a single row ala the LIMIT function in
> MYSQL? Something like:
> update tablename set foo = bar limit 1|||Maybe this

select top 1 ID, PIN from pin_table where acquired_by = <not acquired
value> (NOTE: this could be expensive if you use null to signify Not
Acquired, perhaps a non-null value with an index would help).

update pin_table set acquired_by = <acquired value> where ID = <ID from
select
commit

--or --

set up one table containing the unused pins and one containing the used
pins

then
select top 1 ID, PIN from unused_pin
insert into used_pin values (ID, PIN)
delete from unused_pin where ID = ID

commit|||I successfully used a transactional message queue for a similar
scenario.

Besides, try this:

create table #pins(id int identity, PIN decimal(10));
insert into #pins(PIN)values(1000000000);
insert into #pins(PIN)values(1000000001);
insert into #pins(PIN)values(1000000002);
go
create table #point_to_pins(id int identity)
go
--to get a PIN
insert into #point_to_pins default values
select @.@.identity

use @.@.identity to get the PIN, you will not get any collisions ever|||On 13 Feb 2006 20:00:07 -0800, Bobus wrote:

>Hi,
>I have a table which contains a bunch of prepaid PINs. What is the
>best way to fetch a unique pin from the table in a high-traffic
>environment with lots of concurrent requests?
>For example, my PINs table might look like this and contain thousands
>of records:
> ID PIN ACQUIRED_BY
>DATE_ACQUIRED
> ...
> 100 1864678198
> 101 7862517189
> 102 6356178381
> ...
>10 users request a pin at the same time. What is the easiest/best way
>to ensure that the 10 users will get 10 different unacquired pins?
>Thanks for any help...

Hi Bobus,

To get just one row, you can use TOP 1. Add an ORDER BY if you want to
make it determinate; without ORDER BY, you'll get one row, but there's
no way to predict which one.

If you expect high concurrency, you'll have to use the UPDLOCK to make
sure that the row gets locked when you read it, because otherwise a
second transaction might read the same row before the first can update
it to mark it acquired.

If you also don't want to hamper concurrency, add the READPAST locking
hint to allow SQL Server to skip over locked rows instead of waiting
until the lock is lifted. This is great if you need one row but don't
care which row is returned. But if you need to return the "first" row in
the queue, you can't use this (after all, the transaction that has the
lock might fail and rollback; if you had skipped it, you'd be processing
the "second" available instead of the first). In that case, you'll have
to live with waiting for the lock to be released - make sure that the
transaction is as short as possible!!

So to sum it up: to get "one row, just one, don't care which", use:

BEGIN TRANSACTION
SELECT TOP 1
@.ID = ID,
@.Pin = Pin
FROM PinsTable WITH (UPDLOCK, READPAST)
WHERE Acquired_By IS NULL
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @.User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @.ID
-- Add error handling
COMMIT TRANSACTION

And to get "first row in line", use:

BEGIN TRANSACTION
SELECT TOP 1
@.ID = ID,
@.Pin = Pin
FROM PinsTable WITH (UPDLOCK)
WHERE Acquired_By IS NULL
ORDER BY Fill in the blanks
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @.User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @.ID
-- Add error handling
COMMIT TRANSACTION

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:

> BEGIN TRANSACTION
> SELECT TOP 1
> @.ID = ID,
> @.Pin = Pin
> FROM PinsTable WITH (UPDLOCK, READPAST)
> WHERE Acquired_By IS NULL
> -- Add error handling
> UPDATE PinsTable
> SET Acquired_By = @.User,
> Date_Acquired = CURRENT_TIMESTAMP
> WHERE ID = @.ID
> -- Add error handling
> COMMIT TRANSACTION
> And to get "first row in line", use:
> BEGIN TRANSACTION
> SELECT TOP 1
> @.ID = ID,
> @.Pin = Pin
> FROM PinsTable WITH (UPDLOCK)
> WHERE Acquired_By IS NULL
> ORDER BY Fill in the blanks
> -- Add error handling
> UPDATE PinsTable
> SET Acquired_By = @.User,
> Date_Acquired = CURRENT_TIMESTAMP
> WHERE ID = @.ID
> -- Add error handling
> COMMIT TRANSACTION

Yet a variation is:

SET ROWCOUNT 1
UPDATE PinsTabel
SET @.ID = ID,
@.Pin = Pin
WHERE Acquired_By IS NULL
SET ROWCOUNT 0

It is essential to have a (clustered) index on Acquired_By.

Which solution that gives best performance it's difficult to tell.
My solution looks shorted, but Hugo's may be more effective.

Note also that if there is a requirement that a PIN must actually
be used, the transaction scope may need have to be longer, so in
case of an error, there can be a rollback. That will not be good
for concurrency, though.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the responses everyone!

MGFoster: I was asking about the "limit" clause so that I could
implement a solution similar to what Erland recommended. This
guarantees no collisions.

Randy: in your solution, I believe there is a chance that two
concurrent requests will end up grabbing the same pin.

Alexander: clever! It's like an Oracle sequence. But, in our
particular case, we could have a problem of unused pins for
transactions which rollback.

Hugo: that should definintely do the trick.

Erland: yours too! I will try them both out.

Thanks for the help!|||Bobus,

When I was solving a similar problem, I did try out the approaches
suggested by Hugo and Erland. I hate to say that, but I was always
getting a bottleneck because of lock contention on PinsTable. Maybe I
was missing something at that time. I had a requirement to produce
hundreds of PINs per minute at peak times, so I decided to allocate a
batch of PINs at a time, instead of distrributing them one at a time -
that took care of lock contention|||Alexander Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:
> When I was solving a similar problem, I did try out the approaches
> suggested by Hugo and Erland. I hate to say that, but I was always
> getting a bottleneck because of lock contention on PinsTable. Maybe I
> was missing something at that time. I had a requirement to produce
> hundreds of PINs per minute at peak times, so I decided to allocate a
> batch of PINs at a time, instead of distrributing them one at a time -
> that took care of lock contention

Bobus said "But, in our particular case, we could have a problem of unused
pins for transactions which rollback."

This would call for a design where you get a start a transaction, get a
pin, use it for whatever purpose, and then commit. But as you say, you
will get contentions on the PINs here, although it's possible that READPAST
hint could help. I did some quick tests, and it seem to work.

The other option as you say is just to grab a PIN or even a bunch of
them. A later job would then examine which PINs that were taken, and
which were never used, and then mark the latter as unused.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Alexander/Erland: thanks for the tips. I will let you know what issues
we run into, if any.

Best wishes.|||Erland,

I think you are right. I looked up that project, and in fact I did not
try READPAST at all. Having observed poor performance, I implemented
batches, which reduced amount of database calls and as a side effect
took care of lock contention. I immediately got good performance and
did not drill down any furhter.|||Erland's solution seems to work great from our initial tests!

Unrelated question, and probably should be another thread, but have any
of you SQL Server geniuses tried PostgreSQL? Any comments, positive or
negative?|||In 25 words or less: It is nice but has the feel of a college project
where grad students kept addinf things to it based on the last academic
fad or thesis topic. I would go with Ingres, which has a "commercial
feel" and a great optimizer.

Fetching Record From Table

Hi,
I am facing a peculier problem. I have 3 records in my table. when i am trying to fetch the top 2 its able to fetch the records. but when trying fro moire than 2 (say top 3 or just the *) it say time out. can any one help me out in this regard.

Below is the error that I get when tryong to open the record from SSMS

SQL Execution Error.

Executed SQL statement: SELECT Transaction_ID, tril_gid, WorkGroup_ID, CreatedBy, CreatedDate, ModifiedBy, ModifiedDate, LCID FROM Symp_TransactionHeader
Error Source: .Net SqlClient Data Provider
Error Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
--------
OK Help
--------

Thanks,
Rahul Jhawhere's the TOP in that query?|||there is no top in the query......... the system wil generate the query when you try to open the table from ssms...........

Fetching of data

hi,

I would like to know that, I have three instances of the same database at three different servers and I am trying to fetch the data from the select query. "select * from table_name"

I would like to know, whether the order of rows fetched by this query will be different on different servers of sql server or the same order of rows will be fetched.

For me the output is coming different on each server database with he same query . Pls let me know, is there any default order by or it takes it randomly.

Thanks

Gaurav Gupta

By spec, the order of rows are undetermined unless you specify an order by clause. Even multiple identical queries to the same server can return rows in different orders. It may or may not occur, but the order is not guaranteed unless you ask for the data that way.

Fetching data from IBM DB2 to SQL Server 2005

Hi,

I am trying to fetch data from IBM DB2 to SQL Server 2005.

The problem I am facing is when I create the OLE DB Connection (I am using the "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider") and see the "Preview", I get "System.Byte[]" in a couple of columns for all the rows, instead of the actual data.

The datatype of the original field is "Byte Stream".

I have tried all options, but, failed. I believe there is something in the "Force Translate" property of the OLE DB Connection. Right now it is set to "65535". I am not sure if that needs to be changed.

I was earlier using a DTS package, where I used ODBC for connecting to the same database. In ODBC, there is a "Translation" tab where there is a check box labelled: "Convert binary text (CCSID 65535) to text".

When I check this box, I am able to see the data correctly.

But, now I have moved to SSIS and I am facing the same problem as I am not using the ODBC connection.

Please help.

Thanks and Regards,

B@.ns

I don't knot if this would resolve your problem; but you can still use the ODBC connection in SSIS by using a Data Reader source component.|||

Hi,

Thank you for the reply.

I will try and use the same ODBC connection and get back.

Thanks and Regards,

B@.ns

|||

Thank you Rafael!

This has indeed solved my problem.

Thanks and Regards,

B@.ns

|||

If you still having problems.

1. You can try to create linkserver

2. Create a view of the target table

3. Access it like a SQL server table

Fetching Data from a web service

Hello All,

I have to fetch data in an ssis package from a set of web services. what is the best way of doing this?

The web services are session based, this means that multiple calls are needed to complete one operation. like one to log on, second onwards to execute calls, and the last one to log out.

(there is some cookie management also required to logon successfully into the web services).

Should we write a custom task which will fetch the data for us? Or just write a C# component which is invoked from SSIS?

Regards,

Abhishek.

MSDN Student wrote:

Hello All,

I have to fetch data in an ssis package from a set of web services. what is the best way of doing this?

The web services are session based, this means that multiple calls are needed to complete one operation. like one to log on, second onwards to execute calls, and the last one to log out.

(there is some cookie management also required to logon successfully into the web services).

Should we write a custom task which will fetch the data for us? Or just write a C# component which is invoked from SSIS?

Regards,

Abhishek.

Its your decision. If its something that will need to be done in many packages then a custom component is the way to go. If its just for this package, calling from the script task will be adequate.

-Jamie

Fetch within a fetch

Is it possible to have fetch within a fetch? I am getting this error message "A cursor with the name 'crImgGrp' does not exist." So i separate the process into two stored procedures?

CREATE PROCEDURE TrigSendPreNewIMAlertP2
@.REID int

AS

Declare @.RRID int
Declare @.ITID int

Declare @.intIMEmail varchar(300)

Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@.RRID

Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @.RRID
open crImpGrp
fetch next from crImgGrp
into
@.ITID
while @.@.fetch_status = 0

EXEC TrigSendNewIMAlertP2 @.ITID

FETCH NEXT FROM crImpGrp
into
@.ITID

close crImpGrp
deallocate crImpGrp

while @.@.fetch_status = 0

FETCH NEXT FROM crReqRec
into
@.RRID

close crReqRec
deallocate crReqRec
GO... I'd re-think a different solution than what you want to do.

fetch the value from each cell into datatable

I'm trying this code but nothing is being displayed

SqlConnection conn =newSqlConnection(ConfigurationManager.AppSettings["STRING_CON"]);

SqlDataAdapter da =newSqlDataAdapter("my_sproc", conn);

DataTable dt =newDataTable();

DataRow dr;

//Adding the columns to the datatable

dt.Columns.Add("CategoryIdIn");

dt.Columns.Add("CategoryNameVc");

foreach (DataGridItem itemin gd_freq.Items)

{

dr = dt.NewRow();

dr[0] = item.Cells[0].Text;

dr[1] = item.Cells[1].Text;

dt.Rows.Add(dr);

}//ForEach

da.Fill(dt);

gd_freq.DataSource = dt;

gd_freq.DataBind();

I am not clear what you are trying to do here.

Do you want to populate the datagrid by binding it to the datatable returned by my_sproc?

"fetch the value from each cell into datatable"

Where did the values in the cells come from?

Can you explain a bit more?

|||

Hi Robert,

I'm also a little lost after reading your posted code. It seems that first you filled a table by fetching the data from a data grid and then you called a stored procedure and updated that table with data returned. Note here that all the data you got from the data grid will be overridden now. So, what exactly are you going to do?

If you just want to fetch the data from a data grid and then update the data source, you can try to use da.Update(dt) in your code (instead of da.Fill);

And if you want to fill the data grid with data from the database, first, you can filldt by executing da.Fill(dt), then you can set gd_freq.datasource=dt. Finally call gd_freq.databind and by now you will have your data grid well populated.

Hope my suggestion could help

Thanks.

Fetch the Report - Server Directory (and Items) - Structure programmaticaly in C-Sharp

Good afternoon
Is it possible to fetch the Report - Server Directory (and Items) -
Structure programmaticaly in C-Sharp for the specific user? (to represent in
a Menuetree)
Can someone point me a possible way?
thanks in advance
wishes RalphHere is some code I use to return a 'List' I myself will be adding the
hierarchy soon. Perhaps this will help:
public ETReportItems GetReports()
{
//Not really of much use in our initial release, this method is intended
to provide a 'list' of all reports
//available to a consumer. At this point, it just returns a list of all
reports on the server. We need to
//decide on a folder hierarchy that will be constrasted again the
ETPrincipal object, which will return only
//those reports visible to a given consumer.
CatalogItem[] reportServerItems = null;
ETReportItems reportItems = new ETReportItems();
SearchCondition any = new SearchCondition();
any.ConditionSpecified = false; //Causes a search for all items
any.Name = "Name"; //We could search by a number of items, any one is fine
SearchCondition[] allItems = new SearchCondition[1];
allItems[0] = any;
try
{
reportServerItems = reportingService.FindItems(ReportServicesRoot,
BooleanOperatorEnum.Or, allItems);
}
catch (SoapException e)
{
//TODO: How are we supposed to report exceptions?
throw e;
}
//Now we will determine which of these are reports
if (reportServerItems != null)
{
//We have at least one report server item
ETReportItem reportItem = null;
foreach (CatalogItem catalogItem in reportServerItems)
{
if (catalogItem.Type == ItemTypeEnum.Report && !catalogItem.Hidden)
{
//This item happens to be a report
//TODO: we still need to implement authorization, filtering of
subreports (if we can't insure they will never be used)
//and add additional properties we want to return
reportItem = new ETReportItem();
reportItem.description = catalogItem.Description;
reportItem.id = catalogItem.ID;
reportItem.name = catalogItem.Name;
reportItem.path = catalogItem.Path;
reportItems.AddItem(reportItem);
}
}
}
return reportItems;
}
"Ralph Hüttenmoser" wrote:
> Good afternoon
> Is it possible to fetch the Report - Server Directory (and Items) -
> Structure programmaticaly in C-Sharp for the specific user? (to represent in
> a Menuetree)
> Can someone point me a possible way?
> thanks in advance
> wishes Ralph
>
>
>|||Thank you Herman|||Hey Herman
what do you mean about "ETReportItems"
thanks Ralph|||ETReports is just my type (a collection) of report items as opposed to
Microsoft's (which is an array) CatalogItem[].
"Ralph Hüttenmoser" wrote:
> Hey Herman
> what do you mean about "ETReportItems"
> thanks Ralph
>
>|||thanks Herman

Fetch text from HTML [URGENT]

Hello everybody,

I have data in my sql server table that is entered with a rich text editor so it contains html formatting. I need to show this data on the form with the formatting user has specified while entering the data. On the crystal report, I need to show the data not in the html format but simple text. I created a stored procedure to fetch the data and binded the report with the stored procedure. When data is printed on the report, it shows HTML tags with the data. Is there a way to fetch text in SQL so that report will only show text without HTML formatting?

Thanks.

It is stored a just a string of characters. It is retreived as just a string of characters. (Some of those characters are html coding characters.

You may wish to create a User Defined Function (UDF) that will strip out the html characters and can be used when necessary.

Here is an example of how you might create and use such a function. (It is incomplete -I'm sure you will be able to add the additional html coding...)

CREATE FUNCTION dbo.StripHTML
( @.StringIn varchar(500) )
RETURNS varchar(500)
AS
BEGIN
SET @.StringIn = replace( replace( @.StringIn, '

', '' ), '

', '' )
SET @.StringIn = replace( replace( @.StringIn, '<b>', '' ), '</b>', '' )
-- add addition html codes here
RETURN ( @.StringIn )
END
GO

SELECT dbo.StripHTML('

This is a <b>Test</b>

')

|||

I was looking for a quicker and easier way to do it (if there is something). As you said, I am writing my own UDF to parse HTML.

Thanks a lot.

|||

You can use the following code to replace all the HTML tags....

CREATE Function dbo.Html2Text(@.HtmlString As Varchar(8000))
Returns Varchar(8000)
as
Begin
Declare @.TagString As Varchar(8000)
Declare @.TagStart As int
Declare @.TagEnd As int

Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
While @.TagStart <> 0 And @.TagEnd <> 0 And @.TagEnd > @.TagStart
Begin
Select @.TagString = Substring(@.HtmlString, @.TagStart, @.TagEnd - @.TagStart + 1)
Select @.HtmlString = Replace(@.HtmlString, @.TagString, '')
Select @.TagStart = CharIndex('<', @.HtmlString),
@.TagEnd = CharIndex('>', @.HtmlString)
End

Select @.HtmlString = Replace(@.HtmlString, '&nbsp;', ' ')
Select @.HtmlString = Replace(@.HtmlString, '&amp;', '&')
Select @.HtmlString = Replace(@.HtmlString, '&quot;', '''')
Select @.HtmlString = Replace(@.HtmlString, '&#', '#')
Select @.HtmlString = Replace(@.HtmlString, '&lt;', '<')
Select @.HtmlString = Replace(@.HtmlString, '&gt;', '>')
Select @.HtmlString = Replace(@.HtmlString, '%20', ' ')
Select @.HtmlString = Replace(@.HtmlString, Char(10), '')
Select @.HtmlString = Replace(@.HtmlString, Char(13), '')
Select @.HtmlString = LTrim(RTrim(@.HtmlString))

Return @.HtmlString
End

Go

Select dbo.Html2Text('

Test&nbsp;Data

'); -- Result :Test Data

fetch successful resultset after exception

Ok, here is the deal. In T-SQL, an error is handled depending on its severity. For example, a severity of 15 or less (or is it 10 or less? doesn't matter) will only raise a warning that can be caught through the message event. 16 or higher (or whatever) will cause an exception to be thrown on the .NET side, and 20 or higher causes the connection to be closed.

So far so good.

Now here's my issue: I have a stored procedure that does 2 queries (inside the same SP). Sometimes, the first query will succeed, while the second one will cause an error of severity 16. Again, in .NET that will throw an exception, making me unable to fetch the first resultset (which I require for logging purpose). If the error was, let say, severity 9, I could simply subscribe to the message event to get my error, yet still be able to get the first result set. But unfortunately life isn't perfect, and the error is indeed a severity 16.

Anyone know of a way to be able to get the first result set, even if the second query (within the same SP) completly fails?Why don't you wrap the two SQL statements in a transaction, so that if one fails, all attempted changes will be rolled back. You certainly wouldn't want something to work half-way--it should be an all or nothing deal. If you don't mind it working half-way, then perhaps the SQL should be put into separate calls.|||Well, I answered my own question: nothing is stopping me from accessing the data. If using a datareader, the exception isn't thrown until you move to the resultset that errored out. If using a dataset with multiple tables, the tables that succeeded are filled just fine as normal, and the ones that failed have 0 rows. Not the behavior I expected, but it did the trick.

As to why a non-atomic process is wrapped in a single stored procedure? Its because the SP was made for a relatively limited (in features) scheduling software, and it can only call one SP on the trigger. A bit silly, but I'm not the one who set that up originally, I'm just handling a migration. The requirement here was so that we could log what succeeded so we can give the log away to a supplier or some such.

Anyway, seems like ADO.NET handles that requirement just fine after all. I'm surprised, honestly.

Fetch rows afftected in a table

Hi,
I'm pretty new to this community and here's my query. Lets suppose I have executed a DML query on a table (inserted a single row). The table is not having any identity, date or time fields....just a primary key column. I wish to retrieve the record that I or anybody else inserted last into that table. Or I might even wish to fetch all records inserted into that table in the last 10 seconds. Can anybody here help me out in this case, plz.

Quote:

Originally Posted by dev177

Hi,
I'm pretty new to this community and here's my query. Lets suppose I have executed a DML query on a table (inserted a single row). The table is not having any identity, date or time fields....just a primary key column. I wish to retrieve the record that I or anybody else inserted last into that table. Or I might even wish to fetch all records inserted into that table in the last 10 seconds. Can anybody here help me out in this case, plz.

Yah.
I got the same doubt .is there any to find the time of insertion of a particular row|||

Quote:

Originally Posted by srinit

Yah.
I got the same doubt .is there any to find the time of insertion of a particular row

You could add columns UpdatedByID and LastUpdateDate and set UpdatedByID to be the user's username and set LastUpdateDate to GETDATE() (or just make the default for LastUpdateDate to be GETDATE()).

Fetch Returning Duplicate Last Record

Ok, this thing is returning the last record twice. If I have only one record it returns it twice, multiple records gives me the last one twice. I am sure some dumb pilot error is involved, HELP!

Thanks in advance, Larry

ALTER FUNCTION dbo.TestFoodDisLikes

(

@.ResidentID int

)

RETURNS varchar(250)

AS

BEGIN

DECLARE @.RDLike varchar(50)

DECLARE @.RDLikeList varchar(250)

BEGIN

SELECT @.RDLikeList = ''

DECLARE RDLike_cursor CURSOR

LOCAL SCROLL STATIC

FOR

SELECT FoodItem

FROM tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

OPEN RDLike_cursor

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLike

WHILE @.@.FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM RDLike_cursor

INTO @.RDLike

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike

END

CLOSE RDLike_cursor

DEALLOCATE RDLike_cursor

END

RETURN @.RDLikeList

END

Your selecting into the cursor before any operation in the begin statement. ****

ALTER FUNCTION dbo.TestFoodDisLikes
(
@.ResidentID int
)
RETURNS varchar(250)
AS
BEGIN
DECLARE @.RDLike varchar(50)
DECLARE @.RDLikeList varchar(250)
BEGIN
SELECT @.RDLikeList = ''
DECLARE RDLike_cursor CURSOR
LOCAL SCROLL STATIC
FOR
SELECT FoodItem
FROM tblFoodDislikes
WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')
OPEN RDLike_cursor
FETCH NEXT FROM RDLike_cursor
INTO @.RDLike
SELECT @.RDLikeList = @.RDLike
WHILE @.@.FETCH_STATUS = 0
BEGIN --****

SELECT @.RDLikeList = @.RDLikeList + ', ' + @.RDLike--****
FETCH NEXT FROM RDLike_cursor INTO @.RDLike--****

END--****
CLOSE RDLike_cursor
DEALLOCATE RDLike_cursor
END
RETURN @.RDLikeList
END

|||

Steve,

It still duplicates one record, that just moved it to the beginning. If I am expecting to see somethimg like 'Ham, Beans, Biscuit, Apples, Beets' I get 'Ham, Ham, Beans, Biscuit, Apples, Beets'. Looks like the second part of the FETCH is starting all over again

|||Actually I would avoid using a cursor for this, according to the Northwind database it would be something like this:

DECLARE @.Cities VARCHAR(8000)

SET @.Cities = ''

SELECT @.Cities = CASE @.Cities

WHEN '' THEN City

ELSE @.Cities + ', ' + City

END

from CUstomers

Group by City

Select @.Cities

According to your problem it would be

DECLARE @.ResidentID VARCHAR(8000)

DECLARE @.FoodItems VARCHAR(8000)

SET @.FoodItems = ''

SELECT @.FoodItems =CASE @.FoodItems

WHEN '' THEN FoodItem

ELSE @.FoodItems + ', ' + FoodItem

END

from tblFoodDislikes

WHERE (ResidentID = @.ResidentID) AND (Breakfast = 'True')

Group by FoodItem

Select @.FoodItems

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens, That works great and I did use it, seems faster. I am still at a loss as to why the fetch was returning a duplicate record but I like this better.

Larry

fetch results of a cursor to a temporary DB for manipulation

Hi all

I want to put the fetch results of a cursor to a temporary DB for manipulation, Im selecting all columns from the table in the cursor and the number of total columns is unknow.

Please guide me on how this could be done...

Thanks in advance

Regards

BennyGet rid of the cursor and use a select into statement with the same query to create a table in the temp database.

If you want to perform row by row oprerations on the resultset use the identity function to create an identity on it then you can loop through that without need for a cursor.

Fetch question

i am having an endless loop after the first record... anything i missed?

ALTER PROCEDURE IMPGrpEscalationX

AS

Declare @.IMID int
Declare @.IMID2 int
Declare @.FS1 int
Declare @.FS2 int

Declare crFirst cursor local for
select IMID from ImplementationGroup where IMSTatus = 'Y'
open crFirst
fetch next from crFirst
into
@.IMID

begin
Declare crSecond cursor local for
select IMID from Employees_ImplementationGroup where Employees_ImplementationGroup.IMID = @.IMID
open crSecond
fetch next from crSecond
into
@.IMID2
set @.FS2 = @.@.fetch_status
if not exists(select IMID from Employees_ImplementationGroup where IMID = @.IMID2 and IMType = 'T')
Begin
DECLARE @.MsgText varchar(700)
DECLARE @.IMGRPNAME varchar(50)
Set @.IMGRPNAME = (select IMGrpname from ImplementationGroup where IMID = @.IMID2)
--SET @.MsgText = 'This implementation group has been without a last resort implementer for the past 24 hours. Please click here to assign a last resort implementer: http://xxxx.com/admin/implementationgrp_emps.aspx?imid=' + @.IMID2 + '&imgrpname=' + @.IMGrpName

EXEC master.dbo.xp_sendmail
@.recipients = cccc@.ccc.com',
@.Message = @.IMGRPNAME,
@.Subject = 'needs attention'

end
while @.FS2 = 0
fetch next from crSecond
into
@.IMID2
end
set @.FS1 = @.@.fetch_status
while @.FS1 = 0

FETCH NEXT FROM crFirst
into
@.IMID

close crFirst
deallocate crFirst
close crSecond
deallocate crSecondi fixed this one... second fetch was outside the scope...sql

Fetch Question

I have a stored procedure that inserts one row into a table. From a
SELECT statement I would like to call the SP on each row in the
results. Is setting up a cursor and using fetch statements the best
way (or even the only way) to do this?Hi

It is probably the safest way to do it, but without knowing exactly what you
are trying to do it is hard to suggest alternatives.

John

"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0310010701.261cbff2@.posting.google.c om...
> I have a stored procedure that inserts one row into a table. From a
> SELECT statement I would like to call the SP on each row in the
> results. Is setting up a cursor and using fetch statements the best
> way (or even the only way) to do this?

Fetch Query that triggered the TRIGGER

Hello

The problem is need to find out the querry that has updated or inserted
into the table and in turn 'Triggered the Trigger'. I have the user
name, the machine name, Application name, but not the query. The update
is not desired and the application is doing it but the application
being so large we are unable to pin-point the code which is doing the
dammage.

Pls help!

Regards
AnubhavAnubhav (anbansal@.gmail.com) writes:

Quote:

Originally Posted by

The problem is need to find out the querry that has updated or inserted
into the table and in turn 'Triggered the Trigger'. I have the user
name, the machine name, Application name, but not the query. The update
is not desired and the application is doing it but the application
being so large we are unable to pin-point the code which is doing the
dammage.

If the UPDATE is coming directly from a client, you can use

dbcc inputbuffer(@.@.spid) with tableresults

but if the error is in a stored procedure, you will not see the UPDATE
statement, only the call submitted by the client.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Fetch Loop stored procedure

What is wrong with this stored procedure? This should work right?

Create PROCEDURE UpdRequestRecordFwd
@.oldITIDint ,
@.newITID int
AS
Declare @.RRID int
Declare @.APID int
Declare crReqRec cursor for
select RRID from RequestRecords where ITID = @.oldITID
open crReqRec
fetch next from crReqRec
into
@.RRID
while @.@.fetch_status = 0
Begin
Update RequestRecords
set ITID = @.newITID
where RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO

(1) what is the error you get ? its hard toguess unless you give us complete info
(2) for what you are doing above you dont seem to need a cursor.

Update RequestRecords set ITID = @.newITID where RRID = @.RRID

would work just fine without any cursor..unless you are doing other stuff in between.

either way, you need to give more details.

hth|||(1) I am not getting any error. It's just won't update
(2) I am have multiple RRID's (unique primary key) with one ITID. I want all the RRID's with this ITID to be replaced with the new ITID returned.

I removed the fetch as you suggested... still doesn't work..

Create PROCEDURE UpdRequestRecordFwd

@.oldITIDint ,
@.newITID int

AS
Declare @.RRID int

set @.RRID = (select RRID from RequestRecords where ITID = @.oldITID and RRStatus = 'IA' and APID is null)

Update RequestRecords
set ITID = @.newITID
where RRID = @.RRID

Go|||if you just need to update ITID's then how do the RRID's come into picture...
see if this helps :

Create PROCEDURE UpdRequestRecordFwd
@.oldITID int ,
@.newITID int
AS
Update RequestRecords set ITID = @.newITID where ITID = @.oldITID
Go

hth|||I do appreciate your help but it was my mistake, permission to execute was not checked. Thank you for your time.

Fetch limited rows sequentially

Hello Friends,
I want to fetch limited rows from single query (example:-1000 rows present in emp table , I want to fetch first 10 rows at a time and store in a file. Next iteration it has to fetch next 10 rows and store in another file like this it has to create 10 different files with 10 rows)
Thanks in advance
Waiting early reply
Regds
NitinHi Nitin,

SELECT *
FROM (SELECT TOP 10 *
FROM (SELECT TOP 20 *
FROM emp
ORDER BY LName ASC) AS t1
ORDER BY LName DESC) as t2
ORDER BY LName

This select will return the 10 after the first ten from emps table. Just loop through increasing the TOP 20 to TOP 30 and so on to get each interval of ten employers.

Hope this helps! :)
Robert

Fetch last record in a table

HI ALL,
Suppose i did not use identity for generating sequence in a
table.Also there is no sequence no column or no primary key on a
column. Then how to find last record in a table. Suppose there are 10
rows. How can i fetch 10th rows record.
Define your SQL SELECT statement , and THEN use ORDER BY myCol DESC -
select TOP 1. That's assuming there are 10 records.
If there are more than 10 records , and you are using SQL 2005 , you could
do something like:
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY Col2 DESC)AS RowFROm
myTableWHERE Row = 10
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
|||On Jan 16, 2:50Xpm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched, last
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
>
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.
|||MC
SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
> First you need to define 10th. What does it mean exactly? Last fetched,
> last by some value, last..... Without primary key you basically dont have
> a consistent approach, so some kind of definition is definitely needed
> here.
>
> MC
>
> "mohit" <goenka.mohit@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
|||On Jan 16, 3:22Xpm, mohit <goenka.mo...@.gmail.com> wrote:
> HI ALL,
> X X X Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
In a set there is no such thing as last record.
|||On Jan 16, 3:22Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
>
>
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong
|||On Jan 16, 3:28Xpm, SB <othell...@.yahoo.com> wrote:
> On Jan 16, 3:22Xpm, mohit <goenka.mo...@.gmail.com> wrote:
>
> In a set there is no such thing as last record.
Can you tell what set means. i am asking from table how to fetch last
record i.e last row from a table
|||On Jan 16, 4:45Xpm, mohit <goenka.mo...@.gmail.com> wrote:
> On Jan 16, 3:28Xpm, SB <othell...@.yahoo.com> wrote:
>
>
> Can you tell what set means. i am asking from table how to fetch last
> record i.e last row from a table
A table consists of a set of records. It is not a sequence so that you
have first or last record. A set is an unordered set of records.
|||> Sorry yaar but its not working. When we do order by col_name desc
> the rows gets shuffle due to which answer is coming wrong
How do you know it is not working if there is no data in the row to identify
the order of insertion?
An important relational database concept is that a table is an unordered set
of rows. Rows may be returned in an arbitrary sequence unless you specify
ORDER BY. If you want data returned in the sequence in which rows were
inserted, you'll need an incrementing column like inserted datetime or
identity for the ordering.
Hope this helps.
Dan Guzman
SQL Server MVP
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:d4d2d6c5-c6e0-4612-9f57-13accf2ff8b1@.i7g2000prf.googlegroups.com...
On Jan 16, 3:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
>
>
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong
|||"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
As others have said, w/o a primary key, there's no such thing as a "last
record" defined.
And in fact, without an ORDER BY you can never guarantee the order you'll
return the data in.
SELECT * from FOO may in fact return a different order of results at
different times.
This could be due to what's in the memory cache at the time, if the engine
decides to parallize the query across different CPUs, etc.
Now, MOST LIKELY for 10 rows, a simple "select * from FOO" will return the
data in the order it was inserted but that's absolutely no guarantee this is
true.
You may want to google the definition of a "SET" or "TABLE" within SQL.
They have no inherent order.
So sorry to say, you probably can't get the answer to the question you seek
(at least not the way it's posed.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
sql

Fetch last record in a table

HI ALL,
Suppose i did not use identity for generating sequence in a
table.Also there is no sequence no column or no primary key on a
column. Then how to find last record in a table. Suppose there are 10
rows. How can i fetch 10th rows record.First you need to define 10th. What does it mean exactly? Last fetched, last
by some value, last..... Without primary key you basically dont have a
consistent approach, so some kind of definition is definitely needed here.
MC
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.|||Define your SQL SELECT statement , and THEN use ORDER BY myCol DESC -
select TOP 1. That's assuming there are 10 records.
If there are more than 10 records , and you are using SQL 2005 , you could
do something like:
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY Col2 DESC)AS RowFROm
myTableWHERE Row = 10
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.|||On Jan 16, 2:50=A0pm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched, la=st
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.=
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
> > HI ALL,
> > =A0 =A0 =A0Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.|||MC
SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
> First you need to define 10th. What does it mean exactly? Last fetched,
> last by some value, last..... Without primary key you basically dont have
> a consistent approach, so some kind of definition is definitely needed
> here.
>
> MC
>
> "mohit" <goenka.mohit@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>> HI ALL,
>> Suppose i did not use identity for generating sequence in a
>> table.Also there is no sequence no column or no primary key on a
>> column. Then how to find last record in a table. Suppose there are 10
>> rows. How can i fetch 10th rows record.
>|||On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> HI ALL,
> =A0 =A0 =A0 Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
In a set there is no such thing as last record.|||On Jan 16, 3:22=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
> > First you need to define 10th. What does it mean exactly? Last fetched,
> > last by some value, last..... Without primary key you basically dont ha=ve
> > a consistent approach, so some kind of definition is definitely needed
> > here.
> > MC
> > "mohit" <goenka.mo...@.gmail.com> wrote in message
> >news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> >> HI ALL,
> >> =A0 =A0 =A0Suppose i did not use identity for generating sequence in a
> >> table.Also there is no sequence no column or no primary key on a
> >> column. Then how to find last record in a table. Suppose there are 10
> >> rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong|||On Jan 16, 3:28=A0pm, SB <othell...@.yahoo.com> wrote:
> On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> > HI ALL,
> > =A0 =A0 =A0 Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.
> In a set there is no such thing as last record.
Can you tell what set means. i am asking from table how to fetch last
record i.e last row from a table|||On Jan 16, 4:45=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> On Jan 16, 3:28=A0pm, SB <othell...@.yahoo.com> wrote:
> > On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> > > HI ALL,
> > > =A0 =A0 =A0 Suppose i did not use identity for generating sequence in =a
> > > table.Also there is no sequence no column or no primary key on a
> > > column. Then how to find last record in a table. Suppose there are 10
> > > rows. How can i fetch 10th rows record.
> > In a set there is no such thing as last record.
> Can you tell what set means. i am asking from table how to fetch last
> record i.e last row from a table
A table consists of a set of records. It is not a sequence so that you
have first or last record. A set is an unordered set of records.|||If the 'last' you mean last added then you need a column which will define
sequence of insertions. Something like 'DateAdded' or something like that.
Otherwise, you need to specify how do YOU know which record is 'last' when
you open sample data from table.
MC
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:a53c73e5-4685-4d12-8f94-5e8a1b94b5b8@.f10g2000hsf.googlegroups.com...
On Jan 16, 2:50 pm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched,
> last
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
> > HI ALL,
> > Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.|||> Sorry yaar but its not working. When we do order by col_name desc
> the rows gets shuffle due to which answer is coming wrong
How do you know it is not working if there is no data in the row to identify
the order of insertion?
An important relational database concept is that a table is an unordered set
of rows. Rows may be returned in an arbitrary sequence unless you specify
ORDER BY. If you want data returned in the sequence in which rows were
inserted, you'll need an incrementing column like inserted datetime or
identity for the ordering.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:d4d2d6c5-c6e0-4612-9f57-13accf2ff8b1@.i7g2000prf.googlegroups.com...
On Jan 16, 3:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
> > First you need to define 10th. What does it mean exactly? Last fetched,
> > last by some value, last..... Without primary key you basically dont
> > have
> > a consistent approach, so some kind of definition is definitely needed
> > here.
> > MC
> > "mohit" <goenka.mo...@.gmail.com> wrote in message
> >news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> >> HI ALL,
> >> Suppose i did not use identity for generating sequence in a
> >> table.Also there is no sequence no column or no primary key on a
> >> column. Then how to find last record in a table. Suppose there are 10
> >> rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong|||"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
As others have said, w/o a primary key, there's no such thing as a "last
record" defined.
And in fact, without an ORDER BY you can never guarantee the order you'll
return the data in.
SELECT * from FOO may in fact return a different order of results at
different times.
This could be due to what's in the memory cache at the time, if the engine
decides to parallize the query across different CPUs, etc.
Now, MOST LIKELY for 10 rows, a simple "select * from FOO" will return the
data in the order it was inserted but that's absolutely no guarantee this is
true.
You may want to google the definition of a "SET" or "TABLE" within SQL.
They have no inherent order.
So sorry to say, you probably can't get the answer to the question you seek
(at least not the way it's posed.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>>
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.
If you don't have a column that can tell you what "last" means, then you
could return ANY row and how would you know it wasn't the "last" row?
In SQL Server, a table is an unordered set of rows by definition. If you
say SELECT TOP 1 columns FROM table or SELECT TOP 10 columns FROM table and
you don't include an ORDER BY clause, the optimizer is free to return
whichever rows it wants... and it can change its mind when you run the query
again 5 minutes later, giving you a different set. So, if you want to
identify your "last" row, you will need to have a column that you can use to
tell SQL Server "use this column to determine last."
A