Logical Query Processing- Fuzzy Query with Order By Clause

Today it happened, I was called by my fellow programmer, he saw me a piece of T-SQL Code and asks few question on it, I will try to replicate the same code here.

There was a query somehow like this

select sub_group,sub_group,name,name from #temp

where sub_group = 'Furniture'

order by sub_group

And the query was failing, and asked me to justify the why it failing. That’s good, I find it interesting to explain the logic behind the scene through Logical Query Processing.  Yeah ;) I didn’t ask tea this time for this: P

Let’s formulate the same scenario by creating a sample table.

create table #temp

(id int identity(1,1),

sub_group varchar(10),

name varchar(10))

Command(s) completed successfully.

Lets insert few records in it.

insert into #temp

select 'Furniture','chair' union all

select 'Vechile','Maruti' union all

select 'Furniture','Desk' union all

select 'Furniture','Dine' union all

select 'Vechile','Honda' union all

select 'H.Vechile','Mahindra'

Now we can have the same Select query. Lets fire the query and see  the output.

select sub_group,sub_group,name,name from #temp

where sub_group = 'Furniture'

order by sub_group

Msg 209, Level 16, State 1, Line 32

Ambiguous column name 'sub_group'.

Now lets take the error, and dig the background of it. Now it says Ambiguous Column name.

If you see in the select list, we have called this column two times, now if we go through logical query processing, it says for this query.

1^st from clause gets executed i.e #temp and record set will build up say RS1.

2^nd where by clause gets executed which will filter the data and remain with the data which has ‘Furniture’ as sub_group and create a new record set say RS2

3^rd Now the Select clause will gets executed with form one more column of same name as sub_group. So the new record set will have this many column “sub_group, sub_group, name, name” say this record set as RS3

4^th Now Oder By clause gets the chance, it operate on RS3 record set, now if you see in query, the order by clause is on sub_group, so when it tries to order the RS3 data, it will found two column with same name sub_group, and optimizer find it difficult which column to pick up, thus it throws Ambiguous column error.

Solution to this problem is: make an alias to one of the sub_group column.

select sub_group,sub_group SB ,name,name from #temp

where sub_group = 'Furniture'

order by sub_group

Run the query and you will get your result.

sub_group  SB         name       name

---------- ---------- ---------- ----------

Furniture  Furniture  chair      chair

Furniture  Furniture  Desk       Desk

Furniture  Furniture  Dine       Dine

You just have to make time for your coding and keep it balance J

 

Post Reference: Vikram Aristocratic Elfin Share

TOP 100 PERCENT and ORDER BY obsolete from SQL Server 2005

In SQL 2000 it was an attempt to return all records of the view in the correct order. In SQL 2005 and up you can not simulate ORDER BY using this trick anymore, so SELECT TOP (100) PERCENT has no meaning.

Lets stimulate the scenerio. We create a table testTop100 for our explanation with field id and name

create table testTop100

(id int identity(1,1),

name varchar(10))

Command(s) completed successfully.

Lets insert few records to testTop100

insert into testTop100 values('Abhaya')

insert into testTop100 values('Nitya')

insert into testTop100 values('Ananya')

insert into testTop100 values('Roma')

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Now we will be creating view vwTestTop100 with top 100 Percent and Order by clause

create view vwTestTop100

as

select top 100 percent * from testTop100 order by id desc

go

Command(s) completed successfully.

Lets see the result with simple select query

select * from testTop100

id          name

----------- ----------

1           Abhaya

2           Nitya

3           Ananya

4           Roma

(4 row(s) affected)

Now lets see the result of View which is using Top 100 Percent and order by on id desc

select * from vwTestTop100

id          name

----------- ----------

1           Abhaya

2           Nitya

3           Ananya

4           Roma

(4 row(s) affected)

As we can see SQL Server neither throws error nor it acknowledge the presence of order by clause, it simply ignore the order by clause in View defination.

Now lets try the following

select * from vwTestTop100 order by id desc

id          name

----------- ----------

4           Roma

3           Ananya

2           Nitya

1           Abhaya

(4 row(s) affected)

Now from the output we can see, desired result found

Conclusion :  It is pointless to add ORDER BY clause to the view definition and expect records to come in that ORDER. If you need records ordered, don't put ORDER BY in the view, but rather

select * from myView ORDER BY OrderFields.

This way the correct order will be guaranteed.

Abide by code semantic, u miss all coding fun..Walk off beyond   

Post Reference: Vikram Aristocratic Elfin Share

Sorting table separately while using UNION All, How to??

There are times when developer want top and bottom query of the UNION ALL resultset sorted independently, like this

select Columns from table1 order by Columns

union all

select Columns from tabbe2 order by Columns

However the above query will fail and give the output as

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'union'.

NOTE :It is not possible to have two different order by in the UNION statement because UNION returns single resultant.

However if your requirement is saying to sort top and bottom query independently then how to do?

Let’s create a scenario, by taking two tables TABLE1 and TABLE2

create table table1

(id int,

name varchar(15)

)

Command(s) completed successfully.

create table table2

(id int,

name varchar(15)

)

Command(s) completed successfully.

insert into table1(id,name)

select 1,'Ananya_Tab1'

union all

select 2,'Abhiroop_Tab1'

union all

select 3,'Gunjal_Tab1'

(3 row(s) affected)

insert into table2(id,name)

select 3,'Bikshapati_Tab2'

union all

select 2,'Sanjana_Tab2'

union all

select 1,'Akshit_Tab3'

(3 row(s) affected)

Now our tables are in place let’s do proceed to stimulate our requirement, we want like this  

select id,name, 'tab1' as odrCol from table1

order by id

union all

select id,name, 'tab2' as odr1 from table2

order by id

But firing the above script on ssms, gives error                          

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'union'.

And if we execute the select UNION without order by

select id,name from table1

union all

select id,name from table2

id          name

----------- ---------------

1           Ananya_Tab1

2           Abhiroop_Tab1

3           Gunjal_Tab1

3           Bikshapati_Tab2

2           Sanjana_Tab2

1           Akshit_Tab3

(6 row(s) affected)

However our requirement is like this

id          name          

----------- ---------------

1           Ananya_Tab1    

2           Abhiroop_Tab1  

3           Gunjal_Tab1    

1           Akshit_Tab3    

2           Sanjana_Tab2   

3           Bikshapati_Tab2

So to get the output as required, lets add a additional column ordCol and use it in order by clause

select id,name, 'tab1' as odrCol from table1

union all

select id,name, 'tab2' as odrCol from table2

order by odrCol,id

id          name            odrCol

----------- --------------- ------

1           Ananya_Tab1     tab1

2           Abhiroop_Tab1   tab1

3           Gunjal_Tab1     tab1

1           Akshit_Tab3     tab2

2           Sanjana_Tab2    tab2

3           Bikshapati_Tab2 tab2

(6 row(s) affected)

Now we can see we achieved our desired requirement of independent sorting of select query in UNION ALL.

Code exercises your creative instincts; do code n enjy J   

Post Reference: Vikram Aristocratic Elfin Share

TOP, WITH TIES and ORDER BY? What is WITH TIES clause in SQL Server?

WITH TIES can be used only when TOP and ORDER BY clauses are present in SELECT statement, both the clauses are required in order to use WITH TIES.

Let understand this by taking a small example: Here we are having a table parent_tab with following records:

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           sneha

3           pratik

3           pratik

1           sneha

4           chitrangada

5           chitrangada

(6 row(s) affected)

Now I am firing a select query with Top clause to retrieve the topmost record from the table [parent_tab] where the name is equal to ‘chitrangada’.

select top 1  * from parent_tab where name like 'chitrangada'

first_id    name

----------- --------------------------------------------------

4           chitrangada

(1 row(s) affected)

Here we can see the top most record has been arrived by the above query.

Now If I want to retrieve the top most record and all the records in the table where the name is equal to ‘chitrangada’?

If  such is my requirement then ‘WITH TIES’ will help you out, let see how:

select top 1 with ties  * from parent_tab where name like 'chitrangada' order by name

first_id    name

----------- --------------------------------------------------

4           chitrangada

5           chitrangada

(2 row(s) affected)

What it does it when you use TOP 1 rows, it will return you only 1 rows, but when used TOP 1 WITH TIES, it will return you all the rows that have same value as that of the last record of TOP 1.

The expected result is based on the column that is specified in ORDER BY. That is it will look for the column used in the ORDER BY to compare its equivalent in rest of the table.

NOTE: WITH TIES Clause can be used only with TOP and ORDER BY, both the clauses are required.

Post Reference: Vikram Aristocratic Elfin Share