Keep order of left table after join

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

Keep order of left table after join

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-05-20 : 07:24:40

Hi,

I have an problem with the order of the results after a join.

My first query works fine and the order of field Name ist correct.

Select * 

FROM

(SELECT * FROM

dtree A1

WHERE 

A1.Subtype=31356

AND 

A1.DataID IN 

(select DataID from dtreeancestors where AncestorID=9940974)) t

When I do a join the order of the left table changes

Select * 

FROM

(SELECT * FROM

dtree A1

WHERE 

A1.Subtype=31356

AND 

A1.DataID IN 

(select DataID from dtreeancestors where AncestorID=9940974)) t, llattrdata A4


WHERE 

t.DataID = A4.ID

How can I do a join and keep the order of the left table?

Kind regards,

Lara

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-05-20 : 07:31:25

You have to explicitly order the final result set by having ORDER BY t.AnyID/DateType column name

Reference:
http://stackoverflow.com/questions/5743373/how-to-maintain-order-while-doing-join-in-sql

--
Chandu

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-05-20 : 07:47:27

Hi,

the thing I can't order it by a column. would it make sense to add a column with the rank?

Kind regards,

Lara

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-20 : 10:39:01

quote:
Originally posted by musclebreast

Hi,

the thing I can't order it by a column. would it make sense to add a column with the rank?

Kind regards,

Lara

Well, you have to order the output by something, otherwise it may be different on subsequent executions. Remember that SQL is set-based. tables and query results contain/return multisets of data. Order is not imposed unless explicitly stated in an ORDER BY clause. You can't count on the order returned from a query. It may look "good" today but be totally messed up tomorrow.

How you order your output is really up to you, though it is usually driven by business needs.

GouravSaxena1987
Starting Member

23 Posts

Posted - 2014-05-21 : 06:36:23

You need to impose Order By clause explicitly otherwise it's behavior will not be organized.

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources