order by

hi i have the following
create table #station_mix
(
station varchar(100)
--several other columns
)
insert into #station_mix values('qtq')
insert into #station_mix values('nws')
insert into #station_mix values('stw')
insert into #station_mix values('tcn')
insert into #station_mix values('gtv')
when i do a select * from #station_mix i want the output to be in the order.
'tcn', 'gtv', 'qtq', 'nws','stw'
how do i do this?
Thanks
ICHORHi ichor,
since you havent got any sort crteria in the name itself, you need some
other column to do the ordering:
create table #station_mix (
station varchar(100),
order_no INT
--several other columns)
insert into #station_mix (station, order_no) values('qtq', 2)
insert into #station_mix (station, order_no) values('nws', 3)
insert into #station_mix (station, order_no) values('stw', 4)
insert into #station_mix (station, order_no) values('tcn', 0)
insert into #station_mix (station, order_no) values('gtv', 1)
select * from #station_mix order by order_no
Micha
"ichor" <ichor@.hotmail.com> schrieb im Newsbeitrag
news:OzQacBbkFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the
> order.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
>
>|||Hi,
Which order you need to get the output. If it is alphabetical order why dont
you try ORDER BY clause.
Incase if you cant use order by clause then , create a CLUSTERED index on
station column
Thanks
Hari
SQL Server MVP
"ichor" <ichor@.hotmail.com> wrote in message
news:OzQacBbkFHA.3380@.TK2MSFTNGP12.phx.gbl...
> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the
> order.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
>
>|||Any reason you want this order?
One way is
Select station from #station_mix
Order by case when station ='tcn' then 1 when station ='gtv' then 2
when station ='qtq' then 3 when station ='nws' then 4
when station ='stw' then 5|||ichor wrote:

> when i do a select * from #station_mix i want the output to be in the
> order.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
You could add a "Priority" column to #station_mix, then Order By the
Priority column.
create table #station_mix
(
station varchar(100),
priority int
)
insert into #station_mix values('qtq', 3)
insert into #station_mix values('nws', 4)
insert into #station_mix values('stw', 5)
insert into #station_mix values('tcn', 1)
insert into #station_mix values('gtv', 2)
select station from #station_mix order by priority
Ben|||Hi
I can't see that these are in any specific order, is there another column
that determines this order? If not try
SELECT station
FROM (
SELECT station, CASE station WHEN 'tcn' THEN 1
WHEN 'gtv' THEN 2
WHEN 'qtq' THEN 3
WHEN 'nws' THEN 4
WHEN 'stw' THEN 5
ELSE 6
END AS Orderby
FROM #station_mix ) A
ORDER BY Orderby
John
"ichor" wrote:

> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the orde
r.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR
>
>|||>Incase if you cant use order by clause then , create a CLUSTERED index on
>station column
Hari,
As you probably know, this will not always guarantee that the rows will be
returned in the
order of the Clustered Index.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:%23YZ$9QbkFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Which order you need to get the output. If it is alphabetical order why
> dont you try ORDER BY clause.
> Incase if you cant use order by clause then , create a CLUSTERED index on
> station column
> Thanks
> Hari
> SQL Server MVP
>
> "ichor" <ichor@.hotmail.com> wrote in message
> news:OzQacBbkFHA.3380@.TK2MSFTNGP12.phx.gbl...
>|||Ichor,
In case you didn't get the message from the other replies: the only way
to guarantee a specific output order is to use the ORDER BY clause.
In the ORDER BY clause you can specify a column name (for example if you
want the output to be sorted alphabetically) or an expression (for
example a CASE .. WHEN expression, as demonstrated in Madhivanan's
reply). This will specify the values that are used for sorting.
In addition to that, you can specify a Collation and whether the result
should be sorted ASCending or DESCending. By default, the column's
default collation is used, or the database default collation, and the
result is sorted ascending. The collation determines the sorting rules,
for example whether the ordering should be case sensitive or not.
Checkout BOL for further details.
Hope this helps,
Gert-Jan
ichor wrote:
> hi i have the following
> create table #station_mix
> (
> station varchar(100)
> --several other columns
> )
> insert into #station_mix values('qtq')
> insert into #station_mix values('nws')
> insert into #station_mix values('stw')
> insert into #station_mix values('tcn')
> insert into #station_mix values('gtv')
> when i do a select * from #station_mix i want the output to be in the orde
r.
> 'tcn', 'gtv', 'qtq', 'nws','stw'
> how do i do this?
> Thanks
> ICHOR