| Author |
 Topic  |
|
|
Pete_N
Posting Yak Master
117 Posts |
 Posted - 03/19/2012 : 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 |
|
|
Pete_N
Posting Yak Master
117 Posts |
Posted - 03/19/2012 : 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
117 Posts |
Posted - 03/19/2012 : 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 |
|
| |
Topic |
|
|
|
Last Record ?
