Why ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified

say i have a table

char              Value

—–             ——

A                 1

E                 5

C                 3

B                 2

D                4

A                6

say, the expected result here is Get distict top 5 char’s in the order of Value

select top 5 distict char from (select char from table order by pkey)temp

— Error : Order By should not be part of SubQuery

— Reason: Why we use subquery : to be source of outer clause.

To Do :

***** Lets try it out by Top Clause

select top 5 disctinct from (select top 100 percent char from table order by value)temp *Here inner query is accepted for having ‘Order By’ since it is a helper to TOP clause Since we specify 100 percent, inner results are not Ordered actually. –WorkAround : we can use top 99.99 percent to enforce order by, but the no of result set records varies, so this solution is not at all recomended.

Happy Reading 🙂

I am so happy that i reached 50th post 🙂

Advertisements

Distinct on one column & Order By on another column

1) Problem : How to query when one need to Order By One Column & Apply distict on another column

say i have a table

char              Value

—–             ——  

   A                1  

   E                 5  

   C                 3  

   B                 2  

   D                 4  

   A                  6  

say, the expected result here is

Get distict top 5 char’s in the order of Value

select top 5 distict char from table order by Value

— Error : Order By is not part of Distict

— Reason: when we try to get distinct – A,E,C,B,D are qualified output Now we have to apply Order by for this output based on its value. But the conflict here is whether to consider value 1/6 for ordering A in the result set. So we have to correct the query by resolving the conflict  

* First we form groups of char  

* For each group, we should choose the value to be used in the ordering, hmm here we have to choose btw 1 or 6    this we can do using aggregate functions in the group by clause

***** The Final Query

select top 5 char, min(value) from table group by char order by min(value)

Note : since ordering by ascending order, we used min, otherwise max should be used.

Happy Querying 🙂