Predefined sort order

Hi
is there a way of sorting elements in a predefined order like
select * from currencies
order by ('eur', 'can' ,'yen', *)
so that eur, can,yen comes first and then the rest?
(without an sp)
greets mikeTry something like this:
select <columns>
,PrimarySortOrder
= case <currency column>
when 'eur' then 100
when 'can' then 200
when 'yen' then 300
else 999
end
from currencies
order by PrimarySortOrder
,<currency column>
ML
http://milambda.blogspot.com/|||peppi911@.hotmail.com wrote on 5 Jan 2006 02:20:44 -0800:

> Hi
> is there a way of sorting elements in a predefined order like
> select * from currencies
> order by ('eur', 'can' ,'yen', *)
> so that eur, can,yen comes first and then the rest?
> (without an sp)
You haven't provided DDL, so I'm going to make it up as I go along, you'll
have to edit to fit your database.
How about using CASE? Assuming the column to sort by is called currency:
SELECT * FROM currencies
ORDER BY
CASE WHEN currency = 'eur' THEN 1
ELSE WHEN currency = 'can' THEN 2
ELSE WHEN currency = 'yen' THEN 3
ELSE 4
END
If you end up having a long list of currencies to sort by this will end up
getting messy. You could do this using a joined table to make it much easier
to define a long list of currencies to sort by, and easily change the sort
order without changing your query.
CREATE TABLE [dbo].[currencysort] (
[currency] [varchar] (3) NOT NULL ,
[sort] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[currencysort] ADD
CONSTRAINT [PK_currencysort] PRIMARY KEY CLUSTERED
(
[currency]
) ON [PRIMARY]
GO
(I've been lazy and used the EM script tables wizard).
INSERT INTO currencysort VALUES('eur',1)
INSERT INTO currencysort VALUES('can',2)
INSERT INTO currencysort VALUES('yen',3)
Now, assuming that you have a field called currency in your currencies
table, you can do something like this:
SELECT currencies.*
FROM currencies LEFT JOIN currencysort ON currencies.currency =
currencysort.currency
ORDER BY COALESCE(currencysort.sort,99)
There are likely much more efficient ways to do this, but someone else can
come up with those :)
Dan|||create table #t
(
[id] int not null primary key,
col varchar(10)
)
insert into #t values (1,'dol')
insert into #t values (2,'rub')
insert into #t values (3,'can')
insert into #t values (4,'yen')
insert into #t values (5,'shek')
insert into #t values (6,'pnd')
insert into #t values (7,'krn')
insert into #t values (8,'eur')
--> order by ('eur', 'can' ,'yen', *)
select * from #t
order by
case when col = 'eur' then 2
when col = 'can' then 1
when col = 'yen' then 0
end desc
<peppi911@.hotmail.com> wrote in message
news:1136456444.177808.131830@.z14g2000cwz.googlegroups.com...
> Hi
> is there a way of sorting elements in a predefined order like
> select * from currencies
> order by ('eur', 'can' ,'yen', *)
> so that eur, can,yen comes first and then the rest?
> (without an sp)
>
> greets mike
>|||Hello!
thanks for your help, thats brilliant!
The page is already working!
regards,
mikesql

post SP1 hotfix

Just curious, I'm reading about the SQL Server 2005 post SP1 hotfix and it looks like you need to install the packages in order:

SQL Server

Analysis Services

Integration Services

Notification Services

Reporting Services

Tools

My desktop is configured as an Analysis Services and Integration Services instance, but without the SQL Server proper components installed. What should my install order be? Does important note 3 about SMO and SQL-DMO still apply?

Thanks,

Keehan

What document do you refer to?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Sorry 'bout that:

http://support.microsoft.com/kb/918222

Cheers,

Keehan

|||

If you didnt install SQL Server relational engine, you should be fine with installing hotfix packges for installed components only.

Looks SQL-DMO issue only applies if you have SQL Server relational engine installed.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Possible Bug Using TOP and Paging via a Temp Table

This is not a "bug" per se, as I know that the order of rows in SQL
Server isn't guaranteed to be consistent unless ORDER BY is specified.
Still, this is somewhat odd behavior.
This involves paging logic via parameterized queries. Since I can't
post my client's DDL, I have used northwind to duplicate the issue.
Code:
--vars to simulate paging
DECLARE
@.start int,
@.end int
--create temp table
CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
NULL, [Orderdate] [DateTime] NOT NULL)
SELECT @.start = 0 /*****CHANGE ME*****/
SELECT @.end = 12 /*****CHANGE ME*****/
--insert rows
INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
ShipAddress,Orderdate)
SELECT DISTINCT
TOP 12 /*****CHANGE ME*****/
CustomerID,
ShipVia,
ShipName,
ShipAddress,
Orderdate
FROM
ORDERS
WHERE
CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
ORDER BY
CustomerId
--select the page of data
SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
--select to see the whole temp table
--SELECT * FROM #TempTable
DROP TABLE #TempTable
1. Run the query as is. Notice that there are 3 rows returned where
ShipVia = 2.
2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
24". Notice that although the __rowcnt selection correctly returns
13-24, the rows are the same.
3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
36". The page of data now changes (I believe because the customerID
changes on this page).
4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
of data returned are now "correct"--the data returned is different for
each page.
You can uncomment the final select statement to see what is actually
going into the temp table.
It seems that the TOP statement is somehow causing the rows to be added
to the temp table in reverse order. Apparently, the "correct" rows are
returned using an unpatched version of SQL Server.
Anyone know what might cause this to happen?
Thanks for any insight,
PhilHi
Add a few 100 thousand rows to this, plus a machine with 4 processors and a
lot of RAM and the query performs different again. Even a different OS.
As the row count in your example increases, there is probably a different
query plan/spool to disk occurring.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>|||There are some better approaches to paging that don't exhibit these
symptoms.
http://www.aspfaq.com/2120
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>