Stupid SQL - 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 2000 Forums

SQL Server Development (2000)

Stupid SQL

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-06-20 : 12:16:52

Why is this allowed in Tsql and is it always consistent?


DECLARE @someValue FLOAT

IF object_Id('tempdb..#matrix') IS NOT NULL DROP TABLE #matrix

CREATE TABLE #matrix (
	[x] FLOAT
	)

INSERT INTO #matrix (
	[x]
	)
      SELECT 123.1
UNION SELECT 222.2
UNION SELECT 100.2
UNION SELECT 90.5
UNION SELECT 230.34

SELECT @someValue = [x] FROM #matrix
PRINT @someValue

I've just found a whole bunch of code like this (minus the formatting) in some stored procedures that someone wrote ages ago. It seems to give you the highest value from the table. Is this functionally identical to..


SELECT @someValue = MAX([x]) FROM #matrix

Or is it doing something else?

Charlie.

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 12:25:27

Nope its actually assigning the values one by one to variable @someValue and in end the variable has alst value assigned to it (230.34). there's no guarantee that it will be the max value although in this case it happened so (as max value was last value). You can never guarantee what value will be assigned to variable in a statement like this. the proper approach is:-

SELECT @someValue = MAX([x]) FROM #matrix

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-20 : 14:48:18

quote:
Originally posted by visakh16

there's no guarantee that it will be the max value although in this case it happened so (as max value was last value).

there's no so such thing as "last value" in a table - it's not ordered. there is no guarantee that the "last value you inserted" will be the last value in a result set when you select from the table. it may happen so due to details of the query plan, but there's no guarantee.

you have to specify ORDER BY if you want things ordered.

but you know all this already i'm sure visakh.

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-20 : 14:56:48

quote:
Originally posted by jezemine

quote:
Originally posted by visakh16

there's no guarantee that it will be the max value although in this case it happened so (as max value was last value).

there's no so such thing as "last value" in a table - it's not ordered. there is no guarantee that the "last value you inserted" will be the last value in a result set when you select from the table. it may happen so due to details of the query plan, but there's no guarantee.

you have to specify ORDER BY if you want things ordered.

but you know all this already i'm sure visakh.

elsasoft.org

i was refering to last value assigned to variable @someValue. that was the reason i guess OP got confused that select statement in code will put the max value to variable always. Sorry if i didnt explain it clearly earlier.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-06-23 : 04:15:58

Thanks for the replies -- more or less what I was expecting as that block of code in the stored proc took the lions share of the runtime and didn't seem to do anything sensible with the result anyway.

I don't think there is a *last* value in the table. If you change the code to be...


DECLARE @someValue FLOAT

IF object_Id('tempdb..#matrix') IS NOT NULL DROP TABLE #matrix

CREATE TABLE #matrix (
	[x] FLOAT
	)

INSERT INTO #matrix (
	[x]
	)
      SELECT 123.1
UNION SELECT 222.2
UNION SELECT 100.2
UNION SELECT 90.5
UNION SELECT 230.34
UNION SELECT 111.11

SELECT @someValue = [x] FROM #matrix
PRINT @someValue

Then (in query analyser at least) I still get 230.34 but I think that because if you don't supply a ORDER BY I think QA will attempt to order the results by some criteria anyway.

Thanks once again.
Charlie.

-------------
Charlie

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 04:19:57

If just ANY value is ok, just select a random 1.

SELECT top 1 @someValue = [x] FROM #matrix

That will speed up things, especially if there are many records in #Matrix

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources