vbnetcoder
asked on
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/
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/
Add a new column (SortOrder Varchar(5)) where you can enter 01, 02, etc. to sort your data using this column.
Mike
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
'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.
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:
details
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 |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/3ASKER
ty
Define for us the logic behind how that sort order is achieved.