InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 05:23:46 GMT20http://www.sqlservercentral.com/Forums/Topic1205019-90-1.aspx[quote][b]Dineshbabu (1/8/2013)[/b][hr]Often i will get an error stating, Columns used in ORder By was not listed in SELECT. Can anyone xplain when situtation arises..[/quote]Sorry I got the answer in the error message itself..On using DISTINCT Clause..Tue, 08 Jan 2013 05:50:23 GMTDineshbabuhttp://www.sqlservercentral.com/Forums/Topic1205019-90-1.aspxOften i will get an error stating, Columns used in ORder By was not listed in SELECT. Can anyone xplain when situtation arises..Tue, 08 Jan 2013 05:48:26 GMTDineshbabuhttp://www.sqlservercentral.com/Forums/Topic1205019-90-1.aspxHelp.Okay, so I'm reading through, and decided to try the case sensitive sorts:"I’ll leave it up to you to run the SELECT statement in Listing 3 to show how the cars with a make of “Ford” will be sorted based on case."And I refer back to [u][url=http://msdn.microsoft.com/en-us/library/ms143515%28v=sql.105%29.aspx]Windows Collation Sorting Styles[/url][/u], and it indicates that "lowercase letters sort ahead of their uppercase versions." Great, so all lower case 'c's should appear before upper case 'C's. I added a couple extra entries to the same data, just so I could see it. The results were... not what I expected.[code="sql"]Make Model NumOfDoors ModelYear Color-------------------------------------------------- -------------------------------------------------- ---------- --------- --------------------------------------------------Chevrolet Volt 4 2010 RedChevrolet Camaro 4 1978 GreenChevrolet Cruse 4 2012 Redchevrolet Aveo 4 2011 WhiteChevy Nova 3 1977 Whitechevy Caprice 4 1984 BlueFord Focus 4 2012 GreenFord Mustang 2 1964 RedFord Taurus 5 1995 Whiteford F250 2 2010 Blueford F150 2 2010 RedHonda Civic 4 2012 RedToyota Camry 4 2011 RedToyota Rav4 5 2000 Red[/code]So, I expected all the lower case 'c's to be sorted together (as if lower case 'c' were a different letter, that came slightly before capital 'C' in the alphabet... it would appear that Upper Case letters sort first. (And why wouldn't these sort as 'chevrolet', 'chevy', 'Chevrolet', 'Chevy'?) The query run is Listing 3... with slight changes in the data.Is the documentation wrong, or have I participated in too many Father's Day activities in the bright Maryland sun? (I assume it doesn't make any difference, but I'm using SQL 2008 R2 Dev Ed. (10.50.2500)Sun, 17 Jun 2012 17:26:10 GMTRich Weisslerhttp://www.sqlservercentral.com/Forums/Topic1205019-90-1.aspxNice Article Greg!I think it would be good if you quote it as a bad practice for ‘Sorting Data based on [b]Column Ordinal Position[/b]’. I understand you just wanted to demonstrate all the possible ways to sort the resultset but it’s a bad practice and we shouldn’t encourage it.Overall nice work! Keep it up!!! :-)Mon, 26 Dec 2011 01:04:38 GMTDevhttp://www.sqlservercentral.com/Forums/Topic1205019-90-1.aspxGreg/Robert, just found a great example of the cost of order by here at work. I was wondering what you meant exactly, now I totally get it. Thanks!Wed, 14 Dec 2011 13:59:30 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic1205019-90-1.aspxYou both have excellent points. It is true the heading is a little misleading regarding sorting numeric data. I don't know how many times I've seen people putting numeric data in a character data type and then wonder why the ORDER BY clause reported that "10" came before "2". I would hope people use a little common sense and do not use the ORDER BY clause if they don't need the data sorted. But then again it is always good to remind people that sorting data when you don't need it comes at a cost. Thank you for sharing.GregWed, 14 Dec 2011 13:46:01 GMTGreg Larsenhttp://www.sqlservercentral.com/Forums/Topic1205019-90-1.aspxNice article, but I would have mentioned somewhere the [b]COST[/b] of using the ORDER BY clause, perhaps demonstrating how SQL Server won't return any rows at all until the worktables have been built in order to enforce the ORDER BY clause.When I demonstrate the cost of the ORDER BY clause to new developers, I often show a simple query first run w/o an ORDER BY and then adding an ORDER BY clause to demonstrate the difference in Management Studio. I think this is important for developers to understand. I've always told them to [b]NEVER[/b] use an ORDER BY clause unless they needed server-side, ordered data becuase of the extra work SQL Server has to do in order to sort the data for you.Wed, 14 Dec 2011 07:18:59 GMTRobert A. Shannon IIIhttp://www.sqlservercentral.com/Forums/Topic1205019-90-1.aspxFirst, nice article, great coverage of the topic. Couple things worth mentioning: 1 - [quote]One reason why you might use the ordinal position in your ORDER BY clause is when the select list column you want to sort on contains a complicated expression, such as a CASE expression. [/quote] In that case, you should just alias the column, then use the alias in the ORDER BY rather than using ordinal position. Still allows you to change columns, saves having to rewrite the CASE2 - your "numeric data sorting alphabetically" is misleading, that's not numeric data. Just because you're stuffing number characters into a string and calling it Number doesn't make it anything but character data. Character data should properly sort based on the value of the character, not the "intent" of the data. One more reason to store data in correct datatypes.Wed, 14 Dec 2011 07:06:49 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic1205019-90-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Stairway+Series/75778/">Stairway to T-SQL DML Level 6: The Basics of Joining Tables and Record Sets</A>[/B]Mon, 14 Nov 2011 04:12:56 GMTGreg Larsen