CODEWITHSUNDEEP
sqlsql-servervisual-studiosql-view
DoIt
DoIt

Reputation: 3448

Incorrect sorting in SQL views

I am trying return design an SQL View which returns TOP 3 records from a table in ascending order considering NULL values in the table as the largest value so that I can discard the null values from top 3 results if there are more than three records.

I was able to successfully do it using following query but for reasons it is misbehaving w.r.t the third record is that if the third and fourth record in my table are same then my view is returning a NULL value are the third record which is kind of weird.

For Example Consider I have a column in my table named Grades with values 23,45,19,35,35

and when I run my view it should return top 3 values in ascending order which are 19,23,35 but for some reasons my query returns 19,23,NULL and the query works fine if my third record that is 35 is distinct ie if the Grades are 23,45,19,35,40 then it works fine and returns 19,23,35

My Query is as follows 

CREATE VIEW [dbo].[test]
AS

SELECT distinct

   CC.Grades,
   CC.Term,  




   FROM CanadianCrudes CC
CROSS APPLY (SELECT TOP 3
               Grades

             FROM CanadianCrudes iCC
             WHERE CC.Term = iCC.Term 
             ORDER BY case when iCC.Grades is null then 1 else 0 end, iCC.Grades asc ) iCC

Upvotes: 0

Views: 85

Answers (2)

M.Ali
M.Ali

Reputation: 69554

We cannot use Order by clause in a view's definition in Sql server, unless used with TOP clause a very common practice among developers is to add a SELECT TOP 100 PERCENT yet this does not guarantee the order of rows when you select from view.

You will need to Select against directly the table itself.

For a detailed answer read here Views and ORDER BY (John Paul Cook)

Also When you query through a view that uses the ORDER BY clause in SQL Server 2008, the result is still returned in random order

Upvotes: 1

jhe
jhe

Reputation: 180

You cannot order a View. Instead you have to order your result when you query your view.. Eg:

Select top 1 * from [myNewView] Order by [myNewView].[Column1]

Upvotes: 1

Related Questions