Sort column without ORDER BY
-
Note that in SQL 2005, that is no longer the case. If you're counting on that in existing 2000 implementations, you've got some refactoring to do before an upgrade.
-Eddie
Eddie Wuerch
MCM: SQL -
Dinendra,
The question remains... what are you doing where you think it might be a good idea to find a way to sort without using ORDER BY?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I think it is a trick question from an interview.
N 56°04'39.16"
E 12°55'05.25" -
Heh... could be... the correct answer is "don't sort in a view".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I was joking B-)
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
The reason is tables are not ordered by definition and views are at the same category (RDBMS), if you need an ordered results you must specify "order by".
The solution of clustered index is not an option for you since it's not assured that the result will be ordered (for example parallelism).
The best option you have for SQL Server 2000 is to run the top 100 percent with order by.
-
This is one of the most silly questions I have ever seen. If you want a column sorted for output you use ORDER BY in the SELECT statement period.
It would be like asking how can I get oxygen into my lungs without breathing.
Steve
-
Heh... Oxygenated Hydro-Carbon intebation.... at least THAT can be done
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Today i had an interview with one of the social media sites withe exact same question. The only question they asked me was how do you sort a column without using ORDER BY clause? They said it is database agnostic question. They were expecting a SQL statement.
Could someone please take a stab at this?
Probably there is a solution for this but looks like they wanted to see the problem solving approach.
create table test_sort(id numeric, amt numeric);
insert into test_sort values(10, 100);
insert into test_sort values(20, 300);
insert into test_sort values(30, 50);
insert into test_sort values(40, 10);
insert into test_sort values(50, 400);Expected output for AMT column:
10
50
100
300
400 - ravikanth_b - Wednesday, June 27, 2018 6:49 PM
It seems your interviewer is not very good with databases him/her-self.
Ask for their solution, post it here and give them the link to this page.
I'm sure a day or 2 later you'll find here a script proving their solution does not work._____________
Code for TallyGenerator -
I was patiently trying to work with her with possible solution but you have to see the arrogance of the interviewer 🙂
With this one question that person came to conclusion that i am good for nothing SQL person and did not hire me. That's it, one question my entire experience whatever worth is nullified. Thankfully my self image is not that fragile 🙂Had i been on the other side i would have eaten her alive with gotcha questions too. These days there is no objectivity in the interview process. If a candidate did not answer the next greatest thing the interviewer currently working on which he himself/herself not aware of 2 days ago, they think candidate is dumb.
Anyways...
-
Yes. It can actually be done rather reliably but MS will never support it. You need to do several things...
1. The table must have a Clustered Index that is keyed in the order of the return you want. Doing the same thing with a non-clustered index is not reliable because you could end up with a "merry-go-round" return. It's just the nature of non-clustered indexes.
2. You must not have a JOIN of any kind in the query.
3. You must have exclusive use of the table.
4. You must use a committed transaction isolation level.
5. You must use MAXDOP 1 to prevent parallelism, which could mess up the sort order.
6. You must use and index hint to force the usage of the Clustered Index. A more narrow non-clustered index will come into play if the criteria is right for it unless you specify the index hint to force the usage of the Clustered Index.
7. You must write the query in such a fashion that it will cause a range scan if not a full index scan.
8. The table should not be partitioned.
9. I don't know if compression will cause a problem or not but would recommend against it until you test it on multiple tables.
10. It has to be a rowstore table (Clustered Index).
11. The Clustered Index must be unique or ties could swap the rest of the return.Still and with just one exception which is no longer necessary since 2012 came out, there is no reason to use such trickery. If you use an ORDER BY and SQL Server decides to use a proper range scan on the Clustered Index, the ORDER BY will not appear in the execution plan. The ORDER BY will also hedge against any future changes in the Clustered Index (including conversions to column store, conversion to partitioned tables/views, changes to uniqueness, crazy changes to the optimizer (thankfully, not yet here), etc, etc) and is guaranteed/supported by MS... except on super blue blood moon days when the tide is going out instead of at quiet flood stage. 😀
In theory, a HEAP will return in the correct order without an ORDER BY but, in practicality it will not because there is no guarantee against forwarded rows. And, it's not actually database agnostic because the methods of forcing MAXDOP 1 and forcing the guaranteed usage and range scan of the Clustered Index can vary. If the interviewer was looking for just the typical but grossly incorrect "Just add a clustered index in the desired order" or "just us a heap", then that person is an idiot that you probably didn't want to work for anyway.
There is another way to get the return in sorted order without an ORDER BY and that's to use a GROUP BY with ROLLUP with criteria that excludes the grouped totals... but it's also not guaranteed to occur correctly without an ORDER BY.
Except for one instance that shouldn't be used after 2012, this is all an academic discussion because returning something in sorted order without an ORDER BY isn't supported by MS and the interviewer should know that.
Of course, the interviewer is "always right" and, if you actually want or need the job, you do have to learn how to play their game very quickly, jump through the hoop they want you to, and then, just in case they were just testing your suck-up factor to see if you'd stand your ground, prove that they're wrong in a mentor-like fashion.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - ravikanth_b - Wednesday, June 27, 2018 10:26 PM
Heh... do you know how to get the current date and time using T-SQL??? 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If you add a ROW_NUMBER() it will return in the sort order of the ROW_NUMBER.
Technically, it still has an ORDER BY, but it's not directly on the field.SELECT ts.id, ts.amt, rn = ROW_NUMBER() OVER(ORDER BY amt)
FROM #test_sort AS ts - DesNorton - Wednesday, June 27, 2018 11:52 PM
If that's the answer they were looking for, then I'd not want to work for them.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply