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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.