2
votes
1answer
311 views

Why is the ORDER BY clause in a view ignored as soon as it's called with a WHERE claue?

I have a view that joins several tables, just to simplify a frequently called query. This view has an ORDER BY clause on a calculated field (the order will never be different). (on SQL Server Express ...
-2
votes
1answer
231 views

How to prevent duplicated records when applying ORDER BY NEWID() to fetch them randomly? [closed]

I tried using the solution provided in http://stackoverflow.com/questions/1117761/sql-query-to-return-rows-in-random-order to fetch random records in my query. But I have to add NEWID() to the list of ...
2
votes
1answer
1k views

ORDER BY items must appear in the select list […]

Using Microsoft SQL Server 2008, I get the following error. Msg 104, Level 16, State 1, Line 43 ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or ...
10
votes
1answer
5k views

Parent-Child Tree Hierarchical ORDER

I have to following data in SQL Server 2008 R2. SQLFiddle Schema: CREATE TABLE [dbo].[ICFilters]( [ICFilterID] [int] IDENTITY(1,1) NOT NULL, [ParentID] [int] NOT NULL DEFAULT 0, ...
1
vote
1answer
111 views

Should all queries where you expect a specific order, include an ORDER BY clause?

In order to better understand the SQL Server query processor, I've been thinking about how the ORDER BY clause works, and how SQL Server provides results. It appears SQL Server will provide results ...
1
vote
1answer
237 views

weird sql server behavior when inserting and selecting

Today after one of the drives on our sql server ran out of space. The drive contained eventracker logs, so I didn't think it was a big deal, but then I noticed when I insert a record into a database, ...
2
votes
2answers
124 views

Migrating SQL Server 2000 to SQL Server 2008

I'm migrating SQL Server 2000 to SQL Server 2008. I have many views. How do I check each views that if they have an order by clause? Thanks.
5
votes
1answer
292 views

How does SQL Server order results when joins are used?

How does SQL Server figure out the order of the records in the result set of query execution? I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I ...
2
votes
1answer
934 views

SQL Server 2005: Full-text search, order by matches

Is it possible to do a full-text search in SQL Server 2005, ordering on the number of matches? For example, let's say I have the following products for sale. Tennis Racquets Tennis Balls Footballs ...
23
votes
5answers
5k views

Why does ORDER BY not belong in a View?

I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with) I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
3
votes
3answers
1k views

Does ORDER BY on a clustered key effect performance?

Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey so I additionally always ORDER BY PrimaryKey in all queries. Does this ORDER BY affect ...
2
votes
2answers
2k views

Sort by match of LIKE

I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the ...
4
votes
3answers
4k views

How to make conditional ordering for two or more columns

IN MSSQL 2005 I am writing one query with conditional sort and my problem is I do not know how I can sort condidional using two columns. if i wrte code like this it is working normaly select * from ...
8
votes
3answers
2k views

What are the alternatives for an ORDER BY clause in a View?

This question just had to be in this site :) ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view. I know that there are ...