Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 3rd, 2006, 11:09 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default Retrieve last record

Is there a way in transact sql to only acquire the last record in my returned dataset?

Here is my select statement:

SELECT NXTACT
    FROM INVPOCS_POI_NXTACT N, INVPOCS_POI P
    WHERE N.FKEY = P.PKEY
    AND P.REQUSTNNUM = '1951225'
    AND P.ENTITYCODE = 'HAR';

This resultset has 3 rows returned and I want to get at the last row.

Any help or direction would be appreciated.

Thanks.

 
Old March 3rd, 2006, 11:14 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

What do you mean by "last"?

The concept of "last" implies some sort of ordering. Absent any ORDER BY clause the system is free to deliver the rows of a resultset in any old order it wants to.

If you want a resultset to be ordered, then specify an ORDER BY clause, then the MAX value of the ordering column(s) will guide you to the row that has the maximum value as per your ordering instructions...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old March 3rd, 2006, 11:17 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

The last record means nothing in database/table/set theory. The database can return the records in any order unless you provide an ORDER BY clause. Most database systems however are consistent.
If you supply an ORDER BY column then you can use the TOP specifier to get the record you need. For example if P.PKey are ascending values then:
Code:
SELECT TOP 1 NXTACT
    FROM INVPOCS_POI_NXTACT N, INVPOCS_POI P
    WHERE N.FKEY = P.PKEY
    AND P.REQUSTNNUM = '1951225'
    AND P.ENTITYCODE = 'HAR'
    ORDER BY P.PKEY DESC
will order them in reverse order and then return just the top row.

--

Joe (Microsoft MVP - XML)
 
Old March 3rd, 2006, 11:51 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Arlington, TX, USA.
Posts: 119
Thanks: 0
Thanked 1 Time in 1 Post
Default

Ok. Thanks for the clarification and the help.
 
Old March 3rd, 2006, 12:43 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Jeff,

Thanks for the post. Good to see you here.

Richard





Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Record & retrieve ID gundulf ADO.NET 5 July 12th, 2007 10:03 PM
How to retrieve ID field of newly-created record Steve777 Classic ASP Professional 3 October 2nd, 2006 11:27 AM
Record locking - user needs the next queued record cbtoolkit SQL Server 2000 0 December 6th, 2004 08:29 AM
Ani 1 noe how to retrieve record from select max() babymeizi Pro JSP 2 October 14th, 2004 05:11 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.