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