weird sort - 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

Transact-SQL (2000)

weird sort

Author

Topic

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 09:49:01

Hi all.

when i run the following code in QA
the first @num equals 2, the second equals 1.
so which column does the order by use in case #2??
does it use an index for sort and if so what happens if there's no index?? just a thought if anyone has any ideas...


declare @num int
select top 1 @num = count(*) from pubs..authors group by au_lname order by count(*) desc
select @num

-- which column does the order by use in this case??
select top 1 @num = count(*) from pubs..authors group by au_lname order by 1 desc
select @num

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-03-15 : 10:31:23

Would

select top 1 @num = count(*), @MyName = au_lname from pubs..authors group by au_lname order by 1 desc
select @num, @MyName

tell you?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 10:42:41

i guess my brain is slow today because if i copy your query to QA it returns me an error
Column 'pubs..authors.au_lname' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
and even if i remove the @num and @MyName your query tells me nothing... what should it tell me?

i have a NC compostie index on au_fname and au_lname so i guess i doesn't sort it on them, does it???

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-03-15 : 10:44:46

You declared @MyName ?? (I'm sure you have, but just thought I'd check)

I didn't actually TRY it!

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 10:49:53

yes, i declared it

Go with the flow & have fun! Else fight the flow

rkasse
Starting Member

14 Posts

Posted - 2005-03-15 : 11:13:12

Don't know exaclty what's going on here but if you change both statements to "Select count(*)..." (i.e. remove @num = and the select @num) they both return the correct answer of 2.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 11:20:04

yes i know that. however i want to assign a variable like that. it's just a weird thing i'd like to figure out.
because this also returns 2 for both examples:


DECLARE @num int
set @num = (select top 1 count(*)
	from pubs..authors
	group by au_lname 
	order by 1 desc) -- or count(*) 
select @num

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 12:35:02

so noone has a clue about this??

Go with the flow & have fun! Else fight the flow

rkasse
Starting Member

14 Posts

Posted - 2005-03-15 : 12:50:02

As noted before, "Select top 1 count(*)..." gives the correct answer in both cases. It's the @num = count(*) ... with "order by 1" that seems to generate the incorrect answer.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-15 : 12:51:31

**This one is ordering by All the values of Count(*)
select top 1 @num = count(*) from pubs..authors group by au_lname order by count(*) desc
select @num

This one is taking the top 1 value and ordering by it, if Top 2 were selected it would order just between the 2 returned by "top"
select top 1 @num = count(*) from pubs..authors group by au_lname order by 1 desc
select @num

Edit:
Because order by 1 means the value @num (which just happens to be the result of count(*) for the 1st grouping).

Be One with the Optimizer
TG

rkasse
Starting Member

14 Posts

Posted - 2005-03-15 : 13:06:27

TG I agree, but the value of top 1 @num is 2 so why does it seem to return 1.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 13:10:14

so basicaly you're saying that in the 1st case the order is applied on the count(*) as specified
and in the 2nd the order is applied on the @num????

somehow that doesn't sound right to me... what kind of logic am i missing here??????

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-15 : 13:11:09

Since there is no order by applied to:
Select top 1 count(*) from pubs..authors group by au_lname
How do you know it should be 2?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-15 : 13:12:46

"somehow that doesn't sound right to me... what kind of logic am i missing here??????"

That's because you're thinking like a human, my friend. You must learn to become One with Optimizer...

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-15 : 13:17:06

What column is this ordering by?

Select top 1 case 1 when 1 then 1 else 1 end [1] order by 1

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 13:24:07

yeah that's probably it.. i'm thinking like a human. damn organic brain...

this is from BOL:

quote:

Specifies a column on which to sort. A sort column can be specified as a name or column alias (which can be qualified by the table or view name), an expression, or a nonnegative integer representing the position of the name, alias, or expression in select list.

what do you mean by there's no ordery by applied?
select top 1 count(*) from pubs..authors group by au_lname order by 1 desc
returns 2 which is correct.

i'm beginging to feel like and ID10T here....

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 13:26:55

it's ordering by 1

but you got me there. my guess would be that name takes priority over ordinal postion.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-03-15 : 13:27:43

Can you use something like
SELECT @NUM = (select top 1 count(*) from pubs..authors group by au_lname order by COUNT(*) desc)
to work around the uncertainty?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 13:30:54

i can. i have. that's no problem.
i want to clarify why is this occuring. it's stumping me...
it's just one of those "I wanna know WHY!!" things.

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-15 : 13:32:06

If this question is directed to me:

quote:
what do you mean by there's no ordery by applied?
select top 1 count(*) from pubs..authors group by au_lname order by 1 desc
returns 2 which is correct.

regarding this comment:

quote:
Since there is no order by applied to:
Select top 1 count(*) from pubs..authors group by au_lname
How do you know it should be 2?

What I mean is that this:
select top 1 @num = count(*) from pubs..authors group by au_lname order by 1 desc

translates to:
Select [@num]
From
(select top 1 count(*) as [@num] from pubs..authors group by au_lname)
order by [@num]

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 13:35:46

it was meant for you and

REALY?!?!?!?!?!??!

it translates to that????? how do you know that?????
i always thought it would first evaluate the whole select and then apply it to the variable.
ie
set @num = (select top 1 count ...)
equals
select top 1 @num = count(*) ....

i guess one learns new stuff every day....

Go with the flow & have fun! Else fight the flow

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources