ORDER BY Question

I have a table which contains a sNAME field - a persons name of "firstame space
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
...or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
Brian
Brian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>
|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query[vbcol=seagreen]
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
any
>
|||Jerry,
It worked and it was quick too...thanks again
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
>

ORDER BY Question

I have a table which contains a sNAME field - a persons name of "firstame space
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
> >I have a table which contains a sNAME field - a persons name of "firstame
> >space
> > lastname".
> >
> > I have a query that delivers a recordset using "ORDER BY sName"
> >
> > Now, a client has asked to see the data sorted by "lastname". Is there
any
> > method using an SQL expression to deliver that recordset sorted by
> > lastname?
> >
> > ..or do I have to re-organize my data fields and data?
> >
> > I was hoping for a quick 'sql function' solution instead of changing the
> > database.
> >
> > Brian
> >
>|||Jerry,
It worked and it was quick too...thanks again
--
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>>I have a table which contains a sNAME field - a persons name of "firstame
>>space
>> lastname".
>> I have a query that delivers a recordset using "ORDER BY sName"
>> Now, a client has asked to see the data sorted by "lastname". Is there
>> any
>> method using an SQL expression to deliver that recordset sorted by
>> lastname?
>> ..or do I have to re-organize my data fields and data?
>> I was hoping for a quick 'sql function' solution instead of changing the
>> database.
>> Brian
>

ORDER BY Question

I have a table which contains a sNAME field - a persons name of "firstame sp
ace
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
any[vbcol=seagreen]
>|||Jerry,
It worked and it was quick too...thanks again
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>sql

ORDER BY Issue on funky field names

Hello,
I am using FOR XML EXPLICIT

Problem is, I need to sort by [MyColumn!1!MyCol].

This column contains date in string format, And I want it to be sorted as if it was a date.

so I tried this

ORDER BY CONVERT(DATETIME, [MyColumn!1!MyCol])

it gives me error that ORDER BY items must be in select list
The whole query is actually a UNION of 2 queries

Please help me with this

Thanks,Order by has problems with column aliases. So try:
ORDER BY CONVERT(DATETIME, <statement for column value>)
i you have
...
, my_string_date+' '+my_string_time as [MyColumn!1!MyCol]
....

use

ORDER BY CONVERT(DATETIME, my_string_date+' '+my_string_time)

ORDER BY Issue on funky field names

Hello,
I am using FOR XML EXPLICIT

Problem is, I need to sort by [MyColumn!1!MyCol].

This column contains date in string format, And I want it to be sorted as if it was a date.

so I tried this

ORDER BY CONVERT(DATETIME, [MyColumn!1!MyCol])

it gives me error that ORDER BY items must be in select list
The whole query is actually a UNION of 2 queries

Please help me with this

Thanks,Order by has problems with column aliases. So try:
ORDER BY CONVERT(DATETIME, <statement for column value>)
i you have
...
, my_string_date+' '+my_string_time as [MyColumn!1!MyCol]
....

use

ORDER BY CONVERT(DATETIME, my_string_date+' '+my_string_time)

Order by clause help

Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:

>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Order by clause help

Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
Try:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegrou ps.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:

>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Order by clause help

Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:
>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP

order by and UNION

Hi, I got an error "ORDER BY items must appear in the select list if the
statement contains a UNION operator." when trying to put an order clause at
the end ot the union query.
How to set order to the whole set? Thanks.That will order the entire set, if the column exists. Does it? Can you
post the code you're having trouble with?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"js" <js@.someone@.hotmail.com> wrote in message
news:%23FAx7z1IFHA.1248@.TK2MSFTNGP10.phx.gbl...
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause
at
> the end ot the union query.
> How to set order to the whole set? Thanks.
>|||When using the ORDER BY clause in a query with a UNION, you must adhere
to the ANSI SQL-92 standard, which says you cannot use expressions in
the ORDER BY clause, but only the names or ordinal positions of the
columns in the resultset.
Hope this helps,
Gert-Jan
js wrote:
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause a
t
> the end ot the union query.
> How to set order to the whole set? Thanks.|||select colA,colB
from tableA
union
select stuff as colA,blah as ColB
from tableB
order by 1,2 asc
// 1 = colA and 2=colB
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"js" <js@.someone@.hotmail.com> wrote in message
news:%23FAx7z1IFHA.1248@.TK2MSFTNGP10.phx.gbl...
> Hi, I got an error "ORDER BY items must appear in the select list if the
> statement contains a UNION operator." when trying to put an order clause
> at the end ot the union query.
> How to set order to the whole set? Thanks.
>

ORDER - varchar column contains Char & int values

Hi,
I have a table with a Varchar column(col1) contains values as below,
Low
Upper
4
Lp
31
1
2
Jack
3
default order/order by col1 is
1
2
3
31
4
Jack
Low
Lp
Upper
I want to order this column in such a way that
blank spaces first, numbers second(in ascending) and alphabet...
1
2
3
4
31
Jack
Low
Lp
Upper
Please advise.
Thanks,
Soura
USE tempdb
GO
CREATE TABLE a (cola varchar(10))
GO
INSERT INTO a VALUES('Low')
INSERT INTO a VALUES('Upper')
INSERT INTO a VALUES('4')
INSERT INTO a VALUES('Lp')
INSERT INTO a VALUES('31')
INSERT INTO a VALUES('1')
INSERT INTO a VALUES('2')
INSERT INTO a VALUES('Jack')
INSERT INTO a VALUES('3')
GO
SELECT cola,
CASE
WHEN ISNUMERIC(cola) = 1 THEN CAST(cola AS bigint)
ELSE 9223372036854775807 -- max bigint
END AS ordcol
FROM a
ORDER BY ordcol, cola
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:B7D8A70A-C130-4C6C-9CDF-B453380E810D@.microsoft.com...
> Hi,
> I have a table with a Varchar column(col1) contains values as below,
> Low
> Upper
> 4
> Lp
> 31
> 1
> 2
> Jack
> 3
> default order/order by col1 is
> 1
> 2
> 3
> 31
> 4
> Jack
> Low
> Lp
> Upper
>
> I want to order this column in such a way that
> blank spaces first, numbers second(in ascending) and alphabet...
> 1
> 2
> 3
> 4
> 31
> Jack
> Low
> Lp
> Upper
> Please advise.
> Thanks,
> Soura
|||On Mon, 5 Dec 2005 07:05:03 -0800, SouRa wrote:

>Hi,
>I have a table with a Varchar column(col1) contains values as below,
>Low
>Upper
>4
>Lp
>31
>1
>2
>Jack
>3
>default order/order by col1 is
>1
>2
>3
>31
>4
>Jack
>Low
>Lp
>Upper
>
>I want to order this column in such a way that
>blank spaces first, numbers second(in ascending) and alphabet...
>1
>2
>3
>4
>31
>Jack
>Low
>Lp
>Upper
>Please advise.
>Thanks,
>Soura
Hi Soura,
ORDER BY CASE WHEN col1 = ' ' THEN 1
WHEN col1 NOT LIKE '%[^0-9]%' THEN 2
ELSE 3
END,
CASE WHEN col1 NOT LIKE '%[^0-9]%' THEN CAST(col1 AS int) END,
col1
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

ORDER - varchar column contains Char & int values

Hi,
I have a table with a Varchar column(col1) contains values as below,
Low
Upper
4
Lp
31
1
2
Jack
3
default order/order by col1 is
1
2
3
31
4
Jack
Low
Lp
Upper
I want to order this column in such a way that
blank spaces first, numbers second(in ascending) and alphabet...
1
2
3
4
31
Jack
Low
Lp
Upper
Please advise.
Thanks,
SouraUSE tempdb
GO
CREATE TABLE a (cola varchar(10))
GO
INSERT INTO a VALUES('Low')
INSERT INTO a VALUES('Upper')
INSERT INTO a VALUES('4')
INSERT INTO a VALUES('Lp')
INSERT INTO a VALUES('31')
INSERT INTO a VALUES('1')
INSERT INTO a VALUES('2')
INSERT INTO a VALUES('Jack')
INSERT INTO a VALUES('3')
GO
SELECT cola,
CASE
WHEN ISNUMERIC(cola) = 1 THEN CAST(cola AS bigint)
ELSE 9223372036854775807 -- max bigint
END AS ordcol
FROM a
ORDER BY ordcol, cola
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:B7D8A70A-C130-4C6C-9CDF-B453380E810D@.microsoft.com...
> Hi,
> I have a table with a Varchar column(col1) contains values as below,
> Low
> Upper
> 4
> Lp
> 31
> 1
> 2
> Jack
> 3
> default order/order by col1 is
> 1
> 2
> 3
> 31
> 4
> Jack
> Low
> Lp
> Upper
>
> I want to order this column in such a way that
> blank spaces first, numbers second(in ascending) and alphabet...
> 1
> 2
> 3
> 4
> 31
> Jack
> Low
> Lp
> Upper
> Please advise.
> Thanks,
> Soura|||On Mon, 5 Dec 2005 07:05:03 -0800, SouRa wrote:
>Hi,
>I have a table with a Varchar column(col1) contains values as below,
>Low
>Upper
>4
>Lp
>31
>1
>2
>Jack
>3
>default order/order by col1 is
>1
>2
>3
>31
>4
>Jack
>Low
>Lp
>Upper
>
>I want to order this column in such a way that
>blank spaces first, numbers second(in ascending) and alphabet...
>1
>2
>3
>4
>31
>Jack
>Low
>Lp
>Upper
>Please advise.
>Thanks,
>Soura
Hi Soura,
ORDER BY CASE WHEN col1 = ' ' THEN 1
WHEN col1 NOT LIKE '%[^0-9]%' THEN 2
ELSE 3
END,
CASE WHEN col1 NOT LIKE '%[^0-9]%' THEN CAST(col1 AS int) END,
col1
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

ORDER - varchar column contains Char & int values

Hi,
I have a table with a Varchar column(col1) contains values as below,
Low
Upper
4
Lp
31
1
2
Jack
3
default order/order by col1 is
1
2
3
31
4
Jack
Low
Lp
Upper
I want to order this column in such a way that
blank spaces first, numbers second(in ascending) and alphabet...
1
2
3
4
31
Jack
Low
Lp
Upper
Please advise.
Thanks,
SouraUSE tempdb
GO
CREATE TABLE a (cola varchar(10))
GO
INSERT INTO a VALUES('Low')
INSERT INTO a VALUES('Upper')
INSERT INTO a VALUES('4')
INSERT INTO a VALUES('Lp')
INSERT INTO a VALUES('31')
INSERT INTO a VALUES('1')
INSERT INTO a VALUES('2')
INSERT INTO a VALUES('Jack')
INSERT INTO a VALUES('3')
GO
SELECT cola,
CASE
WHEN ISNUMERIC(cola) = 1 THEN CAST(cola AS bigint)
ELSE 9223372036854775807 -- max bigint
END AS ordcol
FROM a
ORDER BY ordcol, cola
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:B7D8A70A-C130-4C6C-9CDF-B453380E810D@.microsoft.com...
> Hi,
> I have a table with a Varchar column(col1) contains values as below,
> Low
> Upper
> 4
> Lp
> 31
> 1
> 2
> Jack
> 3
> default order/order by col1 is
> 1
> 2
> 3
> 31
> 4
> Jack
> Low
> Lp
> Upper
>
> I want to order this column in such a way that
> blank spaces first, numbers second(in ascending) and alphabet...
> 1
> 2
> 3
> 4
> 31
> Jack
> Low
> Lp
> Upper
> Please advise.
> Thanks,
> Soura|||On Mon, 5 Dec 2005 07:05:03 -0800, SouRa wrote:

>Hi,
>I have a table with a Varchar column(col1) contains values as below,
>Low
>Upper
>4
>Lp
>31
>1
>2
>Jack
>3
>default order/order by col1 is
>1
>2
>3
>31
>4
>Jack
>Low
>Lp
>Upper
>
>I want to order this column in such a way that
>blank spaces first, numbers second(in ascending) and alphabet...
>1
>2
>3
>4
>31
>Jack
>Low
>Lp
>Upper
>Please advise.
>Thanks,
>Soura
Hi Soura,
ORDER BY CASE WHEN col1 = ' ' THEN 1
WHEN col1 NOT LIKE '%[^0-9]%' THEN 2
ELSE 3
END,
CASE WHEN col1 NOT LIKE '%[^0-9]%' THEN CAST(col1 AS int) END,
col1
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Oracle stored proc with OUT parameter

Hi,

I am calling an Oracle stored proc which contains an IN and an OUT parameter also.

To the stored proc, I pass two reports parameteres. I get following error when I execute the report:

PLS-00306: wrong number or types of arguments in call to <Procedure name>

Where am I going wrong?

TIA,

Tanmaya

Out parameters for Oracle stored procedures are not supported. Only an OUT REF cursor is supported. Basically, you need to write a wrapper stored procedure that has only one OUT REF cursor and no other OUT parameters.

-- Robert