MS SQL QUERY WITH JOINS AND ORDER BY

I have the below query and it ignores my order by , I want to order by Name ASC how is this done

SELECT        TOP (100) PERCENT dbo.t_dir.dir_id, dbo.t_dir.Name, dbo.t_dir.cat_id, dbo.t_dir.perioxi_id, dbo.t_dir.Address, dbo.t_dir.Telephone, dbo.t_dir.Fax, dbo.t_dir.website, dbo.t_dir.client_cat_id, dbo.t_dir.ActivationDate,
                         dbo.t_dir.ExpirationDate, dbo.t_client_cat.cat, dbo.t_perioxi.perioxi, dbo.t_dir_cat.cat AS proffession, dbo.t_perioxi.nomos_id, dbo.t_nomos.nomos, dbo.t_dir.email, dbo.t_nomos.nomos_id AS Expr1,
                         dbo.t_dir.stages, dbo.t_dir.AFM, dbo.t_dir.DOY, dbo.t_dir.diakritikos_titlos, dbo.t_dir.address_xrewsis, dbo.t_dir.epaggelmatiko_tel, dbo.t_dir.epwnymia, dbo.t_dir.drastiriotita, dbo.t_dir.extra,
                         dbo.t_dir_cat.cat_id AS prof_id, dbo.t_dir.months, dbo.t_dir.mobile, dbo.t_dir.notify, dbo.t_dir.profile, dbo.t_dir.tameia, dbo.t_dir_cat.catb AS proffession2, dbo.t_dir.MapAddress
FROM            dbo.t_nomos RIGHT OUTER JOIN
                         dbo.t_perioxi ON dbo.t_nomos.nomos_id = dbo.t_perioxi.nomos_id RIGHT OUTER JOIN
                         dbo.t_dir_cat RIGHT OUTER JOIN
                         dbo.t_dir ON dbo.t_dir_cat.cat_id = dbo.t_dir.cat_id LEFT OUTER JOIN
                         dbo.t_client_cat ON dbo.t_dir.client_cat_id = dbo.t_client_cat.client_cat_id ON dbo.t_perioxi.perioxi_id = dbo.t_dir.perioxi_id
WHERE        (dbo.t_dir.stages <> 4)
ORDER BY dbo.t_dir.Name
tonyantonyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
You probably don't need "TOP (100) PERCENT" at all, since that's everything.

Change:  ORDER BY dbo.t_dir.Name
To:           ORDER BY Name

Does that make any difference?
0
tonyantonyAuthor Commented:
nope it didnt work
0
dsackerContract ERP Admin/ConsultantCommented:
Must those be RIGHT outer joins?

Also, change to ORDER BY 2.

That will order by the second column in your results set. That really should work.

Additionally, make sure there is no space in front of the Name (or a period, or something that can easily be missed), as that can skew the sort.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperCommented:
The following is your own code rearranged pulling data from dbo.t_nomos table only.

- test it to make sure it runs okay.
- if it ran okay, then include the second table in the FROM clause (dbo.t_perioxi) of course including its related fields in SELECT clause as well.
- When you get to the 4th table (dbo.t_dir) include WHERE and ORDER BY clauses.

This way, you could easily locate what the problem is. I suspect, the problem will be related to ON in the joins.

SELECT TOP (100) PERCENT 
   t.nomos
   , t.nomos_id AS Expr1
 /*  
   , p.nomos_id
   , p.perioxi

   , d.cat_id AS prof_id
   , d.catb AS proffession2
   , d.cat AS proffession

   , i.dir_id
   , i.Name
   , i.cat_id
   , i.perioxi_id
   , i.Address
   , i.Telephone
   , i.Fax
   , i.website
   , i.client_cat_id
   , i.ActivationDate
   , i.ExpirationDate
   , i.email
   , i.stages
   , i.AFM
   , i.DOY
   , i.diakritikos_titlos
   , i.address_xrewsis
   , i.epaggelmatiko_tel
   , i.epwnymia
   , i.drastiriotita
   , i.extra
   , i.months
   , i.mobile
   , i.notify
   , i.profile
   , i.tameia
   , i.MapAddress 

   , e.cat
*/
FROM dbo.t_nomos t /* RIGHT OUTER JOIN
     dbo.t_perioxi p ON t.nomos_id = p.nomos_id RIGHT OUTER JOIN
     dbo.t_dir_cat d RIGHT OUTER JOIN
     dbo.t_dir i ON d.cat_id = i.cat_id LEFT OUTER JOIN
     dbo.t_client_cat e ON i.client_cat_id = e.client_cat_id ON p.perioxi_id = i.perioxi_id
WHERE (i.stages <> 4)
ORDER BY i.Name  
*/

Open in new window


This post has been revised.
0
Scott PletcherSenior DBACommented:
I suspect that must be a view definition: thus the need for "TOP (100) PERCENT".

Rows returned by a view are never guaranteed to be in any specific order unless you add an ORDER BY clause to the SELECT query against the view, even if the view itself has an ORDER BY.

That is the ANSI-standard, and is thus likely to be the same across all DBMSs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
So:

CREATE VIEW view_name
AS
SELECT        TOP (100) PERCENT dbo.t_dir.dir_id, dbo.t_dir.Name, dbo.t_dir.cat_id,
...
 ORDER BY dbo.t_dir.Name
GO

SELECT * FROM view_name --could be in any order

SELECT * FROM view_name ORDER BY Name -- this is what you must do to order the rows returned by a view
0
tonyantonyAuthor Commented:
all the above not working . And also an adittion I am working with a view
0
dsackerContract ERP Admin/ConsultantCommented:
An ORDER BY is invalid inside a view definition anyway.

If your Name field is the second column in your select statement, and if ORDER BY 2 is not working, then you have some mis-leading characters in your data (space, some control character, etc) that is causing the problem.
0
Mike EghtebasDatabase and Application DeveloperCommented:
tonyantony,

In my post, you were supposed to try incremental steps. Did the following work?
SELECT TOP (100) PERCENT 
   t.nomos
   , t.nomos_id AS Expr1
FROM dbo.t_nomos t 

Open in new window


This was the first step. What happened at this step. Did you try the steps after this one? Which step failed?

When you are saying "all the above not working", this doesn't make sense to me. What I was hoping to hear from you to tell us at what table it failed (or did not work). Am I making sense to you?

Mike
0
Anthony PerkinsCommented:
I have the below query and it ignores my order by
SELECT        TOP (100) PERCENT ...
ORDER BY dbo.t_dir.Name

That is by design.  If you use TOP(100) PERCENT the ORDER BY is ignored.  Period.

But don't take my word for it, read this blog by Connor Cunningham:
TOP 100 Percent ORDER BY Considered Harmful.
0
Mike EghtebasDatabase and Application DeveloperCommented:
Select TOP (100) PERCENT dbo.t_dir.dir_id, dbo.t_dir.Name, dbo.t_dir.cat_id,

or

Select dbo.t_dir.dir_id, dbo.t_dir.Name, dbo.t_dir.cat_id,

Are expected to return the same result. Then why to use TOP (100) PERCENT
0
Anthony PerkinsCommented:
There are all manner of kludges to work around this, my "favorites" are TOP 99 PERCENT and TOP (2147483647).  In the end, these are just gimmicks and they will come back to haunt you.

Incidentally, if you want to change this ANSI behavior to go back to the way it was with SQL Server 2000,  there used to be a Trace flag you could set in the SQL Server startup options.  No idea what it was or if it is even still supported (I can only hope it is not)
0
Anthony PerkinsCommented:
eghtebas,

The author is using a VIEW and in order to use ORDER BY in a VIEW you must have a TOP clause.  This is compounded by the fact that the very lame VIEW Designer by default used to automatically add the TOP (100) PERCENT clause and you have a recipe for a very much repeated question here on EE.
0
Mike EghtebasDatabase and Application DeveloperCommented:
Thanks for the explanations Anthony.
0
Anthony PerkinsCommented:
And I should have added that adding an ORDER BY clause to a VIEW is misguided to say the least.  As Scott has mentioned here, the correct way to sort the results in a VIEW is to do:
SELECT ... FROM MyView ORDER BY Something.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.