Order By Not Working

[primagic]

Any reason why my OrderBy isn't working. The OverallRankingScore is a numeric field but stills sorts my LeadID

SELECT      TOP 100 PERCENT dbo.Leads.*, LeadID AS LeadIDText, OutcomeID AS OutcomeIDText, OverallRankingScore AS OverallRankingScoreText, 
                        NextTaskTime AS NextTaskTimeText, StatusScore AS StatusScoreText, TelephoneScore AS TelephoneScoreText, 
                        LoanSizeScore AS LoanSizeScoreText, DateAddedScore AS DateAddedScoreText, LoanTypeScore AS LoanTypeScoreText, 
                        EquityScore AS EquityScoreText, SourceScore AS SourceScoreText, OutcomeLastModified AS OutcomeLastModifiedText
FROM          dbo.Leads
WHERE      (OutcomeID = 'Pending') OR
                        (OutcomeID = 'No Answer') OR
                        (OutcomeID = 'Engaged') OR
                        (OutcomeID = 'Future Business') OR
                        (OutcomeID = 'Waiting on Base Rate Moving') OR
                        (OutcomeID = 'Scheduled Call Unsuccessful')
ORDER BY ABS(OverallRankingScore) DESC

 

[djj55]

Try using OverallRankingScoreText

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[gmmastros]

Can you run this query and post the output here:

Select Table_Name, Column_Name, Data_Type 
From   Information_Schema.Columns
Where  Table_Name = 'Leads'
       And Column_Name = 'OverallRankingScore'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[primagic]

I managed to get it working by changing

TOP 100 PERCENT

to

TOP 2147483647

Read somewhere there is a bug with SQL 2005. Automatically adds the TOP 100 PERCENT which then cancels any orderby

 

[gmmastros]

I think you are a little mistaken. There is not a bug with SQL2005. Microsoft never supported order by's in views. With SQL2000, people discovered that Top 100 percent was a workaround for doing something that is unsupported. This hack doesn't work in SQL2005, so people came up with another one.

Instead of hacking your views, you should apply an order by at the time you use the view. Ex:

Create View AnyViewName
As
Select ......


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

Select Column(s) From AnyViewName Order By ColumnX


My concern is that you are relying on another hack that may not work with future versions of SQL Server.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[primagic]

Thank you for your advice. The query is actually makes up the recordsource for a form. (I am using access frontend)

 

[markros]

If this is a view, then you can not rely on the ORDER BY there, Period. You need to add ORDER BY to your final select statement.

PluralSight Learning Library