plz plz help with select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

plz plz help with select statement

Author

Topic

mirza21
Starting Member

11 Posts

Posted - 2008-10-30 : 00:58:52

Hi friend i am new to sql and facing problem in writing select queries

Plz help me know how sql query gets data from server , i am unable to understand the flow of query , i could not make out which part of query is executed first.

chk this case:-

select top 3 with ties ProductName,UnitsOnOrder from Products order by UnitsOnOrder desc

1) As per me it executes left to right .First it select top 3 records from products table then order them and then arrange then in descending order

but results i am getting are different

so please help me logically understand how above select statement works

Jason100
Starting Member

34 Posts

Posted - 2008-10-30 : 02:02:59

select top 3 ProductName,UnitsOnOrder from Products order by UnitsOnOrder desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 03:50:35

it orders the result based on decreasing order of specified field (UnitsOnOrder). then it takes top 3 out of them. Then it looks for any other records having same UnitsOnOrder value as the included 3 and also includes them in result nad returns it.

ursangel
Starting Member

17 Posts

Posted - 2008-10-30 : 06:44:53

Top 3 with ties counts the records with same UnitsOnOrder as 1 record and then brings the result set.
IF you have a table with values
ProductName UnitsOnOrder
P1 5
P2 4
P3 5
P4 3
P5 4
P6 6

When your query runs it will first order the result set in the Descending order
your resukt set will looklike
ProductName UnitsOnOrder
P6 6
P1 5
P3 5
P2 4
P5 4
P4 3
Over this result set, the Top 3 with ties will be applied.
So u will get the result set as
P6 6
P1 5
P3 5
P2 4
P5 4
P2 & P3 will be tied up as one record and P2 & P5 will be tied up as another record.
Please chk the following link for detail
http://www.devx.com/vb2themax/Tip/18477

Regards
Angel

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-10-30 : 10:09:25

The main clauses of a SQL query are evaluated in the following order:

FROM (including JOINs)
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
TOP

mirza21
Starting Member

11 Posts

Posted - 2008-10-31 : 00:54:05

you people helped me clear my very basic doubt so now i can move ahead
thanks a friends

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources