Order by?

Hi All,
I have a question in sql...
How can i sort a select statement depending on nvarchar not on Int ??
My select statement is :
" select * from table1 order by st_name asc"
can anyone help me?
thanks a lot

Hello,
Sql statement "select * from table1 order by st_name" will work.
It doesnot matter whether field is nvarchar or int.
It will work for both.
|||

The database doesn't care what data type it is, just the field name to sort by. Of course, a number stored as nvarchar will sort differently than of type int, so that is a disadvantage if that is your situation.

|||What is the datatype for the fieldst_name?
If the datatype is anvarchar, it will order by alpha-numeric, ifASC (ascending)
For example:
- 101 ways
- 20 cars
- 20 boats
- apple
- boy
and of course int datatype is numerical order

sql

Order by with select into

I have a Microsoft SQL Server 7.0.

I wrote a sql command that creates a temporary table with a ORDER BY
clause.

When a execute a SELECT on this temporary table sometimes the result is
ok, but sometimes is not ordered. I didnt see anything like that. Any
clue?

Is there any kind of limits with temporary tables ? Because the command
that creates the temporary table is working and the rsults is always
ordered. But when I create a table with it, sometimes the table is not
ordered.

Paulo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Paulo Andre Ortega Ribeiro" <anonymous@.devdex.com> wrote in message
news:3f0b0ac2$0$202$75868355@.news.frii.net...
> I have a Microsoft SQL Server 7.0.
> I wrote a sql command that creates a temporary table with a ORDER BY
> clause.
> When a execute a SELECT on this temporary table sometimes the result is
> ok, but sometimes is not ordered. I didnt see anything like that. Any
> clue?
> Is there any kind of limits with temporary tables ? Because the command
> that creates the temporary table is working and the rsults is always
> ordered. But when I create a table with it, sometimes the table is not
> ordered.
> Paulo

Rows in tables (temporary or permanent) never have an order, even if you
used ORDER BY when you did the INSERT, and even if there's a clustered index
on the table. The only way to be sure you get ordered data is to use ORDER
BY when you SELECT it.

Some tables, especially with clustered indexes, may look like the data is
ordered, but you can't assume it will always work. You could think of it
like this - when you SELECT from the table, you create a result set, and
ORDER BY only works on the result set, not on the table.

Is there some specific reason that you want to order data in the table,
instead of using ORDER BY in your queries? If there is, then maybe you could
give some more details about what you are trying to do, and someone might be
able to suggest a different solution.

Simon|||Paulo,

Tables do not have an order. In other words, tables by definition are
logically an unordered set of rows. Using ORDER BY in a INSERT...SELECT or
SELECT...INTO does not mean that the data in the table is 'ordered'. The
order of rows which you see when you do a SELECT without an ORDER BY clause
is a undefined/arbitrary order chosen by the optimizer based on the physical
characteristics, indexes, access paths, complexity of joins if any, other
statistical information etc. & many undocumented factors. Hence you should
not rely on the 'order' of rows in a table which you see on the screen.

To repeat, rows in a table do not have a logical order. The only guaranteed
way of retrieving rows in a specific order is to use an ORDER BY clause in
your SELECT statement.

--
- Anith
( Please reply to newsgroups only )sql

ORDER BY with SELECT DISTINCT

Hi,
I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an error
message saying that the ORDER BY needs to be included in the SELECT
statemeny.
Does anyone have a sample on how to sort a SELECT DISTINCT query ?
Niclas"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:ubdCBujQGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an
> error message saying that the ORDER BY needs to be included in the SELECT
> statemeny.
> Does anyone have a sample on how to sort a SELECT DISTINCT query ?
> Niclas
>
The columns you want to order on have to be included in the SELECT list.
Here's why:
CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
(x,z));
INSERT INTO tbl (x,z) VALUES (100,1);
INSERT INTO tbl (x,z) VALUES (100,2);
INSERT INTO tbl (x,z) VALUES (200,0);
INSERT INTO tbl (x,z) VALUES (200,4);
SELECT DISTINCT x FROM tbl ORDER BY z;
Result:
Server: Msg 145, Level 15, State 1, Line 9
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
Can you explain how SQL Server could return X ordered by Z in this example?
Should 100 come first or should 200 come first? There is no single answer so
that's why it has to be disallowed unless Z is in the SELECT list. Example:
SELECT DISTINCT x,z FROM tbl ORDER BY z;
The problem is with your specification rather than with SQL Server. You
haven't given us a clue about what you really want to sort on so here are a
couple of possibilities using the above example data. Notice you'll get two
different orders:
SELECT x
FROM tbl
GROUP BY x
ORDER BY MIN(z);
x
--
200
100
(2 row(s) affected)
SELECT x
FROM tbl
GROUP BY x
ORDER BY MAX(z);
x
--
100
200
(2 row(s) affected)
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

order by values in IN clause

Hi All,
I have a problem [stated below].
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('2001','23456789') GROUP BY TS
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('23456789','2001') GROUP BY TS
both return same result set.
$99,200.00
$4,343.00
I need to order the result set by the values that I give in the IN
clause. Is this possible?Anybody could please explain why this is
happening and what would be the remedy.
Thanks in advance.
Thanks & Regards,
Shankar.> I need to order the result set by the values that I give in the IN
> clause. Is this possible?Anybody could please explain why this is
> happening and what would be the remedy.
SQL Server is free to return results in any order unless you specify ORDER
BY. This is regardless of the order of values in your IN clause, order of
joined tables, table indexes, etc.
One solution is to add an additional value for the desired sequencing so
that you can specify ORDER BY. The example below uses a derived table:
SELECT '$'+CONVERT(VARCHAR,SUM(mMoney),1)
FROM trans
JOIN (SELECT '2001' AS TS, 1 AS Seq
UNION ALL SELECT '23456789', 2) AS trans_list
ON trans.TS = trans_list.TS
GROUP BY trans_list.TS, trans_list.Seq
ORDER BY trans_list.Seq
BTW, formatting data for display purposes is a job best done in the
presentation layer rather than in the database. That approach is more
scalable. Also, front-end tools (e.g. Reporting Services) provide much
richer formatting capability than Transact-SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
<shankararaman.s@.gmail.com> wrote in message
news:1146824029.416992.301160@.j73g2000cwa.googlegroups.com...
> Hi All,
> I have a problem [stated below].
> select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
> ('2001','23456789') GROUP BY TS
> select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
> ('23456789','2001') GROUP BY TS
> both return same result set.
> $99,200.00
> $4,343.00
> I need to order the result set by the values that I give in the IN
> clause. Is this possible?Anybody could please explain why this is
> happening and what would be the remedy.
>
> Thanks in advance.
> Thanks & Regards,
> Shankar.
>|||try this.
select '$'+CONVERT(VARCHAR,SUM(mMoney),1) from trans where TS IN
('23456789','2001') GROUP BY TS
order by TS|||Also format the data in your front end application
Madhivanansql

ORDER BY using a list of elements

Hi,
I have to order a select in a special way, just using the name of the product. Can I use a list of the names of the products to do the ordering?
like "product 1", product 3", "product 5", "product 2", "product 4"...
The list of the products it's not so large and I could write them in a simple sentence. I'm working with Microsoft SQL Server 2000.

Thanks & regardsHow about a CASE statement?

ORDER BY
CASE WHEN name='product 1' THEN 1
WHEN name='product 3' THEN 2
...
END;

Or, perhaps better, add a new column like PRODUCT_PRIORITY to the table and order by that.

order by slows me down

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Any ideas?
TIA, ChrisChris,
The Top Engine simply fetches the 1st occurance, satisfying your where
criteria.
When you issue an Order BY Clause it have to construct a virutal table in
Tempdb and then sort and give you the 1st occurance.
To speed up I would look at the index plan and add/tweak the index
satisfying the Order By and Where.
HTH
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:219f01c3e079$0670b460$a501280a@.phx.gbl...

quote:

---

> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Any ideas?
> TIA, Chris

---

order by slows me down

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Any ideas?
TIA, ChrisChris,
The Top Engine simply fetches the 1st occurance, satisfying your where
criteria.
When you issue an Order BY Clause it have to construct a virutal table in
Tempdb and then sort and give you the 1st occurance.
To speed up I would look at the index plan and add/tweak the index
satisfying the Order By and Where.
--
HTH
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:219f01c3e079$0670b460$a501280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Any ideas?
> TIA, Chris

ORDER BY returns opposite sequence between SQL 2000 and 2005

I have the following query returning results in opposite order between
versions 2000 and 2005:-
SELECT * FROM tblname where form= 'L'
order by acolumn desc
The database has been migrated from 2000 to 2005, so the structure and data
should be identical. Here's the DDL for the table in SQL2000:-
USE [MSD_Contracts]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblname](
[Form] [varchar](1) NOT NULL,
[acolumn] [varchar](1) NOT NULL,
[FieldNo] [int] NOT NULL,
[dbField] [varchar](30) NOT NULL,
[Validation] [varchar](20) NOT NULL,
[AllowNulls] [varchar](1) NOT NULL,
CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
(
[FieldNo] ASC,
[Form] ASC,
[acolumn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
[PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Any ideas please?Hi Adrian
The order should be the case assuming that the collations are the same. Do
you have some sample data? http://www.aspfaq.com/etiquette.asp?id=5006
You should change the varchar(1) columns.
John
"Adrian" wrote:
> I have the following query returning results in opposite order between
> versions 2000 and 2005:-
> SELECT * FROM tblname where form= 'L'
> order by acolumn desc
> The database has been migrated from 2000 to 2005, so the structure and data
> should be identical. Here's the DDL for the table in SQL2000:-
> USE [MSD_Contracts]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[tblname](
> [Form] [varchar](1) NOT NULL,
> [acolumn] [varchar](1) NOT NULL,
> [FieldNo] [int] NOT NULL,
> [dbField] [varchar](30) NOT NULL,
> [Validation] [varchar](20) NOT NULL,
> [AllowNulls] [varchar](1) NOT NULL,
> CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
> (
> [FieldNo] ASC,
> [Form] ASC,
> [acolumn] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> [PRIMARY]
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> Any ideas please?|||Collation sequence is the same in both versions -
SQL_Latin1_General_CP1_CI_AS. Here is some sample data:_
Form FrontBack FieldNo dbField Validation
AllowNulls
-- -- -- --
-- --
L F 470 SignaturePresent AlphaNum
N
L F 210 DependentQ Bit
N
L F 200 DependentN Bit
N
L F 190 DependentY Bit
N
L F 180 MarriageCertQ Bit
N
L F 170 MarriageCertN Bit
N
L F 160 MarriageCertY Bit
N
L F 150 ConsentQ Bit
N
L F 140 ConsentN Bit
N
L F 130 ConsentY Bit
N
L F 120 Under18Years Bit
N
L F 110 CAltered Bit
N
L F 100 SignedAndDatedQ Bit
N
L F 90 SignedAndDatedN Bit
N
L F 80 SignedAndDatedY Bit
N
L F 70 NameAndDOBQ Bit
N
L F 60 NameAndDOBN Bit
N
L F 50 NameAndDOBY Bit
N
L F 40 Barcode Num
N
L F 30 FormType1 AlphaNum
N
L F 20 ImageRef1 AlphaNum
N
L F 10 BatchNo Num
N
L B 420 EvidenceSent Bit
N
L B 410 EvidenceToWinz Bit
N
L B 400 ExtraEvidence Bit
N
L B 390 Oaltered Bit
N
L B 380 BankQ Bit
N
L B 370 BankN Bit
N
L B 360 BankY Bit
N
L B 340 IRDQ Bit
N
L B 330 IRDN Bit
N
L B 320 IRDY Bit
N
L B 310 ResidentQ Bit
N
L B 300 ResidentN Bit
N
L B 290 ResidentY Bit
N
L B 280 CitizenQ Bit
N
L B 270 CitizenN Bit
N
L B 260 CitizenY Bit
N
L B 250 Barcode Num
N
L B 240 FormType2 AlphaNum
N
L B 230 ImageRef2 AlphaNum
N
L B 220 BatchNo Num
N
(42 row(s) affected)
"John Bell" wrote:
> Hi Adrian
> The order should be the case assuming that the collations are the same. Do
> you have some sample data? http://www.aspfaq.com/etiquette.asp?id=5006
> You should change the varchar(1) columns.
> John
> "Adrian" wrote:
> > I have the following query returning results in opposite order between
> > versions 2000 and 2005:-
> > SELECT * FROM tblname where form= 'L'
> > order by acolumn desc
> >
> > The database has been migrated from 2000 to 2005, so the structure and data
> > should be identical. Here's the DDL for the table in SQL2000:-
> > USE [MSD_Contracts]
> > GO
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > SET ANSI_PADDING ON
> > GO
> > CREATE TABLE [dbo].[tblname](
> > [Form] [varchar](1) NOT NULL,
> > [acolumn] [varchar](1) NOT NULL,
> > [FieldNo] [int] NOT NULL,
> > [dbField] [varchar](30) NOT NULL,
> > [Validation] [varchar](20) NOT NULL,
> > [AllowNulls] [varchar](1) NOT NULL,
> > CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
> > (
> > [FieldNo] ASC,
> > [Form] ASC,
> > [acolumn] ASC
> > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> > [PRIMARY]
> > ) ON [PRIMARY]
> >
> > GO
> > SET ANSI_PADDING OFF
> >
> > Any ideas please?|||Hi Arian
You did not read the link I posted about sample data.
Here is some code that would have been useful:
USE TEMPDB
GO
CREATE TABLE [dbo].[tblname](
[Form] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FrontBack] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FieldNo] [int] NOT NULL ,
[dbField] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Validation] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AllowNulls] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
(
[FieldNo] ASC,
[Form] ASC,
[FrontBack] ASC
)
)
INSERT INTO tblname (Form, FrontBack, FieldNo, dbField, Validation,
AllowNulls)
SELECT 'L','F',470,'SignaturePresent','AlphaNum','N'
UNION ALL SELECT 'L','F',210,'DependentQ','Bit','N'
UNION ALL SELECT 'L','F',200,'DependentN','Bit','N'
UNION ALL SELECT 'L','F',190,'DependentY','Bit','N'
UNION ALL SELECT 'L','F',180,'MarriageCertQ','Bit','N'
UNION ALL SELECT 'L','F',170,'MarriageCertN','Bit','N'
UNION ALL SELECT 'L','F',160,'MarriageCertY','Bit','N'
UNION ALL SELECT 'L','F',150,'ConsentQ','Bit','N'
UNION ALL SELECT 'L','F',140,'ConsentN','Bit','N'
UNION ALL SELECT 'L','F',130,'ConsentY','Bit','N'
UNION ALL SELECT 'L','F',120,'Under18Years','Bit','N'
UNION ALL SELECT 'L','F',110,'CAltered','Bit','N'
UNION ALL SELECT 'L','F',100,'SignedAndDatedQ','Bit','N'
UNION ALL SELECT 'L','F',90,'SignedAndDatedN','Bit','N'
UNION ALL SELECT 'L','F',80,'SignedAndDatedY','Bit','N'
UNION ALL SELECT 'L','F',70,'NameAndDOBQ','Bit','N'
UNION ALL SELECT 'L','F',60,'NameAndDOBN','Bit','N'
UNION ALL SELECT 'L','F',50,'NameAndDOBY','Bit','N'
UNION ALL SELECT 'L','F',40,'Barcode','Num','N'
UNION ALL SELECT 'L','F',30,'FormType1','AlphaNum','N'
UNION ALL SELECT 'L','F',20,'ImageRef1','AlphaNum','N'
UNION ALL SELECT 'L','F',10,'BatchNo','Num','N'
UNION ALL SELECT 'L','B',420,'EvidenceSent','Bit','N'
UNION ALL SELECT 'L','B',410,'EvidenceToWinz','Bit','N'
UNION ALL SELECT 'L','B',400,'ExtraEvidence','Bit','N'
UNION ALL SELECT 'L','B',390,'Oaltered','Bit','N'
UNION ALL SELECT 'L','B',380,'BankQ','Bit','N'
UNION ALL SELECT 'L','B',370,'BankN','Bit','N'
UNION ALL SELECT 'L','B',360,'BankY','Bit','N'
UNION ALL SELECT 'L','B',340,'IRDQ','Bit','N'
UNION ALL SELECT 'L','B',330,'IRDN','Bit','N'
UNION ALL SELECT 'L','B',320,'IRDY','Bit','N'
UNION ALL SELECT 'L','B',310,'ResidentQ','Bit','N'
UNION ALL SELECT 'L','B',300,'ResidentN','Bit','N'
UNION ALL SELECT 'L','B',290,'ResidentY','Bit','N'
UNION ALL SELECT 'L','B',280,'CitizenQ','Bit','N'
UNION ALL SELECT 'L','B',270,'CitizenN','Bit','N'
UNION ALL SELECT 'L','B',260,'CitizenY','Bit','N'
UNION ALL SELECT 'L','B',250,'Barcode','Num','N'
UNION ALL SELECT 'L','B',240,'FormType2','AlphaNum','N'
UNION ALL SELECT 'L','B',230,'ImageRef2','AlphaNum','N'
UNION ALL SELECT 'L','B',220,'BatchNo','Num','N'
SELECT *
FROM tblname
where form= 'L'
order by FrontBack desc
The output I get from SQL 2000 is:
Form FrontBack FieldNo dbField Validation
AllowNulls
-- -- -- --
-- --
L F 10 BatchNo Num
N
L F 20 ImageRef1 AlphaNum
N
L F 30 FormType1 AlphaNum
N
L F 40 Barcode Num
N
L F 50 NameAndDOBY Bit
N
L F 60 NameAndDOBN Bit
N
L F 70 NameAndDOBQ Bit
N
L F 80 SignedAndDatedY Bit
N
L F 90 SignedAndDatedN Bit
N
L F 100 SignedAndDatedQ Bit
N
L F 110 CAltered Bit
N
L F 120 Under18Years Bit
N
L F 130 ConsentY Bit
N
L F 140 ConsentN Bit
N
L F 150 ConsentQ Bit
N
L F 160 MarriageCertY Bit
N
L F 170 MarriageCertN Bit
N
L F 180 MarriageCertQ Bit
N
L F 190 DependentY Bit
N
L F 200 DependentN Bit
N
L F 210 DependentQ Bit
N
L F 470 SignaturePresent AlphaNum
N
L B 220 BatchNo Num
N
L B 230 ImageRef2 AlphaNum
N
L B 240 FormType2 AlphaNum
N
L B 250 Barcode Num
N
L B 260 CitizenY Bit
N
L B 270 CitizenN Bit
N
L B 280 CitizenQ Bit
N
L B 290 ResidentY Bit
N
L B 300 ResidentN Bit
N
L B 310 ResidentQ Bit
N
L B 320 IRDY Bit
N
L B 330 IRDN Bit
N
L B 340 IRDQ Bit
N
L B 360 BankY Bit
N
L B 370 BankN Bit
N
L B 380 BankQ Bit
N
L B 390 Oaltered Bit
N
L B 400 ExtraEvidence Bit
N
L B 410 EvidenceToWinz Bit
N
L B 420 EvidenceSent Bit
N
(42 row(s) affected)
The output I get from SQL 2005 is:
Form FrontBack FieldNo dbField Validation
AllowNulls
-- -- -- --
-- --
L F 10 BatchNo Num
N
L F 20 ImageRef1 AlphaNum
N
L F 30 FormType1 AlphaNum
N
L F 40 Barcode Num
N
L F 50 NameAndDOBY Bit
N
L F 60 NameAndDOBN Bit
N
L F 70 NameAndDOBQ Bit
N
L F 80 SignedAndDatedY Bit
N
L F 90 SignedAndDatedN Bit
N
L F 100 SignedAndDatedQ Bit
N
L F 110 CAltered Bit
N
L F 120 Under18Years Bit
N
L F 130 ConsentY Bit
N
L F 140 ConsentN Bit
N
L F 150 ConsentQ Bit
N
L F 160 MarriageCertY Bit
N
L F 170 MarriageCertN Bit
N
L F 180 MarriageCertQ Bit
N
L F 190 DependentY Bit
N
L F 200 DependentN Bit
N
L F 210 DependentQ Bit
N
L F 470 SignaturePresent AlphaNum
N
L B 220 BatchNo Num
N
L B 230 ImageRef2 AlphaNum
N
L B 240 FormType2 AlphaNum
N
L B 250 Barcode Num
N
L B 260 CitizenY Bit
N
L B 270 CitizenN Bit
N
L B 280 CitizenQ Bit
N
L B 290 ResidentY Bit
N
L B 300 ResidentN Bit
N
L B 310 ResidentQ Bit
N
L B 320 IRDY Bit
N
L B 330 IRDN Bit
N
L B 340 IRDQ Bit
N
L B 360 BankY Bit
N
L B 370 BankN Bit
N
L B 380 BankQ Bit
N
L B 390 Oaltered Bit
N
L B 400 ExtraEvidence Bit
N
L B 410 EvidenceToWinz Bit
N
L B 420 EvidenceSent Bit
N
(42 row(s) affected)
The order by only guarantees that FrontBack will be ordered 'F' then 'B' and
this is the case on both instances. As FieldNo and Form are not in the order
by their order is not guaranteed.
John
"Adrian" wrote:
> Collation sequence is the same in both versions -
> SQL_Latin1_General_CP1_CI_AS. Here is some sample data:_
> Form FrontBack FieldNo dbField Validation
> AllowNulls
> -- -- -- --
> -- --
> L F 470 SignaturePresent AlphaNum
> N
> L F 210 DependentQ Bit
> N
> L F 200 DependentN Bit
> N
> L F 190 DependentY Bit
> N
> L F 180 MarriageCertQ Bit
> N
> L F 170 MarriageCertN Bit
> N
> L F 160 MarriageCertY Bit
> N
> L F 150 ConsentQ Bit
> N
> L F 140 ConsentN Bit
> N
> L F 130 ConsentY Bit
> N
> L F 120 Under18Years Bit
> N
> L F 110 CAltered Bit
> N
> L F 100 SignedAndDatedQ Bit
> N
> L F 90 SignedAndDatedN Bit
> N
> L F 80 SignedAndDatedY Bit
> N
> L F 70 NameAndDOBQ Bit
> N
> L F 60 NameAndDOBN Bit
> N
> L F 50 NameAndDOBY Bit
> N
> L F 40 Barcode Num
> N
> L F 30 FormType1 AlphaNum
> N
> L F 20 ImageRef1 AlphaNum
> N
> L F 10 BatchNo Num
> N
> L B 420 EvidenceSent Bit
> N
> L B 410 EvidenceToWinz Bit
> N
> L B 400 ExtraEvidence Bit
> N
> L B 390 Oaltered Bit
> N
> L B 380 BankQ Bit
> N
> L B 370 BankN Bit
> N
> L B 360 BankY Bit
> N
> L B 340 IRDQ Bit
> N
> L B 330 IRDN Bit
> N
> L B 320 IRDY Bit
> N
> L B 310 ResidentQ Bit
> N
> L B 300 ResidentN Bit
> N
> L B 290 ResidentY Bit
> N
> L B 280 CitizenQ Bit
> N
> L B 270 CitizenN Bit
> N
> L B 260 CitizenY Bit
> N
> L B 250 Barcode Num
> N
> L B 240 FormType2 AlphaNum
> N
> L B 230 ImageRef2 AlphaNum
> N
> L B 220 BatchNo Num
> N
> (42 row(s) affected)
>
> "John Bell" wrote:
> > Hi Adrian
> >
> > The order should be the case assuming that the collations are the same. Do
> > you have some sample data? http://www.aspfaq.com/etiquette.asp?id=5006
> >
> > You should change the varchar(1) columns.
> >
> > John
> >
> > "Adrian" wrote:
> >
> > > I have the following query returning results in opposite order between
> > > versions 2000 and 2005:-
> > > SELECT * FROM tblname where form= 'L'
> > > order by acolumn desc
> > >
> > > The database has been migrated from 2000 to 2005, so the structure and data
> > > should be identical. Here's the DDL for the table in SQL2000:-
> > > USE [MSD_Contracts]
> > > GO
> > > SET ANSI_NULLS ON
> > > GO
> > > SET QUOTED_IDENTIFIER ON
> > > GO
> > > SET ANSI_PADDING ON
> > > GO
> > > CREATE TABLE [dbo].[tblname](
> > > [Form] [varchar](1) NOT NULL,
> > > [acolumn] [varchar](1) NOT NULL,
> > > [FieldNo] [int] NOT NULL,
> > > [dbField] [varchar](30) NOT NULL,
> > > [Validation] [varchar](20) NOT NULL,
> > > [AllowNulls] [varchar](1) NOT NULL,
> > > CONSTRAINT [PK_tblname_1_12] PRIMARY KEY CLUSTERED
> > > (
> > > [FieldNo] ASC,
> > > [Form] ASC,
> > > [acolumn] ASC
> > > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY => > > OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON
> > > [PRIMARY]
> > > ) ON [PRIMARY]
> > >
> > > GO
> > > SET ANSI_PADDING OFF
> > >
> > > Any ideas please?

Order by rank

Hello
I have this query which works fine, but I would like to order by rank, so i
get most "correct" results first.....
SELECT * FROM Questions
WHERE FREETEXT(Question, @.Keywords)
OR FREETEXT(Answer, @.Keywords)
OR FREETEXT(Headline, @.Keywords)
TIA
/Lasse
here's how I would tackle this:
declare declare @.keywords varchar(100)
set @.keywords='microsoft'
select rank= case when search1.rank>=search2.rank and
search1.rank>=search3.rank then search1.rank
when search2.rank>=search3.rank and search2.rank>=search1.rank then
search2.rank
when search3.rank>=search1.rank and search3.rank>=search1.rank then
search3.rank end
from questions join freetexttable(Questions, question, @.keywords) as Search1
on Search1.[key]=pk
join freetexttable(Questions, Answer, @.keywords) as Search2 on
Search2.[key]=pk
join freetexttable(Questions, HeadLine, @.keywords) as Search3 on
Search3.[key]=pk
order by rank
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:esNSb4WlEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hello
> I have this query which works fine, but I would like to order by rank, so
i
> get most "correct" results first.....
> SELECT * FROM Questions
> WHERE FREETEXT(Question, @.Keywords)
> OR FREETEXT(Answer, @.Keywords)
> OR FREETEXT(Headline, @.Keywords)
>
> TIA
> /Lasse
>
|||Lasse,
I don't want to assume anything here, but in order by rank, you would need
to use FREETEXTTABLE as FREETEXT does not provide that functionality. Again,
not wanting to assume anything, without having you provide more information,
but do you want a one column to be "ranked" higher than the over columns in
your tables?
More information on what you are trying to achieve would be helpful in order
to provide a more specific solution for you.
Thanks,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:esNSb4WlEHA.1152@.TK2MSFTNGP11.phx.gbl...
> Hello
> I have this query which works fine, but I would like to order by rank, so
i
> get most "correct" results first.....
> SELECT * FROM Questions
> WHERE FREETEXT(Question, @.Keywords)
> OR FREETEXT(Answer, @.Keywords)
> OR FREETEXT(Headline, @.Keywords)
>
> TIA
> /Lasse
>
|||John,
no column should be ranked higher than other.
works fine with "rank" when one column, but not sure how to do it when there
are 3 columns.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23IaKv3blEHA.1904@.TK2MSFTNGP09.phx.gbl...
> Lasse,
> I don't want to assume anything here, but in order by rank, you would need
> to use FREETEXTTABLE as FREETEXT does not provide that functionality.
Again,
> not wanting to assume anything, without having you provide more
information,
> but do you want a one column to be "ranked" higher than the over columns
in
> your tables?
> More information on what you are trying to achieve would be helpful in
order[vbcol=seagreen]
> to provide a more specific solution for you.
> Thanks,
> John
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:esNSb4WlEHA.1152@.TK2MSFTNGP11.phx.gbl...
so
> i
>
|||If that's the case you should add them respective ranks, as illustrated
below.
declare @.keywords varchar(100)
set @.keywords='microsoft'
select question, answer, headline, rank=
search1.rank+search2.rank+search3.rank
from questions join freetexttable(Questions, question, @.keywords) as Search1
on Search1.[key]=pk
join freetexttable(Questions, Answer, @.keywords) as Search2 on
Search2.[key]=pk
join freetexttable(Questions, HeadLine, @.keywords) as Search3 on
Search3.[key]=pk
order by rank desc
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:%23IwqipklEHA.3876@.TK2MSFTNGP15.phx.gbl...
> John,
> no column should be ranked higher than other.
> works fine with "rank" when one column, but not sure how to do it when
there[vbcol=seagreen]
> are 3 columns.
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23IaKv3blEHA.1904@.TK2MSFTNGP09.phx.gbl...
need
> Again,
> information,
> in
> order
> so
>
|||Lasse,
Ok... and that's why I assume nothing and try to gather information first
before answering questions... <G>
You can use FREETEXTTABLE in the following multiple table & columns SQL FTS
query:
SELECT distinct e.OrderNo, e.Label
from ItemStock AS e, ItemTitles t, ItemHardware h,
containstable(ItemStock, Label, 'Billy') as A,
containstable(ItemTitles, Title, 'Stranger') as B,
containstable(ItemHardware, s_page, 'row') as C
where
A.[KEY] = e.OrderNo and -- OR = generates mutiple rows, and
therefore needs distinct e.OrderNo.
B.[KEY] = t.OrderNo and
C.[KEY] = h.OrderNo
Substitute ItemStock for your table Question, ItemTitles for your table
Answer and ItemHardware for your table Headline. Note, all the above tables
have Primary key - Foreign Key relationships as should your tables in order
for the joins to work correctly. You can also alter the above to have OR
conditions between the containstable (or freetexttable) clauses, but you
will need to use the distinct parameter to eliminate the duplicate rows. Let
me know if you need the DDL (create table, etc.) for the above tables as I
can email them to you if you want.
Thanks,
John
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:#IwqipklEHA.3876@.TK2MSFTNGP15.phx.gbl...
> John,
> no column should be ranked higher than other.
> works fine with "rank" when one column, but not sure how to do it when
there[vbcol=seagreen]
> are 3 columns.
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23IaKv3blEHA.1904@.TK2MSFTNGP09.phx.gbl...
need
> Again,
> information,
> in
> order
> so
>
|||What the heck?
In the original post the poster was using freetext where Question, Answer
and Headline would refer to columns in the table Questions.
Secondly the poster wanted to order by rank. I don't see anything in here
where you order by rank.
Are you possibly assuming something?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uiS6ymolEHA.2504@.TK2MSFTNGP14.phx.gbl...
> Lasse,
> Ok... and that's why I assume nothing and try to gather information first
> before answering questions... <G>
> You can use FREETEXTTABLE in the following multiple table & columns SQL
FTS
> query:
> SELECT distinct e.OrderNo, e.Label
> from ItemStock AS e, ItemTitles t, ItemHardware h,
> containstable(ItemStock, Label, 'Billy') as A,
> containstable(ItemTitles, Title, 'Stranger') as B,
> containstable(ItemHardware, s_page, 'row') as C
> where
> A.[KEY] = e.OrderNo and -- OR = generates mutiple rows, and
> therefore needs distinct e.OrderNo.
> B.[KEY] = t.OrderNo and
> C.[KEY] = h.OrderNo
>
> Substitute ItemStock for your table Question, ItemTitles for your table
> Answer and ItemHardware for your table Headline. Note, all the above
tables
> have Primary key - Foreign Key relationships as should your tables in
order
> for the joins to work correctly. You can also alter the above to have OR
> conditions between the containstable (or freetexttable) clauses, but you
> will need to use the distinct parameter to eliminate the duplicate rows.
Let[vbcol=seagreen]
> me know if you need the DDL (create table, etc.) for the above tables as I
> can email them to you if you want.
> Thanks,
> John
>
> "Lasse Edsvik" <lasse@.nospam.com> wrote in message
> news:#IwqipklEHA.3876@.TK2MSFTNGP15.phx.gbl...
> there
> need
columns[vbcol=seagreen]
rank,
>
|||In Lasse's reply to me, he stated that "no column should be ranked higher
than other.", so all that needs to be changed / added to my query example is
substituting freetexttable for containstable and adding an ORDER BY clause,
for example:
SELECT distinct t.OrderNo, t.Title, A.[RANK], B.[RANK], C.[RANK]
from ItemTitles AS t,
freetexttable(ItemTitles, Title, 'title') as A,
freetexttable(ItemTitles, Artist, 'microsoft') as B,
freetexttable(ItemTitles, Location, 'else') as C
where
A.[KEY] = t.OrderNo and
B.[KEY] = t.OrderNo and
C.[KEY] = t.OrderNo
ORDER BY A.[RANK], B.[RANK], C.[RANK] DESC
So, Hilary, I wasn't assuming anything, I did mis-read the initial question
(as you have as well from time-to-time) to indicate multiple tables, vs. a
single table with multiple columns and I've altered the above query to
correct this mistake. I was replying to Lasse's most recent post and I'll
wait for Lasse to reply with his feedback to this post as well. Please, feel
free to email me directly, if you have any questions &/or concerns.
A question for Lasse - Are you trying to get results from just one column or
across all three columns?
Best Regards,
John
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Obq5shplEHA.3452@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> What the heck?
> In the original post the poster was using freetext where Question, Answer
> and Headline would refer to columns in the table Questions.
> Secondly the poster wanted to order by rank. I don't see anything in here
> where you order by rank.
> Are you possibly assuming something?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uiS6ymolEHA.2504@.TK2MSFTNGP14.phx.gbl...
first[vbcol=seagreen]
> FTS
> tables
> order
> Let
I[vbcol=seagreen]
would[vbcol=seagreen]
functionality.[vbcol=seagreen]
> columns
in
> rank,
>
|||now, I'm even more confused. Lasse said ""no column should be ranked higher
than other.", but you are ranking them by a.rank, b.rank, c.rank desc.
So, it seems you are ranking a.rank higher than b.rank, and b.rank higher
than c.rank. Then you are sorting a.rank asc., b.rank asc, and c.rank desc.
This I really don't understand.
Surely you mean a.rank desc, b.rank desc, c.rank desc?
What am I missing?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23hG8r9qlEHA.324@.TK2MSFTNGP11.phx.gbl...
> In Lasse's reply to me, he stated that "no column should be ranked higher
> than other.", so all that needs to be changed / added to my query example
is
> substituting freetexttable for containstable and adding an ORDER BY
clause,
> for example:
> SELECT distinct t.OrderNo, t.Title, A.[RANK], B.[RANK], C.[RANK]
> from ItemTitles AS t,
> freetexttable(ItemTitles, Title, 'title') as A,
> freetexttable(ItemTitles, Artist, 'microsoft') as B,
> freetexttable(ItemTitles, Location, 'else') as C
> where
> A.[KEY] = t.OrderNo and
> B.[KEY] = t.OrderNo and
> C.[KEY] = t.OrderNo
> ORDER BY A.[RANK], B.[RANK], C.[RANK] DESC
>
> So, Hilary, I wasn't assuming anything, I did mis-read the initial
question
> (as you have as well from time-to-time) to indicate multiple tables, vs. a
> single table with multiple columns and I've altered the above query to
> correct this mistake. I was replying to Lasse's most recent post and I'll
> wait for Lasse to reply with his feedback to this post as well. Please,
feel
> free to email me directly, if you have any questions &/or concerns.
> A question for Lasse - Are you trying to get results from just one column
or[vbcol=seagreen]
> across all three columns?
> Best Regards,
> John
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:Obq5shplEHA.3452@.TK2MSFTNGP15.phx.gbl...
Answer[vbcol=seagreen]
here[vbcol=seagreen]
> first
SQL[vbcol=seagreen]
table[vbcol=seagreen]
OR[vbcol=seagreen]
you[vbcol=seagreen]
rows.[vbcol=seagreen]
as[vbcol=seagreen]
> I
when[vbcol=seagreen]
> would
> functionality.
helpful
> in
>
|||Well, I just threw that in for you while waiting for Lasse's reply!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:et9$kFtlEHA.704@.TK2MSFTNGP09.phx.gbl...
> now, I'm even more confused. Lasse said ""no column should be ranked
higher
> than other.", but you are ranking them by a.rank, b.rank, c.rank desc.
> So, it seems you are ranking a.rank higher than b.rank, and b.rank higher
> than c.rank. Then you are sorting a.rank asc., b.rank asc, and c.rank
desc.[vbcol=seagreen]
> This I really don't understand.
> Surely you mean a.rank desc, b.rank desc, c.rank desc?
> What am I missing?
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23hG8r9qlEHA.324@.TK2MSFTNGP11.phx.gbl...
higher[vbcol=seagreen]
example[vbcol=seagreen]
> is
> clause,
> question
a[vbcol=seagreen]
I'll[vbcol=seagreen]
> feel
column[vbcol=seagreen]
> or
> Answer
> here
> SQL
> table
in[vbcol=seagreen]
have[vbcol=seagreen]
> OR
> you
> rows.
tables[vbcol=seagreen]
> as
> when
> helpful
by
>

ORDER BY question.

I have some data that I want to select with an ORDER BY statement and be =
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a =
ParentID =3D to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.comORDER BY ID, ParentID, Name
http://www.aspfaq.com/
(Reverse address to reply.)
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a
ParentID = to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com|||select * from tbl
order by
case when parentID = 0 then ID
when parentID > 0 then parentID
end, parentID, name
Regards,
Huiyong
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a
ParentID = to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com|||Greg Collins [InfoPath MVP] wrote:
> I have some data that I want to select with an ORDER BY statement and be a
ble to have it sorted in a hierrarchical order.
> There will be essentially 1) and ID, and 2) a ParentID.
> I would like to have the data returned sorted such that any row with a Par
entID = to an ID is under that ID
> So if I have:
> NAME / ID / ParentID
> name1 / 1 / 0
> name2 / 2 / 0
> name3 / 3 / 1
> name4 / 4 / 5
> name5 / 5 / 1
> name6 / 6 / 2
> I want it to come back sorted as:
> name1
> name3
> name5
> name4
> name2
> name6
> Any ideas?
>
Doing what you want to do will require some front-end manipulation.
Zach|||This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
"Huiyong Lau" <huiyong_lau@.hotmail.com> wrote in message =
news:u35#rAM2EHA.1264@.TK2MSFTNGP12.phx.gbl...
select * from tbl
order by
case when parentID =3D 0 then ID
when parentID > 0 then parentID
end, parentID, name
Regards,
Huiyong
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a
ParentID =3D to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com|||Greg, see if the following function works for you:
CREATE TABLE Tree
(
NAME VARCHAR(10) NOT NULL,
ID INT NOT NULL PRIMARY KEY,
ParentID INT NOT NULL
)
INSERT INTO Tree VALUES('name1',1,0)
INSERT INTO Tree VALUES('name2',2,0)
INSERT INTO Tree VALUES('name3',3,1)
INSERT INTO Tree VALUES('name4',4,5)
INSERT INTO Tree VALUES('name5',5,1)
INSERT INTO Tree VALUES('name6',6,2)
GO
CREATE FUNCTION ufn_TreeSort() RETURNS @.t table
(
id int NOT NULL PRIMARY KEY,
parentID int NOT NULL,
NAME varchar(10) NOT NULL,
lvl int NOT NULL,
path varbinary(8000) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int; SET @.lvl = 0
-- insert 0 level nodes
INSERT INTO @.t
SELECT id, parentID, NAME, @.lvl, CAST(id AS BINARY(4))
FROM Tree
WHERE parentID = 0
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
-- insert children of nodes in prev level
INSERT INTO @.t
SELECT E.id, E.parentID, E.NAME, @.lvl,
T.path + CAST(E.id AS BINARY(4))
FROM Tree AS E JOIN @.t AS T
ON E.parentID = T.id AND T.lvl = @.lvl - 1
END
RETURN
END
GO
-- test
SELECT REPLICATE(' ', lvl) + NAME
FROM ufn_TreeSort()
ORDER BY path
-- Output:
name1
name3
name5
name4
name2
name6
BG, SQL Server MVP
www.SolidQualityLearning.com
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a
ParentID = to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com|||Greg,
Here is something similar that might help:
set nocount on
go
create table Hi (
PageID int,
ParentPageID int,
Descrip varchar(20)
)
insert into Hi values (1,null,'County 1')
insert into Hi values (2,1,'C1 Region 1')
insert into Hi values (3,1,'C1 Region 2')
insert into Hi values (4,2,'C1 R1 Unit 1')
insert into Hi values (5,2,'C1 R1 Unit 2')
insert into Hi values (6,3,'C1 R2 Unit 1')
insert into Hi values (7,null,'County 2')
insert into Hi values (8,7,'C2 Region 1')
insert into Hi values (9,1,'C1 Region 3')
select *, 0 as Level, cast(str(pageid,10) as varchar(8000)) as H into #Temp
from Hi
where ParentPageID is null
declare @.lev int
set @.lev = 0
while @.@.rowcount > 0 begin
set @.lev = @.lev + 1
insert into #Temp
select G.*, @.lev, T.H+str(G.pageid,10)
from Hi G join #Temp T
on G.ParentPageID = T.PageID
and T.level = @.lev - 1
end
select space(level*3)+descrip from #temp
order by H
go
drop table #temp
drop table Hi
Steve Kass
Drew University
Greg Collins [InfoPath MVP] wrote:

>This still just mainly sorts by ParentID. As the organization can be about
9 levels deep and any length... this approach doesn't accomplish the goal--
but I appreciate the idea.
>
>|||That worked great! Thank you!
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in =
message news:#NnAytN2EHA.4028@.TK2MSFTNGP15.phx.gbl...
Greg, see if the following function works for you:
CREATE TABLE Tree
(
NAME VARCHAR(10) NOT NULL,
ID INT NOT NULL PRIMARY KEY,
ParentID INT NOT NULL
)
INSERT INTO Tree VALUES('name1',1,0)
INSERT INTO Tree VALUES('name2',2,0)
INSERT INTO Tree VALUES('name3',3,1)
INSERT INTO Tree VALUES('name4',4,5)
INSERT INTO Tree VALUES('name5',5,1)
INSERT INTO Tree VALUES('name6',6,2)
GO
CREATE FUNCTION ufn_TreeSort() RETURNS @.t table
(
id int NOT NULL PRIMARY KEY,
parentID int NOT NULL,
NAME varchar(10) NOT NULL,
lvl int NOT NULL,
path varbinary(8000) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int; SET @.lvl =3D 0
-- insert 0 level nodes
INSERT INTO @.t
SELECT id, parentID, NAME, @.lvl, CAST(id AS BINARY(4))
FROM Tree
WHERE parentID =3D 0
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl =3D @.lvl + 1
-- insert children of nodes in prev level
INSERT INTO @.t
SELECT E.id, E.parentID, E.NAME, @.lvl,
T.path + CAST(E.id AS BINARY(4))
FROM Tree AS E JOIN @.t AS T
ON E.parentID =3D T.id AND T.lvl =3D @.lvl - 1
END
RETURN
END
GO
-- test
SELECT REPLICATE(' ', lvl) + NAME
FROM ufn_TreeSort()
ORDER BY path
-- Output:
name1
name3
name5
name4
name2
name6
--=20
BG, SQL Server MVP
www.SolidQualityLearning.com
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
=
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be =
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a=20
ParentID =3D to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com|||That worked great!
Thanx ever so much!!
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
"Steve Kass" <skass@.drew.edu> wrote in message =
news:u4$P2fP2EHA.3064@.TK2MSFTNGP10.phx.gbl...
Greg,
Here is something similar that might help:
set nocount on
go
create table Hi (
PageID int,
ParentPageID int,
Descrip varchar(20)
)
insert into Hi values (1,null,'County 1')
insert into Hi values (2,1,'C1 Region 1')
insert into Hi values (3,1,'C1 Region 2')
insert into Hi values (4,2,'C1 R1 Unit 1')
insert into Hi values (5,2,'C1 R1 Unit 2')
insert into Hi values (6,3,'C1 R2 Unit 1')
insert into Hi values (7,null,'County 2')
insert into Hi values (8,7,'C2 Region 1')
insert into Hi values (9,1,'C1 Region 3')
select *, 0 as Level, cast(str(pageid,10) as varchar(8000)) as H into =
#Temp
from Hi
where ParentPageID is null
declare @.lev int
set @.lev =3D 0
while @.@.rowcount > 0 begin
set @.lev =3D @.lev + 1
insert into #Temp
select G.*, @.lev, T.H+str(G.pageid,10)
from Hi G join #Temp T
on G.ParentPageID =3D T.PageID
and T.level =3D @.lev - 1
end
select space(level*3)+descrip from #temp
order by H
go
drop table #temp
drop table Hi
Steve Kass
Drew University
Greg Collins [InfoPath MVP] wrote:

>This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
> =20
>sql

ORDER BY question.

I have some data that I want to select with an ORDER BY statement and be =
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a =
ParentID =3D to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
ORDER BY ID, ParentID, Name
http://www.aspfaq.com/
(Reverse address to reply.)
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a
ParentID = to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
|||select * from tbl
order by
case when parentID = 0 then ID
when parentID > 0 then parentID
end, parentID, name
Regards,
Huiyong
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a
ParentID = to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
|||Greg Collins [InfoPath MVP] wrote:
> I have some data that I want to select with an ORDER BY statement and be able to have it sorted in a hierrarchical order.
> There will be essentially 1) and ID, and 2) a ParentID.
> I would like to have the data returned sorted such that any row with a ParentID = to an ID is under that ID
> So if I have:
> NAME / ID / ParentID
> name1 / 1 / 0
> name2 / 2 / 0
> name3 / 3 / 1
> name4 / 4 / 5
> name5 / 5 / 1
> name6 / 6 / 2
> I want it to come back sorted as:
> name1
> name3
> name5
> name4
> name2
> name6
> Any ideas?
>
Doing what you want to do will require some front-end manipulation.
Zach
|||This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
"Huiyong Lau" <huiyong_lau@.hotmail.com> wrote in message =
news:u35#rAM2EHA.1264@.TK2MSFTNGP12.phx.gbl...
select * from tbl
order by
case when parentID =3D 0 then ID
when parentID > 0 then parentID
end, parentID, name
Regards,
Huiyong
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a
ParentID =3D to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
|||Greg, see if the following function works for you:
CREATE TABLE Tree
(
NAME VARCHAR(10) NOT NULL,
ID INT NOT NULL PRIMARY KEY,
ParentID INT NOT NULL
)
INSERT INTO Tree VALUES('name1',1,0)
INSERT INTO Tree VALUES('name2',2,0)
INSERT INTO Tree VALUES('name3',3,1)
INSERT INTO Tree VALUES('name4',4,5)
INSERT INTO Tree VALUES('name5',5,1)
INSERT INTO Tree VALUES('name6',6,2)
GO
CREATE FUNCTION ufn_TreeSort() RETURNS @.t table
(
id int NOT NULL PRIMARY KEY,
parentID int NOT NULL,
NAME varchar(10) NOT NULL,
lvl int NOT NULL,
path varbinary(8000) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int; SET @.lvl = 0
-- insert 0 level nodes
INSERT INTO @.t
SELECT id, parentID, NAME, @.lvl, CAST(id AS BINARY(4))
FROM Tree
WHERE parentID = 0
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
-- insert children of nodes in prev level
INSERT INTO @.t
SELECT E.id, E.parentID, E.NAME, @.lvl,
T.path + CAST(E.id AS BINARY(4))
FROM Tree AS E JOIN @.t AS T
ON E.parentID = T.id AND T.lvl = @.lvl - 1
END
RETURN
END
GO
-- test
SELECT REPLICATE(' ', lvl) + NAME
FROM ufn_TreeSort()
ORDER BY path
-- Output:
name1
name3
name5
name4
name2
name6
BG, SQL Server MVP
www.SolidQualityLearning.com
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a
ParentID = to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
|||Greg,
Here is something similar that might help:
set nocount on
go
create table Hi (
PageID int,
ParentPageID int,
Descrip varchar(20)
)
insert into Hi values (1,null,'County 1')
insert into Hi values (2,1,'C1 Region 1')
insert into Hi values (3,1,'C1 Region 2')
insert into Hi values (4,2,'C1 R1 Unit 1')
insert into Hi values (5,2,'C1 R1 Unit 2')
insert into Hi values (6,3,'C1 R2 Unit 1')
insert into Hi values (7,null,'County 2')
insert into Hi values (8,7,'C2 Region 1')
insert into Hi values (9,1,'C1 Region 3')
select *, 0 as Level, cast(str(pageid,10) as varchar(8000)) as H into #Temp
from Hi
where ParentPageID is null
declare @.lev int
set @.lev = 0
while @.@.rowcount > 0 begin
set @.lev = @.lev + 1
insert into #Temp
select G.*, @.lev, T.H+str(G.pageid,10)
from Hi G join #Temp T
on G.ParentPageID = T.PageID
and T.level = @.lev - 1
end
select space(level*3)+descrip from #temp
order by H
go
drop table #temp
drop table Hi
Steve Kass
Drew University
Greg Collins [InfoPath MVP] wrote:

>This still just mainly sorts by ParentID. As the organization can be about 9 levels deep and any length... this approach doesn't accomplish the goal-- but I appreciate the idea.
>
>
|||That worked great! Thank you!
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in =
message news:#NnAytN2EHA.4028@.TK2MSFTNGP15.phx.gbl...
Greg, see if the following function works for you:
CREATE TABLE Tree
(
NAME VARCHAR(10) NOT NULL,
ID INT NOT NULL PRIMARY KEY,
ParentID INT NOT NULL
)
INSERT INTO Tree VALUES('name1',1,0)
INSERT INTO Tree VALUES('name2',2,0)
INSERT INTO Tree VALUES('name3',3,1)
INSERT INTO Tree VALUES('name4',4,5)
INSERT INTO Tree VALUES('name5',5,1)
INSERT INTO Tree VALUES('name6',6,2)
GO
CREATE FUNCTION ufn_TreeSort() RETURNS @.t table
(
id int NOT NULL PRIMARY KEY,
parentID int NOT NULL,
NAME varchar(10) NOT NULL,
lvl int NOT NULL,
path varbinary(8000) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int; SET @.lvl =3D 0
-- insert 0 level nodes
INSERT INTO @.t
SELECT id, parentID, NAME, @.lvl, CAST(id AS BINARY(4))
FROM Tree
WHERE parentID =3D 0
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl =3D @.lvl + 1
-- insert children of nodes in prev level
INSERT INTO @.t
SELECT E.id, E.parentID, E.NAME, @.lvl,
T.path + CAST(E.id AS BINARY(4))
FROM Tree AS E JOIN @.t AS T
ON E.parentID =3D T.id AND T.lvl =3D @.lvl - 1
END
RETURN
END
GO
-- test
SELECT REPLICATE(' ', lvl) + NAME
FROM ufn_TreeSort()
ORDER BY path
-- Output:
name1
name3
name5
name4
name2
name6
--=20
BG, SQL Server MVP
www.SolidQualityLearning.com
"Greg Collins [InfoPath MVP]" <Greg.Collins_AT_InfoPathDev.com> wrote in =
message news:OmpKL6L2EHA.204@.TK2MSFTNGP10.phx.gbl...
I have some data that I want to select with an ORDER BY statement and be =
able to have it sorted in a hierrarchical order.
There will be essentially 1) and ID, and 2) a ParentID.
I would like to have the data returned sorted such that any row with a=20
ParentID =3D to an ID is under that ID
So if I have:
NAME / ID / ParentID
name1 / 1 / 0
name2 / 2 / 0
name3 / 3 / 1
name4 / 4 / 5
name5 / 5 / 1
name6 / 6 / 2
I want it to come back sorted as:
name1
name3
name5
name4
name2
name6
Any ideas?
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
|||That worked great!
Thanx ever so much!!
--=20
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com
"Steve Kass" <skass@.drew.edu> wrote in message =
news:u4$P2fP2EHA.3064@.TK2MSFTNGP10.phx.gbl...
Greg,
Here is something similar that might help:
set nocount on
go
create table Hi (
PageID int,
ParentPageID int,
Descrip varchar(20)
)
insert into Hi values (1,null,'County 1')
insert into Hi values (2,1,'C1 Region 1')
insert into Hi values (3,1,'C1 Region 2')
insert into Hi values (4,2,'C1 R1 Unit 1')
insert into Hi values (5,2,'C1 R1 Unit 2')
insert into Hi values (6,3,'C1 R2 Unit 1')
insert into Hi values (7,null,'County 2')
insert into Hi values (8,7,'C2 Region 1')
insert into Hi values (9,1,'C1 Region 3')
select *, 0 as Level, cast(str(pageid,10) as varchar(8000)) as H into =
#Temp
from Hi
where ParentPageID is null
declare @.lev int
set @.lev =3D 0
while @.@.rowcount > 0 begin
set @.lev =3D @.lev + 1
insert into #Temp
select G.*, @.lev, T.H+str(G.pageid,10)
from Hi G join #Temp T
on G.ParentPageID =3D T.PageID
and T.level =3D @.lev - 1
end
select space(level*3)+descrip from #temp
order by H
go
drop table #temp
drop table Hi
Steve Kass
Drew University
Greg Collins [InfoPath MVP] wrote:

>This still just mainly sorts by ParentID. As the organization can be =
about 9 levels deep and any length... this approach doesn't accomplish =
the goal-- but I appreciate the idea.
> =20
>

order by question

I have the following SQL query

SQL = "SELECT * from nonconformance INNER JOIN nc_status ON nonconformance.status_id = nc_status.status_id INNER JOIN nc_major_category ON nc_major_category.major_category_id = nonconformance.major_category_id INNER JOIN nc_department ON nc_department.department_id = nonconformance.department_id AND car_only = '0'ORDER by nonconformance.nc_date"

The order by statement is set to sort my data in a datagrid by date. The issue I am having is it sorts by the lastest date first and the newest record or date is the last in the grid, so if i have 500 records the newest one is the last. How can I reverse the order by so that the newest date is first.

thanksORDER by nonconformance.nc_date DESC|||works great thanks

Order by question

How do a construct a select statement that will perform an 'order by ASC',
but will place my nulls at the end of the list instead of the beginning of
the list.
Thanks.
Archerbagman3rd wrote on Wed, 11 May 2005 08:24:01 -0700:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
What's the largest value you expect to see in the column? Place something
larger in the following to replace <<value to use>>
ORDER BY COALESCE(<column>,<<value to use>> )
eg. if column A will have a max value of 100, you would use
ORDER BY COALESCE(A,101)
Dan|||Figure out the "highest" possible value that can be in that column, dependin
g
on what datatype the column is, say it's varchar(6), then it would be
'ZZZZZZ', if it's a smallInt then its 32768, etc.
Then use the Coalesce function
Order By Coalesce(ColNAme, <MaxColValue> )
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer|||Use a CASE expression in the ORDER BY clause.
Example:
use northwind
go
select
OrderID,
ShippedDate
from
dbo.orders
order by
case when ShippedDate is null then 1 else 0 end asc,
ShippedDate asc
AMB
"bagman3rd" wrote:

> How do a construct a select statement that will perform an 'order by ASC',
> but will place my nulls at the end of the list instead of the beginning of
> the list.
> Thanks.
> Archer

ORDER BY question

I'm having some issuses with ORDER BY with my query. I'm trying to run the
same query in Oracle and SQL Server.
select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA)
AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP_I
D
= 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
This work fine in SQL Server but not in Oracle, if I replace ORDER BY
sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
SQL Server. Any idea how I can achive the same results but one query to wor
k
in Oracle and SQL Server. Thanks.How about using a derived table?
Not sure if this is the exact syntax in Oracle (might want to post to an
Oracle group!) but this should work in SQL Server:
SELECT
APP_ID,
USER_ID,
CB,
SB,
CA,
SA
FROM
(
select
a.APP_ID,
a.USER_ID,
SUM(a.CB) AS "CB",
SUM(a.SB) AS "SB",
SUM(a.CA) AS "CA",
SUM(a.SA) AS "SA"
FROM
APP_USER a
WHERE
a.START_TIME >= 1135044000000
AND a.START_TIME < 1135047600000
AND a.APP_ID = 56
AND GROUP_ID = 50
GROUP BY
a.APP_ID,
a.USER_ID
) x
ORDER BY
CB+SB DESC;
"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:D4C09490-E189-4524-95FF-744CCE7F3A7A@.microsoft.com...
> I'm having some issuses with ORDER BY with my query. I'm trying to run
> the
> same query in Oracle and SQL Server.
> select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB",
> SUM(a.CA)
> AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
> 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND
> GROUP_ID
> = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
> This work fine in SQL Server but not in Oracle, if I replace ORDER BY
> sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
> SQL Server. Any idea how I can achive the same results but one query to
> work
> in Oracle and SQL Server. Thanks.|||"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:D4C09490-E189-4524-95FF-744CCE7F3A7A@.microsoft.com...
> I'm having some issuses with ORDER BY with my query. I'm trying to run
> the
> same query in Oracle and SQL Server.
> select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB",
> SUM(a.CA)
> AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
> 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND
> GROUP_ID
> = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
> This work fine in SQL Server but not in Oracle, if I replace ORDER BY
> sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
> SQL Server. Any idea how I can achive the same results but one query to
> work
> in Oracle and SQL Server. Thanks.
Try:
SELECT app_id, user_id, cb, sb, ca, sa
FROM
(SELECT a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB",
SUM(a.CA) AS "CA", SUM(a.SA) AS "SA",
SUM(a.CB)+SUM(a.SB) AS ord
FROM APP_USER a
WHERE a.START_TIME >= 1135044000000
AND a.START_TIME < 1135047600000
AND a.APP_ID = 56 AND GROUP_ID = 50
GROUP BY a.APP_ID, a.USER_ID) AS T
ORDER BY ord DESC ;
David Portas
SQL Server MVP
--|||"yodarules" <yodarules@.discussions.microsoft.com> wrote in message
news:D4C09490-E189-4524-95FF-744CCE7F3A7A@.microsoft.com...
> I'm having some issuses with ORDER BY with my query. I'm trying to run
> the
> same query in Oracle and SQL Server.
> select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB",
> SUM(a.CA)
> AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
> 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND
> GROUP_ID
> = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
> This work fine in SQL Server but not in Oracle, if I replace ORDER BY
> sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
> SQL Server. Any idea how I can achive the same results but one query to
> work
> in Oracle and SQL Server. Thanks.
I don't know if this will work, but you might try:
Order by 3 DESC
3 being the column's ordinal position in the SELECT list.
Rick Sawtell|||how about just adding a column for
sum(a.CB+a.SB) as CBSB
and then
order by CBSB desc
yodarules wrote:
> I'm having some issuses with ORDER BY with my query. I'm trying to run th
e
> same query in Oracle and SQL Server.
> select a.APP_ID, a.USER_ID, SUM(a.CB) AS "CB", SUM(a.SB) AS "SB", SUM(a.CA
)
> AS "CA", SUM(a.SA) AS "SA" FROM APP_USER a WHERE a.START_TIME >=
> 1135044000000 AND a.START_TIME < 1135047600000 AND a.APP_ID = 56 AND GROUP
_ID
> = 50 GROUP BY a.APP_ID, a.USER_ID ORDER BY sum(cb+sb) DESC
> This work fine in SQL Server but not in Oracle, if I replace ORDER BY
> sum(cb+sb) DESC with ORDER BY (cb+sb) DESC it works fine in Oracle but not
> SQL Server. Any idea how I can achive the same results but one query to w
ork
> in Oracle and SQL Server. Thanks.|||Thanks guys,
I already tried that, but adding another columns in the select list is ruled
out, since we don't need the sum of these there. Its only for display
purpose that we need the sum of these two columns.
The reply by Rick, what I need is the sum of the two columns, in your case
giving the position is only going to do it for that one column.
"Trey Walpole" wrote:

> how about just adding a column for
> sum(a.CB+a.SB) as CBSB
> and then
> order by CBSB desc
>
> yodarules wrote:
>|||I don't understand the 'x' before the ORDER BY, could you explain that pleas
e.
"Aaron Bertrand [SQL Server MVP]" wrote:

> How about using a derived table?
> Not sure if this is the exact syntax in Oracle (might want to post to an
> Oracle group!) but this should work in SQL Server:
>
> SELECT
> APP_ID,
> USER_ID,
> CB,
> SB,
> CA,
> SA
> FROM
> (
> select
> a.APP_ID,
> a.USER_ID,
> SUM(a.CB) AS "CB",
> SUM(a.SB) AS "SB",
> SUM(a.CA) AS "CA",
> SUM(a.SA) AS "SA"
> FROM
> APP_USER a
> WHERE
> a.START_TIME >= 1135044000000
> AND a.START_TIME < 1135047600000
> AND a.APP_ID = 56
> AND GROUP_ID = 50
> GROUP BY
> a.APP_ID,
> a.USER_ID
> ) x
> ORDER BY
> CB+SB DESC;
>
> "yodarules" <yodarules@.discussions.microsoft.com> wrote in message
> news:D4C09490-E189-4524-95FF-744CCE7F3A7A@.microsoft.com...
>
>|||a derived table must have an alias - 'x' is as good as any. :)
yodarules wrote:
> I don't understand the 'x' before the ORDER BY, could you explain that ple
ase.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||>I don't understand the 'x' before the ORDER BY, could you explain that
>please.
>
The X is an alias for the derived table (the bracketed query after the FROM
clause).
David Portas
SQL Server MVP
--|||so don't display it :)
yodarules wrote:
> Thanks guys,
> I already tried that, but adding another columns in the select list is rul
ed
> out, since we don't need the sum of these there. Its only for display
> purpose that we need the sum of these two columns.
> The reply by Rick, what I need is the sum of the two columns, in your cas
e
> giving the position is only going to do it for that one column.
> "Trey Walpole" wrote:
>

order by query

Hi, I am using below query:

SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body,
tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved, tbh_Articles.Listed,
tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating,
tbh_Articles.ImageURL, tbh_Articles.special
FROM tbh_Lang CROSS JOIN
tbh_Articles
WHERE (tbh_Lang.LangID = @.LanguageID) AND (tbh_Articles.ArticleID = tbh_Lang.ArticleMain OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond1 OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond2 OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond3 OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond4 OR
tbh_Articles.ArticleID = tbh_Lang.ArticleSecond5)

Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on...

Tables structure: tbh_Articles(id, title, body...) ; tbh_Lang(id,ArticleMain,ArticleSecond1 ,ArticleSecond2... )

Any suggestions?

Try adding an ORDER BY clause after your WHERE clause and put the columns on which you want to order by.

|||

Tried that. that was my first instinct. the problem is that the query looks for all the articleIDs that in the sub query...it doesnt matter what is the order in the subquery.

Any other ideas?

Actually now I am getting punisg for using unormalize tables...

|||

I dont see any subquery in the code you provided. can you provide some sample data and how you want it sorted on?

|||

you need to ORDER BY tbh_Lang.ArticleMain, tbh_Lang.ArticleSecond1 , etc

Put the table qualifier (tbh_Lang) in the ORDER BY column specification.

Also, anything you put in the ORDER BY should also be in the select list

If you tried this, and it didn't work, then you didn't do what you think you did. This will work.

|||

dbland07666:

you need to ORDER BY tbh_Lang.ArticleMain, tbh_Lang.ArticleSecond1 , etc

Put the table qualifier (tbh_Lang) in the ORDER BY column specification.

Also, anything you put in the ORDER BY should also be in the select list

If you tried this, and it didn't work, then you didn't do what you think you did. This will work.

No.

Perhaps.

No.

Or perhaps you don't fully understand the problem. (I might not either) ndinakar asked for clarification, which I suspect will show that what you are suggesting doesn't do what he wants. I'll take a guess, but it's only a guess at this point.

|||

TRY:

ORDER BY tbh_Lang.ArticleMain,CASE WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleMain THEN 0

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond1 THEN 1

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond2 THEN 2

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond3 THEN 3

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond4 THEN 4

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond5 THEN 5

ELSE 9 END

Is that what you were looking for?

|||

megetron:

Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on...

I find that sentence confusing.

I wondered: Do you mean that you want to SORT by ArticleMain, ArticleSecond1, 2, 3 ... - ie put rows in that order without including all those columns in the results -

or do you mean you want to "return" those columns as in, fetch them as part of the results rows?

Then I wondered if you meant that you wanted to sort by only one of ArticleSecond1 or ArticleSecond2 etc, etc, whichever one of them happened to match ArticlesID.

But that is an uneccessary complication, because whichever one it was, it matches ArticlesID, so we can simply sort by ArticlesID instead.

So, I am not sure what you really want to happen, but perhaps you can clarify that for us.

|||

Sorry for the late response,
It looks like my question was not clear enough

I needed to sort the articles according to the data I have in ArticleMain, articleSecond1, areticleSecond2 and so on...

if the value in the tbh_lang table will be ArticleMain=5, ArticleSecond1=2 and ArticleSecond2=6 then the query will return from the Articles table the data details for article id 5, 2 , 6 and exactly in this order...currently the above query returns: 2,5,6 which is the order of the ids in the article table.

The following query do this but it is not that effisient. I am paying for the fact that I didn;t normalize tables, but still here is the query (if you can find any better, please post):

SELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesWHERE (ArticleIDIN (SELECT ArticleMainFROM tbh_LangWHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_5WHERE (ArticleIDIN (SELECT ArticleSecond1FROM tbh_LangAS tbh_Lang_5WHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_4WHERE (ArticleIDIN (SELECT ArticleSecond2FROM tbh_LangAS tbh_Lang_4WHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_3WHERE (ArticleIDIN (SELECT ArticleSecond3FROM tbh_LangAS tbh_Lang_3WHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_2WHERE (ArticleIDIN (SELECT ArticleSecond4FROM tbh_LangAS tbh_Lang_2WHERE (LangID = @.LanguageID)))UNIONALLSELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURLFROM tbh_ArticlesAS tbh_Articles_1WHERE (ArticleIDIN (SELECT ArticleSecond5FROM tbh_LangAS tbh_Lang_1WHERE (LangID = @.LanguageID)))

|||

Use your original query with this added at the end:

ORDER BY CASE WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleMain THEN 0

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond1 THEN 1

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond2 THEN 2

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond3 THEN 3

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond4 THEN 4

WHEN tbh_Articles.ArticleID=tbh_Lang.ArticleSecond5 THEN 5

ELSE 9 END

PS. Your union query is close, but it doesn't guarantee the order of the results. It may work most of the time, but 'A' UNION ALL 'B' can come back in any order. If you want to use your union idea, this is how you need to guarantee order:

SELECT *

FROM (

SELECT 1 AS SortOrder,ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURL
FROM tbh_Articles
WHERE (ArticleIDIN
(SELECT ArticleMain
FROM tbh_Lang
WHERE (LangID = @.LanguageID)))

UNION ALL

SELECT 2 AS SortOrder,ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURL
FROM tbh_Articles
WHERE (ArticleIDIN
(SELECT ArticleSecond1
FROM tbh_Lang
WHERE (LangID = @.LanguageID)))

...

) t1

ORDER BY SortOrder

And here's a 3rd method that should work as well:

SELECT ArticleID, AddedDate, AddedBy, Title, Abstract, ImageURL
FROM tbh_Articles
JOIN (SELECT 1 AS SortOrder, ArticleMain AS AID
FROM tbh_Lang
WHERE (LangID = @.LanguageID)

UNION ALL

SELECT 2 AS SortOrder, ArticleSecond1 AS AID
FROM tbh_Lang
WHERE (LangID = @.LanguageID)

UNION ALL

...

) InnerTable ON Innertable.AID=tbh_Articles.ArticleID

ORDER BY InnerTable.SortOrder

ORDER BY problem with CONVERT

Hi,
I just realized that when I started using the CONVERT function on my dates in my SELECT statement and try to ORDER BY one of the date fields that I convert, the order isn't actually correct. Here's the statement:

$query = "SELECT id, BroSisFirstName, BroSisLastName, TerrNumber, IsCheckedOut, IsLPCheckedOut, BroSisFirstNameLP, BroSisLastNameLP, CONVERT(char(10),CONVERT(datetime, CAST(checkedOutDate as varchar(12))),101) AS checkedOutDate, CONVERT(char(10),CONVERT(datetime, CAST(returnedDate as varchar(12))),101) AS returnedDate, CONVERT(char(10),CONVERT(datetime, CAST(lpcheckedOutDate as varchar(12))),101) AS lpcheckedOutDate, CONVERT(char(10),CONVERT(datetime, CAST(lpReturnedDate as varchar(12))),101) AS lpReturnedDate FROM Checkouts WHERE IsClosed < 1 ORDER BY checkedOutDate";

It's almost as if it's treating the date as a string. Does anybody know why, and how I can correct the issue? I need to use the CONVERT function because I don't want the whole 00:00:00 returned with each date. And I say it's the CONVERT function because if I take off the CONVERT on one of the fields such as checkedOutDate and try to sort by it, it sorts correctly.try changing your sql so that your select convert(...) as xyz uses different names...

eg. if you are converting checked_out_date don't select it as checked_out_date, try selecting it as checked_out_date1.

Alternatively include the order by date as another field in your query and don't convert it. You will need to select it as something else check order_checked_out_date and then sort on that field.

I don't know if it will work, but it's worth a try.|||Well, I tried your first idea, but it didn't work, but don't quite understand your second idea - I'm new to SQL so I was wondering if you could elaborate your explanation? Thanks|||does this help??

$query = "SELECT id, BroSisFirstName, BroSisLastName, TerrNumber,
IsCheckedOut, IsLPCheckedOut, BroSisFirstNameLP, .... etc .... CAST(lpReturnedDate as varchar(12))),101) AS lpReturnedDate, checkedOutDate as order_date FROM Checkouts WHERE IsClosed < 1 ORDER BY order_date";|||That did it! Thanks! Sorry for being so lame - like I said before, I'm just a newbie.|||no worries, it's not always easy to figure out what other people mean when you are swapping emails etc...

I'm not sure if that is the best way to do things, but I'm glad it worked. :)|||I'm surprised his original query did not sort correctly, even if he did use the same name as an alias. :confused:

I wonder if it would have worked if he had just fully qualified the field in the SORT statement:

"ORDER BY Checkouts.checkedOutDate"|||possible.... not sure to be honest... it kinda surprised me as well but then (no offense meant) it is a MS product and their behaviour can be a bit perverse. ;)|||I wonder if it has something to do with the interface he is using? It doesn't look like he's executing through query analyzer or a stored proc. Perhaps something is doing some independent interpretation of his code before it is sent to the server?

Hmmm...|||Well, what you said went over my head blindman, but if it helps, I'm just using PHP on Windows XP Pro w/Apache web server, and I'm executing my query through my PHP scripts.|||Now you are over my head.

It might be worth checking Current Activity Process Info in Enterprise Manager to see exactly what statement is being sent to SQL server.

Whatever works, I guess!|||Ok, I'll look for that and check it out - thanks.sql

Order By Parameter

Is it possible to use a variable like @.SelectOrderBy with the Order By of a stored procedure? or do I need several SP's?

Select *
FROM Table
Order By @.SelectOrderBy

I want to let a user select from a drop down on a webform the order by column to use.

ThanksYou'd either have seperate stored procedures (or a conditional within one stored procedure), or to implement it like your example above you would have to use dynamic SQL (look in Books Online for proper syntax, mine is iffy):

Declare @.strSQL VARCHAR(8000)
SELECT @.strSQL = "SELECT * FROM table ORDER BY " + @.SelectOrderBy
EXEC @.strSQL

It's not advisable to do it this way however, due to security and efficiencty issues. Stick with the conditional within one proc if it's not too complex!|||the other option is to pull the data back into a dataset and then use a dataview with a sort, passing in the column they selected.

cs

ORDER BY not working with ADO or OLE

I am inserting rows into an Excel file and the ORDER BY is coming out wrong.

When I run the select I get priority 1,2,3,4, ...10, 11, 12, ... as I should.

But in the excel file the rows come out 1,10,11,12,13 ..., 2, 20, 21, ...

SET @.sql = ' INSERT INTO OpenRowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;Database='+@.finalFile+';HDR=YES'',

''SELECT [ID],[Priority],[Comments] FROM [Sheet1$]'')

SELECT ID,priority,comments FROM OurTable WHERE orgId='+@.orgId+' ORDER BY priority,ID'

EXECUTE (@.sql)

Please help!! Thank you!

The order by is ordering them in string order. The type coming in must be a string type. Try converting them to integer to get the right order (e.g. ORDER BY convert(integer, priority.ID). That should clear things up.

Hope that helps,

John

order by killing me

sql2k sp2
Im aware that it normally slows everone down. But Ive got
a view, and when I select top 1 from it without an order
by it returns in 1 second. With order by takes over 4
minutes. Quite a jump. Ive got a Clustered Primary Key on
the column that Im ordering by. Any ideas?
TIA, Chris"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
What does the output from Query Analyzer > Show Plan indicate? How many rows
do you have?
Steve|||Please post DDL including index and with Execution plan and Statistics IO.
--
Satish Balusa
Corillian Corp.
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
> TIA, Chris
>|||Hi,
Do the select statement with out Order by clause,Since you have primary key
on that field , automatically data will be arranged in that order. In this
case you do not require a order by clause.
Thanks
Hari
MCDBA
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> sql2k sp2
> Im aware that it normally slows everone down. But Ive got
> a view, and when I select top 1 from it without an order
> by it returns in 1 second. With order by takes over 4
> minutes. Quite a jump. Ive got a Clustered Primary Key on
> the column that Im ordering by. Any ideas?
> TIA, Chris
>|||Hari,
For your information: if you want the rows of the resultset in a
particular order, you must specify an ORDER BY clause. Without it,
SQL-Server is at liberty to return the rows in any order it likes. At
one occasion this might accidentally be the order you desired, but at
another occasions a different order might result.
Gert-Jan
Hari Prasad wrote:
> Hi,
> Do the select statement with out Order by clause,Since you have primary key
> on that field , automatically data will be arranged in that order. In this
> case you do not require a order by clause.
> Thanks
> Hari
> MCDBA
> "chris" <anonymous@.discussions.microsoft.com> wrote in message
> news:281e01c3e108$bae3dff0$a401280a@.phx.gbl...
> > sql2k sp2
> >
> > Im aware that it normally slows everone down. But Ive got
> > a view, and when I select top 1 from it without an order
> > by it returns in 1 second. With order by takes over 4
> > minutes. Quite a jump. Ive got a Clustered Primary Key on
> > the column that Im ordering by. Any ideas?
> >
> > TIA, Chris
> >

Order By items not in the select list

Books online says this about "Order By":
The ORDER BY clause can include items not appearing in the select list.
Well, that's actually strange and surprising. This is surely not new to
many of you, but I just recently saw this construction:
Use Pubs
Select Top 5 * From Titles Order by Newid()
Which, of course, picks 5 titles randomly from the Titles table. It might
not be efficient for huge tables, but it's good to know that you can do
this. Other than this, I have NEVER seen an Order By that didn't reference
a column from the Select list, and I had no idea that it was possible -- I
must have glossed over that sentence in BOL since it had no more
explanation and it wouldn't have really make sense before I knew about it
(cognitive dissonance, I suppose).
Note: If you're using Windows 2000 or later, the implementation of Newid()
returns random IDs. In earlier operating systems, I think they were
sequential.
You have to read between the lines of that Select statement to figure out
that SQL must append the expression you supply to each row of the table,
then order the result set by that expression. Not that BOL bothers to
explain this, of course.
It's kind of poor that BOL has this one sentence but doesn't go on to
explain what happens when you order by something not in the select list.
Can I order by the values taken from a field in a different table? (Not as
far as I can tell.)
Can you do this:
Select Top 5 * From Titles Order by 'Hello there'
(Yes, you can, but it's not interesting.)
BOL should say "The ORDER BY clause can include items not appearing in the
select list, and when you do that, here's what happens..."
Are there any other cases where this feature of Order By could be useful,
other than with Newid()?
David Walker"DWalker" <none@.none.com> wrote in message
news:%23UutkJ$MFHA.3512@.TK2MSFTNGP15.phx.gbl...
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not
as
> far as I can tell.)
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
You can ORDER BY any valid scalar.
Yes, you can order by a value from another table. Use a correlated
subquery:
ORDER BY
(SELECT SomeColumn
FROM SomeOtherTable
WHERE SomeOtherTable.PK = YourTable.PK)
Or, it's often very useful to ORDER BY a CASE expression... For
instance, maybe you want to return all rows where SomeColumn = 99 first,
then sort the rest by SomeColumn ascending:
ORDER BY
CASE SomeColumn
WHEN 99 THEN 1
ELSE SomeColumn
END
.. There are lots of interesting things you can do.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||To add to Adam's reply, In general, this should occur anytime you want th
e
rows in an output resultset ordered by some value (albiet a direct Column
value or calculated expression) whose value you are not specifically
interested in on the CLient, other than as a mechanism by which to sort the
rows...
I often Order by a datetime expression, which is not output in the Select
clause,
It is frequently required that an Output column based on a datetime be
formatted as a user readable string, say "Tuesday, March 14" While actually
sorting on the internal datetime value of the same column, not on the string
which is output by the SQL .
"DWalker" wrote:

> Books online says this about "Order By":
> The ORDER BY clause can include items not appearing in the select list.
> Well, that's actually strange and surprising. This is surely not new to
> many of you, but I just recently saw this construction:
> Use Pubs
> Select Top 5 * From Titles Order by Newid()
> Which, of course, picks 5 titles randomly from the Titles table. It might
> not be efficient for huge tables, but it's good to know that you can do
> this. Other than this, I have NEVER seen an Order By that didn't referenc
e
> a column from the Select list, and I had no idea that it was possible -- I
> must have glossed over that sentence in BOL since it had no more
> explanation and it wouldn't have really make sense before I knew about it
> (cognitive dissonance, I suppose).
> Note: If you're using Windows 2000 or later, the implementation of Newid()
> returns random IDs. In earlier operating systems, I think they were
> sequential.
> You have to read between the lines of that Select statement to figure out
> that SQL must append the expression you supply to each row of the table,
> then order the result set by that expression. Not that BOL bothers to
> explain this, of course.
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not a
s
> far as I can tell.)
> Can you do this:
> Select Top 5 * From Titles Order by 'Hello there'
> (Yes, you can, but it's not interesting.)
> BOL should say "The ORDER BY clause can include items not appearing in the
> select list, and when you do that, here's what happens..."
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
>
> David Walker
>|||Thanks, Adam and cbretana. That helps.
David
"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:7B887787-2D11-4886-8CE7-C007F4F93603@.microsoft.com:
> To add to Adam's reply, In general, this should occur anytime you
> want the rows in an output resultset ordered by some value (albiet a
> direct Column value or calculated expression) whose value you are not
> specifically interested in on the CLient, other than as a mechanism by
> which to sort the rows...
> I often Order by a datetime expression, which is not output in the
> Select clause,
> It is frequently required that an Output column based on a datetime be
> formatted as a user readable string, say "Tuesday, March 14" While
> actually sorting on the internal datetime value of the same column,
> not on the string which is output by the SQL .
>
> "DWalker" wrote:
>|||On Tue, 29 Mar 2005 08:08:53 -0800, DWalker wrote:

>Thanks, Adam and cbretana. That helps.
>David
Hi David,
In addition to Adam's and CBretana's reply, I'd like to point out that
the ability to order by items that are not in the select list is a T-SQL
proprietary feature. If portability of your code is important, then it's
best to include the ordering columns in the select list, so that your
query is ANSI-compliant.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> In addition to Adam's and CBretana's reply, I'd like to point out that
> the ability to order by items that are not in the select list is a T-SQL
> proprietary feature. If portability of your code is important, then it's
> best to include the ordering columns in the select list, so that your
> query is ANSI-compliant.
Maybe not ANSI-compliant, but I think "proprietary" is too harsh a term.
This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
PostgreSQL, and others all support this, in addition to SQL Server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||OK, thanks. I'm sure we won't be moving this out of SQL 2000 except
possibly to SQL 2005, but it's good to know it's not strictly ANSI-
compliant.
David
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:OBsRlIKNFHA.244@.tk2msftngp13.phx.gbl:

> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> Maybe not ANSI-compliant, but I think "proprietary" is too harsh a
> term.
> This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
> PostgreSQL, and others all support this, in addition to SQL Server.
>