[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

order by when there is nothing natural to order by

My sample data looks something like this


1 aaaa
2 zzzz
3 dddd

there is not a natural way of ordering so i can't order by the id or the name.  Theirfore, my query needs to determine the order so it would returns something like

2 zzz
1 aaa
3 ddd

Where i would determine in my SQL what the order should be/
0
vbnetcoder
Asked:
vbnetcoder
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>it would returns something like
Define for us the logic behind how that sort order is achieved.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Add a new column (SortOrder Varchar(5)) where you can enter 01, 02, etc. to sort your data using this column.

Mike
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
WIthout any further details, you can use CASE in an ORDER BY clause to pull off a custom sort order.
For example...
CREATE TABLE #tmp (val varchar(10)) 
INSERT INTO #tmp (val) 
VALUES ('1 aaa'), ('2 zzz'), ('3 ddd'), ('goo'), ('foo'), ('boo')

SELECT val
FROM #tmp
ORDER BY CASE LEFT(val, 1) WHEN '2' THEN 1 WHEN '1' THEN 2 WHEN '3' THEN 3 ELSE 4 END

Open in new window


For more reading check out my article on SQL Server CASE Solutions, scroll down about halfway.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Dave BaldwinFixer of ProblemsCommented:
'order by' works with some column or field in the result set.  Either method above would work Ok.  The biggest problem is that if you are going to use some order that is not numeric or alpha, then you have to manually define that order and find a way to use it.  On a large table, that can get pretty time consuming if it changes very often.
0
 
PortletPaulCommented:
well I don't agree there isn't a "natural way to order" that sample data,
  it would either be numeric by id:  ORDER BY ID
  or alphabetic by name then numeric by id: ORDER BY NAME, ID

what you fail to explain at all is the "unnatural" method of order that results in:

2 zzz
1 aaa
3 ddd

that is neither numeric nor alphabetic, and without any explained logic

perhaps: ORDER BY CHECKSUM(NEWID())

(a random order)
using this 4 times produced these results:
| ID | NAME |
|----|------|
|  3 | dddd |
|  1 | aaaa |
|  2 | zzzz |


| ID | NAME |
|----|------|
|  2 | zzzz |
|  1 | aaaa |
|  3 | dddd |


| ID | NAME |
|----|------|
|  1 | aaaa |
|  2 | zzzz |
|  3 | dddd |


| ID | NAME |
|----|------|
|  1 | aaaa |
|  3 | dddd |
|  2 | zzzz |

Open in new window


details
[SQL Fiddle][1]

**MS SQL Server 2008 Schema Setup**:

    
    
    CREATE TABLE Table1
    	([ID] int, [Name] varchar(40))
    ;
    	
    INSERT INTO Table1
    	([ID], [Name])
    VALUES
    	(1, 'aaaa'),
    	(2, 'zzzz'),
    	(3, 'dddd')
    ;

**Query 1**:

    select
    id, name
    from table1
    
    ORDER BY CHECKSUM(NEWID())
    

**[Results][2]**:
    
    | ID | NAME |
    |----|------|
    |  3 | dddd |
    |  1 | aaaa |
    |  2 | zzzz |


**Query 2**:

    select
    id, name
    from table1
    
    ORDER BY CHECKSUM(NEWID())
    

**[Results][3]**:
    
    | ID | NAME |
    |----|------|
    |  2 | zzzz |
    |  1 | aaaa |
    |  3 | dddd |


**Query 3**:

    select
    id, name
    from table1
    
    ORDER BY CHECKSUM(NEWID())
    

**[Results][4]**:
    
    | ID | NAME |
    |----|------|
    |  1 | aaaa |
    |  2 | zzzz |
    |  3 | dddd |


**Query 4**:

    select
    id, name
    from table1
    
    ORDER BY CHECKSUM(NEWID())
    

**[Results][5]**:
    
    | ID | NAME |
    |----|------|
    |  1 | aaaa |
    |  3 | dddd |
    |  2 | zzzz |



  [1]: http://sqlfiddle.com/#!3/c8427/1

  [2]: http://sqlfiddle.com/#!3/c8427/1/0

  [3]: http://sqlfiddle.com/#!3/c8427/1/1

  [4]: http://sqlfiddle.com/#!3/c8427/1/2

  [5]: http://sqlfiddle.com/#!3/c8427/1/3

Open in new window

0
 
vbnetcoderAuthor Commented:
ty
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now