-1

I am using Report Builder 3.0. I have a report where one of the sorts is based on the line (it is also grouped on line) using Row Groups. When I use LIKE in the query code, the sorting on the report works fine: it is sorted A-Z. When I use = in the query code, the report sorts it like this:

LN BM4
LN BM2
LN BM1
LN BM6
LN BM5
LN WT2
LN WT4
LN WT3
LN WT5
LN BM3
LN WT5

Why is using a different operator causing a sorting difference? How can I get my report to sort A-Z and still use the = operator? On a side note, I was told by one of our I.T. guys to use = because using LIKE slows down a query significantly, but seeing as how it's causing this problem I'm wondering what I should really use. Is this true?

Below I have given all of the possible data in our table.

**Raw data (unsorted)**
LN BM1
LN BM2
LN BM3
LN BM4
LN BM5
LN BM6

LN WT1
LN WT2
LN WT3
LN WT4
LN WT5
LN WT6

Query code

(CASE
            WHEN jobs.Uf_Production_Line LIKE 'LN BM%' THEN jobs.Uf_Production_Line
            WHEN jobs.Uf_Production_Line LIKE 'LN WT%' THEN jobs.Uf_Production_Line

            ELSE

            (CASE

                WHEN    IsNumeric(RIGHT(jobs.Uf_Production_Line, 4)) = 1 
                    THEN RIGHT(jobs.Uf_Production_Line, 4)

                ELSE    

                    (CASE
                        WHEN    IsNumeric(RIGHT(jobs.Uf_Production_Line, 3)) = 1 
                            THEN RIGHT(jobs.Uf_Production_Line, 3)
                        ELSE    
                            '99999'
                    END)
            END) 


        END
    ) AS line

ORDER BY statement at the bottom of the query seems to have no effect because there are different grouping and sorting rules based on Row Groups in the Design View of the Report Builder

ORDER BY job, datevalue, shift
whatwhatwhat
  • 1,991
  • 4
  • 31
  • 50
  • 4
    Order of result doesn't depend on `Like` or `=`. Always you need to use `Order By` clause to order your results – Pரதீப் Aug 17 '15 at 17:03
  • @Indian I haven't made any other change. I am switching the code back and forth between `LIKE` and `=` and it is definitely the cause of the this weird sorting. – whatwhatwhat Aug 17 '15 at 17:04
  • 1
    The query code that you show is just an expression. Where is it used in the query? – Guffa Aug 17 '15 at 17:10
  • 7
    NO. The "cause of the this weird sorting" is that your query doesn't have an `ORDER BY`. [See #3 here](http://blogs.sqlsentry.com/aaronbertrand/t-sql-tuesday-56-sql-server-assumptions/), then add an `ORDER BY` to your query to get ***predictable ordering***. There is no other way. If you don't have an `ORDER BY`, dozens of factors can influence the way SQL Server will return results. – Aaron Bertrand Aug 17 '15 at 17:11
  • @Guffa it's part of a very large query, so I only pasted the expression in question. – whatwhatwhat Aug 17 '15 at 17:11
  • 1
    Also see [this](http://stackoverflow.com/a/15188437/61305), [this](http://dba.stackexchange.com/a/21437/1186), and [this](http://dba.stackexchange.com/a/76225/1186). – Aaron Bertrand Aug 17 '15 at 17:18
  • @AaronBertrand Ah, my fault. I should have made it more clear that my _report_ isn't sorting, not the query. – whatwhatwhat Aug 17 '15 at 17:21
  • I don't understand the difference between "your report" and "the query"... also, tell your I.T. guy to read up on the difference between `col LIKE 'foo%'` (which can seek to a range scan if there is an index with `col` high enough in the key) and `col LIKE '%foo%'` (which cannot)... – Aaron Bertrand Aug 17 '15 at 17:22
  • @AaronBertrand the report is sorting based on the row groups. I noticed that I should have specified this because taking your advice I checked the bottom of my query and noticed that there already _is_ an `ORDER BY` statement, but it is not affecting the end report. I'll add it to the original question. – whatwhatwhat Aug 17 '15 at 17:24
  • You need to describe how this query is getting fed to the report. Without this information, nobody can help you. You need to be able to tell Report Builder how the query should be sorted. I don't use that tool, so I have no idea what you mean by "the report is sorting based on the row groups." If you want to sort by Uf_Production_Line, there must be some way to tell Report Builder that that's the sorting you want. – Aaron Bertrand Aug 17 '15 at 17:24
  • @AaronBertrand I made a report in a tablix using the Design View in Report Builder 3.0. I am connected to my company's data source. I have 5 datasets, one of them being the main one where the query code from my example is located. I right click on the main dataset and hit the "query" button to modify the query. Does this paint a good picture? – whatwhatwhat Aug 17 '15 at 17:29
  • Not really, because I can't see you pointing and clicking, and I don't understand that tool well enough to guide you on exactly how you should dictate to it that you want a specific ordering. – Aaron Bertrand Aug 17 '15 at 17:31
  • @AaronBertrand please keep in mind the question I am asking: Why does changing an operator here affect the sorting on my report? I know this is difficult to answer without seeing the entire report, query, or database, but I thought there was a fundamental reason why this is happening. – whatwhatwhat Aug 17 '15 at 17:32
  • You need to post a different question, like "My query has ORDER BY but Report Builder shows the results in a different order." <--- not that wording exactly, but you're asking a different question than the one you've asked above. – Aaron Bertrand Aug 17 '15 at 17:32
  • 1
    As has been mentioned many times on this page already, the order changes because whatever query Report Builder ultimately submits to SQL Server in each case is different enough that it generates a different plan, which leads to a different order, because *report builder* isn't submitting an ORDER BY. (Or, potentially, report builder is collecting the results in one order but displaying them in a different order. That'd be one for the report builder folks or Microsoft support, because that shouldn't be the way that tool works.) – Aaron Bertrand Aug 17 '15 at 17:34
  • 1
    I bet you a 500 rep bounty that if you take your query (with the ORDER BY) and run it in a Management Studio query window, whether you use = or LIKE, Management Studio will ***ALWAYS*** return in the order of your ORDER BY. (And I can wait until you earn 500 rep before you pay up :-).) Meaning the problem is in whatever Report Builder is submitting on your behalf (which you can validate doesn't have an ORDER BY, or has a different ORDER BY than what you expect, using trace / extended events). – Aaron Bertrand Aug 17 '15 at 17:36
  • SSRS is going to present the data as it receives it from the database. You have the option of having the database sort your data. You also have the option of having SSRS sort your data. The database engine *likely* is going to be more efficient at doing so as it could leverage existing indexes to do this. SSRS will have to sort the data in memory every time (I'm ignoring caching option) the report is requested. Either way, the results of a query are a set which by definition is unordered. If you have a specific order in mind, you *must* order it. Pick one of the two options and make it so. – billinkc Aug 17 '15 at 17:42
  • @billinkc what do you mean by "the database" and "SSRS"? By SSRS do you mean the Report Builder? – whatwhatwhat Aug 17 '15 at 17:44
  • ReportBuilder is a click-once application that allows you to create SQL Server Reporting Services, SSRS, reports. SSRS is the engine that takes the report definition (layout, aggregations, etc) that you design with report builder and applies the data from the source to render the output in html/excel/etc. The database is your SQL Server - the thing you've defined in your data sources for the report – billinkc Aug 17 '15 at 17:48

2 Answers2

0

When you are using something like "LN bm%", here "%" means that anything can come after this. It means that is open for any kind of words so in this case = is the real way to use. because you have mentioned that it should start with "LN bm" and any keyword after that is possible :)

Akmal Arzhang
  • 122
  • 1
  • 9
0

If you haven't specify any sorting for the query, the records will come in the order that the database finds them.

Changing an operator can result in a different execution plan. That means that the data will use different methods to find the right data, and the rows will be returned in an order depending on how those methods process the data.

An index scan for example would produce rows in the order of that index, but a table scan would produce rows in the order that they happen to be stored in the table.

Even for the same query the execution plan can change. The database gathers statistics about the data which it uses to choose the execution plan, so it may choose a different plan because it has got better knowledge about the data.

Without specifying the sorting for the query, you can simply never be sure what the order will be, or even that it will stay the same.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005