SQLServerCentral / Discuss Content Posted by Andy Warren / Article Discussions / Article Discussions by Author / Worst Practice - Sorting by Ordinal / Latest Posts

RE: Worst Practice - Sorting by Ordinal

T. C. — Mon, 01 Jan 2007 11:52:00 GMT

I have on occasion used an ordinal for sorting, only for adhoc stuff though, not production code.

RE: Worst Practice - Sorting by Ordinal

troy fernando — Thu, 02 Nov 2006 19:23:00 GMT

i think it will not hurt to use it for small tables.

RE: Worst Practice - Sorting by Ordinal

swarm — Mon, 07 Oct 2002 10:49:00 GMT

Actually, I didn't know there's such a statement. I have lots of sql books and none of them ever mentions such statement. Maybe because it's a bad, bad one.Anyways, thanks for making a newbie aware of it.

RE: Worst Practice - Sorting by Ordinal

Andy Warren — Tue, 01 Oct 2002 18:07:00 GMT

Thanks for the comments!Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Worst Practice - Sorting by Ordinal

vyaskn — Tue, 01 Oct 2002 06:47:00 GMT

It is a worst practice when it comes to production class code. But it sure is a power users tool and a shortcut for ad hoc queries. When I am working on a problem that requires me to run a bunch of SELECT statements, I am not going to type the column names in ORDER BY clause. I'll simply use ordinal numbers.Here's another reason why, we shouldn't use ordinal position in production code:SELECT id, nameFROM sysobjectsORDER BY 1.2Notice the dot between 1 and 2. It's a typo, it should actually be a comma. But the statement still works, except that the output is not sorted as you'd expect, there by introducing a bug.And no, there are no performance differences.HTH,Vyashttp://vyaskn.tripod.com/Edited by - vyaskn on 10/01/2002 07:03:08 AM

RE: Worst Practice - Sorting by Ordinal

Antares686 — Tue, 01 Oct 2002 04:00:00 GMT

My opinion is better safe than sorry. So what if you have to do more maintainence, if someone comes along later who doesn't understand ordinal order by's then you end up causing the enduser grief if he displaces a column that was sorted on and should remain that way. And anyway, how often would you really want to change the order by."Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

RE: Worst Practice - Sorting by Ordinal

Andy Warren — Mon, 30 Sep 2002 09:16:00 GMT

Totally agree. I'd be afraid to test for fear it would be faster!Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Worst Practice - Sorting by Ordinal

BobAtDBS — Mon, 30 Sep 2002 07:10:00 GMT

I don't like the ordinal order by, but for a different reason. I would hope that if you are altering the Select portion, you'd have enough sense to look at the entire statement anyway.I don't like it because I like code to be self documenting where ever possible. Order by CustomerNum, DateInvoiced is a hell of a lot clearer than Order by 3, 4 in my book. I could get out another soapbox and talk about using clearly named field names, but that belongs in someone else's article!Just for discussion purposes, has anyone looked at any performance differences using ordinals? I haven't, and unless it was very significant, I still wouldn't use them due to the clarity issue.Student of SQL and Golf, Master of Neither

Worst Practice - Sorting by Ordinal

Andy Warren — Wed, 25 Sep 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/worstpracticesortingbyordinal.asp>http://www.sqlservercentral.com/columnists/awarren/worstpracticesortingbyordinal.asp