Last Record ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Last Record ?

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-19 : 12:21:53

I have a third party SQL table that stores a filenumber in field FileID.
I need to get the last filenumber, but the problem is that the filenumber go from 1 to 999 then start at 1 again so doing max(fileID) always returns 999. I know I can get the first record by using Top (1) , but how can i get the last record

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 12:24:38

What other columns are in the table?

Do you have an ADD_TS or ADD_DT that defaults to GetDate()

Or and IDENTITY() Column?

Do you know how to post the DDL of a table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-19 : 12:31:42

Table is as follows

CREATE TABLE [dbo].[Batch](
[ID] [bigint] NOT NULL,
[BatchType] [nvarchar](255) NOT NULL,
[Version] [int] NOT NULL,
[Name] [nvarchar](100) NULL,
[LiveRecord] [bigint] NULL,
[RecordStatus] [int] NULL,
[LastModBy] [bigint] NULL,
[Operation] [int] NULL,
[ProcessingDate] [datetime] NULL,
[PaymentDate] [datetime] NULL,
[FileName] [nvarchar](260) NULL,
[Currency] [int] NULL,
[LatestSendingDate] [datetime] NULL,
[ContraReference] [nvarchar](255) NULL,
[HasMultipleAccounts] [bit] NULL,
[FileID] [int] NULL,
[SenderIdentifier] [nvarchar](255) NULL,
[BUNIdentifier] [nvarchar](255) NULL,
[Signature] [nvarchar](max) NULL,
[MustBeSigned] [bit] NULL,
[ErrorLevel] [int] NULL,
[WorkingCode] [int] NULL,
[FileSequenceNumber] [int] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

FileSequenceNumber is always a 0 or 1.
FileID goes from 1 to 999 then back to 1.

If i have 1075 records, i want to get the fileID out as 76 being the last number. ie 999, then 76. I dont know if there is a way to go by max row number ?

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-03-19 : 12:33:53

DUH ! sorted,

SELECT [FileID]
FROM [dbo].[Batch]
where ID = (SELECT MAX(ID) FROM [dbo].[Batch])

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-19 : 12:44:30

when you script out the table, you need to do it from the DB

Right Click>Tasks>Generate Scripts

Then pick the table and go to advanced and choose all the options for the table

That will give you the default, indexes, keys, ect

BUT...if I miss my guess, and would bet heavily on this, that a). This is not a DB2 Mainframe DBA who built this table, and b). There has GOT to be an IDENTITY Column on one of those int or BIGINT Columns

I'm Guessing [ID] is IDENTITY(-n,n)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources