Is this possible - 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)

Is this possible

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-09-20 : 11:18:01

BAck again to this inherited table. The table has a field called payment ID. The makers of the system that load this tablke with dats, have made the PaymentID field an Int field, but the maximum number it will hold is 999 before it starts again at 1.

My proble is getting the nest PaymentID number . I cannot use max as It will always return 999
Say for instance the table has the following

Data etc ........ PaymentID 1
etc to 999
It will then start again at 1 so i coauld end up with the following

data etc ........ PaymentID 1
data etc ........ PaymentID 1
data etc ........ PaymentID 2
data etc ........ PaymentID 3
data etc ........ PaymentID 4
data etc ........ PaymentID 5
data etc ........ PaymentID 6

ect. Is there a way of inding out what the next iD number would be .. in this case it would be 2

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 11:25:14

Does the table have any other sequential way or ordering?

Does it (hope of hopes) have an IDENTITY(1,1) PRIMARY KEY or something similar.

OR failing this is that another column that can be used to order the data.

And - - is there a primary key?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-09-20 : 11:29:36

It has a primary key of [ID] bigint

not much help i know

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 11:39:15

is that an autonumber field? How is the primary key populated?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 11:43:08

use ORDER BY and TOP to get the max value

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-09-20 : 11:44:04

quote:
Originally posted by Transact Charlie

is that an autonumber field? How is the primary key populated?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Hi Charlie,

Looking at the table, the ID field is not an Identiy field. by the looks of things the third party software increments the ID value each time a record is added.

I may not know much, but i do know if i was going there I wouldnt start from here !

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-20 : 11:44:57

use ORDER BY and TOP to get the max value

SELECT TOP 1 id FROM tblXYZ ORDER BY id desc

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 11:52:42

I don't think rohitvishwakarma has understood what you are asking for.

You are right -- this third party software is doing classic "bad stuff".

If the ID field is always increasing (sounds like it is) and whenever a new row is added it gets +1 in PaymentID field (I take it the 3rd party app can ONLY ADD ONE VALUE AT A TIME?) Then you could maybe do something like....



SELECT TOP 1
	CASE WHEN [paymentID] = 999 THEN 1 ELSE [paymentID] + 1 END AS [Next Payment ID]
FROM
	<table>
ORDER BY
	[ID] DESC

Example....



DECLARE @foo TABLE (
	[Id] INT PRIMARY KEY
	, [paymentID] INT
	)

INSERT @foo ([ID], [paymentId])
      SELECT 1, 1
UNION SELECT 2, 2
UNION SELECT 3, 3
UNION SELECT 4, 999

SELECT TOP 1
	CASE WHEN [paymentID] = 999 THEN 1 ELSE [paymentID] + 1 END AS [Next Payment ID]
FROM
	@foo
ORDER BY
	[ID] DESC

INSERT @foo ([ID], [paymenyID])
      SELECT 5, 1

SELECT TOP 1
	CASE WHEN [paymentID] = 999 THEN 1 ELSE [paymentID] + 1 END AS [Next Payment ID]
FROM
	@foo
ORDER BY
	[ID] DESC

But post your own data if this doesn't look sensible.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-09-20 : 11:53:11

quote:
Originally posted by rohitvishwakarma

use ORDER BY and TOP to get the max value

SELECT TOP 1 id FROM tblXYZ ORDER BY id desc

DO i feel so stupid right now .. Not enough sleep and missing logical

Thanks guys :)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-20 : 11:54:23

be careful. It sounds like [ID] will have nothing to do with [paymentID]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Pete_N
Posting Yak Master

181 Posts

Posted - 2010-09-20 : 11:57:44

quote:
Originally posted by Transact Charlie

I don't think rohitvishwakarma has understood what you are asking for.

You are right -- this third party software is doing classic "bad stuff".

If the ID field is always increasing (sounds like it is) and whenever a new row is added it gets +1 in PaymentID field (I take it the 3rd party app can ONLY ADD ONE VALUE AT A TIME?) Then you could maybe do something like....

SELECT TOP 1
CASE WHEN [paymentID] = 999 THEN 1 ELSE [paymentID] + 1 END AS [Next Payment ID]
FROM
<table>
ORDER BY
[ID] DESC

Example....

DECLARE @foo TABLE (
[Id] INT PRIMARY KEY
, [paymentID] INT
)

INSERT @foo ([ID], [paymentId])
SELECT 1, 1
UNION SELECT 2, 2
UNION SELECT 3, 3
UNION SELECT 4, 999

SELECT TOP 1
CASE WHEN [paymentID] = 999 THEN 1 ELSE [paymentID] + 1 END AS [Next Payment ID]
FROM
@foo
ORDER BY
[ID] DESC

INSERT @foo ([ID], [paymenyID])
SELECT 5, 1

SELECT TOP 1
CASE WHEN [paymentID] = 999 THEN 1 ELSE [paymentID] + 1 END AS [Next Payment ID]
FROM
@foo
ORDER BY
[ID] DESC

But post your own data if this doesn't look sensible.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Thank you Charlie, your example makes perfect sense, and just testing it .. looks A Ok :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources