[dba-SQLServer] Strange happening

Have you tried  ORDER BY NEWID()
What happens if you do that? Does it still order by PK?

(I often use SELECT TOP x  PK, fld1,fld2 FROM tbl1 ORDER BY NEWID()
to return x random records from a table.)


On 27 Feb 2008 at 18:46, jwcolby wrote:

> I have a pretty strange happening. 
> 
> I have a table with a PK which is an autoincrement int.  When I create a
> view and include that field, then all order by clauses are ignored.  IOW I
> create a query 
> 
> SELECT PKID, Fld1, fld2, fld3 ORDER By Fld3 DESC
> 
> And when executed the data is ordered by PKID.
> 
> If I save the view and then create a QUERY SELECT * from MyView ORDER BY
> Fld3 DESC then it does in fact order by fld3 desc order.  If I save THAT SQL
> MSTATEMENT into another view and then execute that view, I am right back to
> ordered by PKID.
> 
> I am at a loss as to why this happens and how to avoid it.  I am trying to
> pull the first 1 million rows, ordered in a random order (for testing of
> another app) so I tried creating a view where I order by zip 4 DESC.  I
> store the view and then export the view into a CSV file.  The data comes out
> ordered on the PKID.
> 
> Truly weird!
> 
> I can't go further on my testing until I get this resolved.
> 
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>