ORDER BY NEWID() and another column.

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)

ORDER BY NEWID() and another column.

Author

Topic

optik
Starting Member

1 Post

Posted - 2003-12-05 : 14:33:11

Hello, I'm using a SELECT statement which ORDER BY's NEWID() to present the items in a random order, but I also need to sort by another column because some of the items need to be at the bottom of the list no matter what. However, when I seem to just add the column name to the ORDER BY statement, it doesn't sort by that column. So my question is: If you order by newid(), can you not specifiy other columns to sort by as well?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-05 : 14:43:32

Take a look at this:



SELECT 	
	a2.AssetID, 
	a1.company_id
FROM 	
	ASSETS a1
INNER JOIN 
	(
		SELECT TOP 10 AssetID
		FROM ASSETS
		ORDER BY NEWID()
	) a2
ON 	
	a1.AssetID = a2.AssetID
ORDER BY 
	a1.company_id

I am joining to a derived table that gets me the random order.

Does that help?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-05 : 15:18:55

Try:


select 
  <column list> 
from 
  sometable
order by 
  CASE WHEN <some condition> THEN 1 ELSE 0 END, New_ID()

Where <some condition> is what makes a row need to sort to the end.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-05 : 15:24:18

I knew there'd be a better solution. I am getting too comfortable with joining to derived tables that I am using them too much. When to use them and when to not use them... Any guidelines?

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources