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

Faster Query

Two quick questions,
1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
table .
2. Does anyone knows what is the equivalent command for "Instant File
Initilization"
in SQL Server 2005 for faster database / file creation, in SQL Server
2000...'
Regards.
Piku.1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are del
eted in a single
transaction.
2. There is no such thing in 2000. Don't over-use autogrow and be prepared t
hat it takes time to
create database and expand database files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
You'll usually get the best performance and keep the transaction log size
reasonable by deleting data in smaller batches. The details on how to do
this efficiently depend your actual situation (indexes, delete criteria) and
the version of SQL Server. A SQL 2005 example:
DECLARE @.RowsDeleted int
WHILE @.RowsDeleted IS NULL OR @.RowsDeleted > 0
BEGIN
DELETE TOP (1000000)
FROM dbo.MyTable
WHERE CreateDate < '20060101'
SET @.RowsDeleted = @.@.ROWCOUNT
END

> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
This is a new feature introduced in SQL 2005 and is therefore not available
in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>|||Tibor, Thx for the quick reply.
1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
about 5 hours.
here is the script I am using, please suggest me if you have something bette
r:
Use database
GO
while 1=1
begin
set rowcount 5000
DELETE from table_name
where DATE_TIME < '2006-03-01 00:00:00.000'
IF @.@.ROWCOUNT = 0
BREAK
end
set rowcount 0
2. Are you sure there is no FASTER way of creating a DB / File...' Because
it really matters, when you want to create a DB / File of about 200-300 gb.
"Tibor Karaszi" wrote:

> 1. Do it in steps, say 1000 to 10000 rows at a time, so not all rows are d
eleted in a single
> transaction.
> 2. There is no such thing in 2000. Don't over-use autogrow and be prepared
that it takes time to
> create database and expand database files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Piku" <Piku@.discussions.microsoft.com> wrote in message
> news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
>|||> 2. Are you sure there is no FASTER way of creating a DB / File...' Because[vbcol=seagreen
]
> it really matters, when you want to create a DB / File of about 200-300 gb.[/vbcol
]
That would be better I/O throughput...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...[vbcol=seagreen]
> Tibor, Thx for the quick reply.
> 1. I am deleting 5000 at a time , but for deleting 10 mil, rows it takes
> about 5 hours.
> here is the script I am using, please suggest me if you have something bet
ter:
> Use database
> GO
> while 1=1
> begin
> set rowcount 5000
> DELETE from table_name
> where DATE_TIME < '2006-03-01 00:00:00.000'
> IF @.@.ROWCOUNT = 0
> BREAK
> end
> set rowcount 0
> 2. Are you sure there is no FASTER way of creating a DB / File...' Becaus
e
> it really matters, when you want to create a DB / File of about 200-300 gb
.
>
>
> "Tibor Karaszi" wrote:
>|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:57419BC5-2016-4C80-8BC8-0621B7FCD6D1@.microsoft.com...
> 2. Are you sure there is no FASTER way of creating a DB / File...'
Because
> it really matters, when you want to create a DB / File of about 200-300
gb.
>
Not within SQL Server.
However, some SAN solutions like I believe Left Hand Network's solution will
do this at a hardware level.|||"Piku" <Piku@.discussions.microsoft.com> wrote in message
news:93258D03-424F-412E-9624-82864426F6A5@.microsoft.com...
> Two quick questions,
> 1. Pleasae post a query for FASTER deleting rows ( millions ( 10s ) from a
> table .
Well if you want to delete ALL the rows, truncate table is the answer.
Another option if you want to keep some records and the number of records is
relatively small is to select them into a new table, drop the old table and
then rename the new table to the old table name.

> 2. Does anyone knows what is the equivalent command for "Instant File
> Initilization"
> in SQL Server 2005 for faster database / file creation, in SQL Server
> 2000...'
> Regards.
> Piku.
>