Select max query condition - 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 2005 Forums

Transact-SQL (2005)

Select max query condition

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2009-12-03 : 11:03:31

is this the right way to get the maxid to set the value to this: @TempSeqNO

For some reason i executed sql procedure, it did'nt get the right maxid's to begin with. Now i will have to readjust everything back.


DECLARE @SequenceNO	real		
Declare @TempSeqNO 	real


SET @TempSeqNO = (SELECT MAX(SequenceNO) FROM TAB_orders
		WHERE ContractID = @CTRID AND
			LogTypeID = @logtypeid)


		IF @TempSeqNO is null 
			Begin
				SET @SequenceNO = 1.0
			End
		ELSE
			Begin
				SET @SequenceNO = ROUND(@TempSeqNO,0,1) + 1.0
			End

Thank you very much for the helpful information.

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-12-04 : 01:45:22

This is the alternate way to find MAX value(since you are attempting to set a value to a variable the below said can be useful for u)..

SELECT TOP 1 SequenceNO FROM TAB_orders
WHERE ContractID = @CTRID
AND LogTypeID = @logtypeid
ORDER BY SequenceNO DESC

Here, it is a different case and not applicable for your post since it returns multiple values.. If there are more than 1 rows having MAX value, you can try this..

SELECT TOP 1 WITH TIES SequenceNO FROM TAB_orders
WHERE ContractID = @CTRID
AND LogTypeID = @logtypeid
ORDER BY SequenceNO DESC

Balaji.K

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources