Help with ORDER BY in SUBQUERY ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2012 Forums

Transact-SQL (2012)

Help with ORDER BY in SUBQUERY ?

Author

Topic

ipisors
Starting Member

39 Posts

Posted - 2015-03-06 : 11:46:41

Not sure if this is necessarily called a SUBQUERY, properly, or not.

Simply put, I want to INSERT into a table, using a SELECT statement.

However, I DO want to use an ORDER BY in the select...Why? Because I am trying to achieve a somewhat "random" top N.

I have decided to define that 'random' quality, by ordering by a particular field.

Why is ORDER red lined with parsing error?


					insert dbo.claimsaudit_temp_finalaudit(changeuserid,changedate,auditnumber,samplesize,formnumber,primaryservdate)
						(
							select top(@threepercentrounded)
								analyst,
								changedate,
								@totalclaimsworked,
								@threepercentrounded,
								claimno,
								primaryservdate
							from
								dbo.claimsaudit_temp_paidclaims c
							where
								c.analyst = @ezcapusername
							order by claimno --I.E. a "meaningless" sort, because we're selecting TOP #, we call this concept "random", or close enough.
						)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-06 : 12:22:21

[code]
insert into dbo.claimsaudit_temp_finalaudit(changeuserid,changedate,auditnumber,samplesize,formnumber,primaryservdate)
select top(@threepercentrounded)
analyst,
changedate,
@totalclaimsworked,
@threepercentrounded,
claimno,
primaryservdate
from dbo.claimsaudit_temp_paidclaims c
where c.analyst = @ezcapusername
order by claimno
[/code]

The order by is not meaningless. It is determining which rows to return for top. It certainly is not random. If you want random, then you can use NEWID().

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2015-03-06 : 12:30:00

Error because of wrong syntax, not ORDER BY

Remove ( and ) in your query

ipisors
Starting Member

39 Posts

Posted - 2015-03-06 : 12:43:54

Yikes, parenthetical things still confuse me sometimes...I've done too much VB where just about anything could be enclosed in a parenthetical, as long as equivalents were equivalents, even when it wasn't necessary.

THANKS much for the correction.

As to the order by, I didn't really mean "meaningless", I just meant ordering by something that has no meaning to business ... and thus, could be considered "random" - even though I realize that, diving deep enough into the layers of theory, it still isn't truly, totally, random.

I guess you're right - it's definitely not "random", it's just "without any meaning to the business" - i.e., a sort that will just pick the top 3 without any pattern that will be a detriment to the sort.

Sorting by claimno is basically a sort with no meaning to the business - but I like the NEWID(), it seems like an even better choice.

Thanks again !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources