Order Issue

have a character field that represents a date in the format YYYYMM.
This data is broken down in three month blocks,
12 Dec
11 Nov
10 Oct
by this year and the corresponding 3 month block from last year.
So the data will look like this
200512
200511
200510
200412
200411
200410
I have a report that requires this order, but my result set is already being
put into another order and I want to know if I can convert the current YYYYM
M
format to a valid date.
Can this be done and how?
Thank you~~I'm not sure I follow you. If you have:
ORDER BY thecolumn DESC
don't you get the desired order? Also, why don't you store these as a smalld
atetime column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony W DiGrigoli" <AnthonyWDiGrigoli@.discussions.microsoft.com> wrote in
message
news:6368A787-C7D5-4DBA-B0DE-4E7D2B6473D3@.microsoft.com...
> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~|||You can use multiple expressions in the "order by" clause.
Example:
select c1, ..., cn
from table1
order by
cast(left(c1, 4) as int) desc, -- here you order by year
cast(right(c1, 2) as int) desc -- here by month
AMB
"Anthony W DiGrigoli" wrote:

> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~

Order Date prompt descending doesn't work

Hi,

In the report model I'm defining attribute date as ValueSelection: dropdown and SortDirection: Descending.

In the report builder I define the field as a prompt.

When I execute the report the values in the prompt date are order ascending and not descending.

This occur all over my model, can't order date field as ascending.

Any idea?

Thanks,

Assaf

In the report builder: There is a button "Sort and Group". Did you set the "Sort by" property of the date? I think that has to be set in order to sort right.|||

Hi,

Thanks for your reply.

The "Sort by" sort the records on the report.

My problem is that the sort in the prompt itself is always stay ascesnding although in the .NET I set it to descending

Any Idea?

Assaf

sql

order data

Hi all,
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
wa
The obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or a character
representation which sorts correctly), but we need to see what format you have for your date values
first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegr oups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>
|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa
|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegr oups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>
|||Thanks a lots!
I'm new on sql language.

order data

Hi all,
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
waThe obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or a character
representation which sorts correctly), but we need to see what format you have for your date values
first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegroups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegroups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>|||Thanks a lots!
I'm new on sql language.

order data

Hi all,
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
waThe obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or
a character
representation which sorts correctly), but we need to see what format you ha
ve for your date values
first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegroups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegroups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>|||Thanks a lots!
I'm new on sql language.

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

Hi,
I have a table with a field i sort on which has values like: p100-01,
p101-02, p999-05, p1000-03. On my order by shows these values like this:
p100-01
p1000-03
p101-02
p999-05
Is there a way around this or since its alpha numeric I am stuck with this?
ThanksYou can create a column that pads the strings out the way you want them
and do an ORDER BY it.
CASE WHEN foo_nbr LIKE 'p[0-9][0-9][0-9]-[0-9][0-9]'
THEN SUBSTRING (foo-nbr, 1,4) + '0' + SUBSTRING (foo-nbr,
5,7)
ELSE foo_nbr END AS sort_col|||if your pattern is always as illustrated (i.e., single
char+number+dash+number) then try
order by convert(decimal(30,5), replace(substring(value,2,9), '-', '.'))
you'll want to adjust the decimal precision and scale based on how many
numbers you'll have before and after the dash, if it's constant or at
least has a specified range.
also, this will not be a fast order, except on a small result set
Kyle wrote:
> Hi,
> I have a table with a field i sort on which has values like: p100-01,
> p101-02, p999-05, p1000-03. On my order by shows these values like this:
> p100-01
> p1000-03
> p101-02
> p999-05
> Is there a way around this or since its alpha numeric I am stuck with this
?
> Thanks
>|||... ["value" in this represents the column name] ...
Trey Walpole wrote:
> if your pattern is always as illustrated (i.e., single
> char+number+dash+number) then try
> order by convert(decimal(30,5), replace(substring(value,2,9), '-', '.'))
> you'll want to adjust the decimal precision and scale based on how many
> numbers you'll have before and after the dash, if it's constant or at
> least has a specified range.
> also, this will not be a fast order, except on a small result set
> Kyle wrote:
>|||On Fri, 9 Sep 2005 13:41:47 -0600, Kyle wrote:

>Hi,
>I have a table with a field i sort on which has values like: p100-01,
>p101-02, p999-05, p1000-03. On my order by shows these values like this:
>p100-01
>p1000-03
>p101-02
>p999-05
>Is there a way around this or since its alpha numeric I am stuck with this?
>Thanks
>
Hi Kyle,
This requirement indicates that the value is not atomic, but that the
numeric parts have different meaning. Instead of using a kludge to fix
the sorting, it's probably better to split the various parts of this
data into seperate columns, and concatenate them when selecting the
data.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I developed a query to do natural alphanumeric sorting. This query breaks
the alphanumeric string up into chunks of char's and number's, then orders b
y
the chunks. It only works for the first 4 chunks. I haven't had the time,
but I am sure that you could build a trigger to populate a,b,c and d columns
and sort by that.
Archer
select TOP 100 fieldid as 'sortcolumn',
LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1) as 'a',
CASE
WHEN right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) not
like '%[a-z,-]%'
then CAST(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1
) AS INT)
else
cast (
left (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
case
when patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) = 0
then patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 ))
else patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) -1
end
)
as int)
end
as 'b',
LEFT (
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
,
CASE
WHEN PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) = 0
THEN
LEN( right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
)
ELSE PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) -1
END
)
as 'c',
CASE
WHEN right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) not like '%[a-z,-]%'
then CAST(right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) AS INT)
else
cast (
left (
right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ),
case
when patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) = 0
then patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ))
else patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) -1
end
)
as int)
end
as 'd',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
) as 'cTEST'
from tblsamplesTEST
order by a,b,c,d
"Kyle" wrote:

> Hi,
> I have a table with a field i sort on which has values like: p100-01,
> p101-02, p999-05, p1000-03. On my order by shows these values like this:
> p100-01
> p1000-03
> p101-02
> p999-05
> Is there a way around this or since its alpha numeric I am stuck with this
?
> Thanks
>
>|||Kyle wrote:
> I have a table with a field i sort on which has values like: p100-01,
> p101-02, p999-05, p1000-03. On my order by shows these values like this:
> p100-01
> p1000-03
> p101-02
> p999-05
> Is there a way around this or since its alpha numeric I am stuck with this?[/color
]
If only the first number has a variable length, but the second number is
always two digits:
order by len(i), i
Dieter

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 problem

Hello,

I have a table with a field called 'time' which is type char.

The data which goes into this field is 1am, 2am, 3am, 4am ,5am, 6am, 7am, 8am ,9am, 10am, 11am, 12noon, 1pm, 2pm, 3pm, 4pm,5pm, 6pm, 7pm, 8pm, 9pm, 10pm, 11pm, 12mid.

As users enter the data the order can go out of sequence, so I want to be able to sort the order to go as above for each day.

Is there a way of doing custom ordering by number, then letter of data in the same field.
or any other suggestions are welcome.

Thanks
GoongSuggestion: Store military time (00:00-23:00) and they will sort fine. Then, create a method in the UI that will display that value in a reasonable format.

Order By Primary Key

I've got a table with an a primary key of type 'int' (auto incrementing).
Were talking about millions of rows.
The name of this field is TempID.
I always want to sort by the TempID ascending...is that something that I
even need to specify?
When I query it in Query Analyzer it seems to always be sorted by the
primary key ascending.
Although this is what I want, will this ALWAYS be the case, or should I just
throw an order by in there for good measure?
Stored Proc:
--
@.numberOfRows int,
@.startingID int
set rowcount @.numberOfRows
Select * From tblList Where TempID > @.startingID
set rowcount 0
--
Will this always work how I expect or should I add the "Order By TempID"?
I am asking because I don't know if my Order By clause will slow it down.
I'm sure I wouldn't notice if it was .0000000002 ms slower, but this would
be good to know, especially if @.startingID = 4,000,000 or the @.numberOfRows
is very large.
I hope that all made sense.Read the BOL for more information under "Clustered Indexes":
"An index on the columns specified in the ORDER BY or GROUP BY clause
may remove the need for the Database Engine to sort the data, because
the rows are already sorted. This improves query performance."
Didi you create them as Clustered keys ?
HTH, Jens Suessmeyer.|||INeedADip wrote:
> I've got a table with an a primary key of type 'int' (auto
> incrementing). Were talking about millions of rows.
> The name of this field is TempID.
> I always want to sort by the TempID ascending...is that something
> that I even need to specify?
> When I query it in Query Analyzer it seems to always be sorted by the
> primary key ascending.
> Although this is what I want, will this ALWAYS be the case, or should
> I just throw an order by in there for good measure?
> Stored Proc:
> --
> @.numberOfRows int,
> @.startingID int
> set rowcount @.numberOfRows
> Select * From tblList Where TempID > @.startingID
> set rowcount 0
> --
> Will this always work how I expect or should I add the "Order By
> TempID"? I am asking because I don't know if my Order By clause will
> slow it
> down. I'm sure I wouldn't notice if it was .0000000002 ms slower, but
> this would be good to know, especially if @.startingID = 4,000,000 or
> the @.numberOfRows is very large.
> I hope that all made sense.
The only way to guarantee the order of results is with an ORDER BY
clause. If that column is your PK, you should already have a unique
index on the column. If it's clustered, it will help with sorting. Keep
in mind that sort operations are very costly. While the unique,
clustered index on the PK column might eliminate the need for the sort,
it is no guarantee. You still need the ORDER BY clause.
David Gugick
Quest Software
www.quest.com|||You have a ton of basic mistakes and need to get an intro book on SQL.
Columns are not fields. An auto-increment can never be a relational
key by definition because it is not an attribute of the entities
modeled in the table. Tables by definition have no ordering.
You are confusing an implementation with SQL and proper data modeling.
If you want to be sure that you get your output in order, you need an
ORDER BY clause.
Years ago, SQL Server programmers got screwed up becuase they wrote
code that assumed a GROUP BY clause would be done with a sort under the
covers, so they did not write the ORDER clause. It blew up a ton of
programs on the next release.|||Wow...guess I hit a nerve.
I'm sorry, I should have picked my words a little more carefully.
I figured you would get the jist of it....
Why would auto-increment fields not be an attribute of the entities in the
table?
Isn't that one of the easiest ways to get a unique identifier for that set
of data, making it a key attribute?
"can never be a relational key"...I've never heard that argument.|||There are different schools of thought on using auto-increment fields as
keys. Personally, I tend to agree with Celko on this one. Auto-increment
fields have nothing to do with the data in the table, they are an artificial
way of creating uniqueness. A more meaningful key would suit my tastes.
Using meaningful keys allows you to
1. Reuse the column name and data type in all tables, which makes joins more
intuitive.
Job.deptid = department.deptid
is easier and more intuitive than
job.Department = department.id
2. Use a key value that makes some sort of sense when you look at it.
3. Avoid redundancy in your table and insure normalization.
Often when we have an auto-incremeneting key, there is another column or
columns that also uniquely identify the row.
However, there are many in the field (with as much experience as myself or
Celko) that consider auto-increment keys a requirement in their tables.
As for the terminology, do a search on celko and punch cards to see why he
makes such an issue of it. As long as everone knows what you mean by
"field" and "record" (and we do) I wouldn't lose any sleep over it.
"INeedADip" <INeedADip@.gmail.com> wrote in message
news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Wow...guess I hit a nerve.
> I'm sorry, I should have picked my words a little more carefully.
> I figured you would get the jist of it....
> Why would auto-increment fields not be an attribute of the entities in the
> table?
> Isn't that one of the easiest ways to get a unique identifier for that set
> of data, making it a key attribute?
> "can never be a relational key"...I've never heard that argument.
>|||So you guys are just refering to the name of the column?
I do have incrementing fields in 99% of my tables like AccountID, QueueID,
CampaignID, ect...
If that is what you guys are talking about, I agree....anyways...
Thanks for educating me on the Order By, I was under the impression that if
I used an index on the field, I could save some time by not using the Order
By. And just to note...I'm wasn't asking this question as a general rule,
but in this one case (this specific table) I was trying to save some time
because the table is so large and I will only be getting chunks in ascending
order EVERY TIME...but if it is not guaranteed, then I will add the "Order
By".
- Thanks|||Hi Jim,
I've got to take the bite and put the surrogate key side forward.
If a natural key exists on the table (that isn't in reality a generated
number in itself - for instance deptid would indicate its auto-generated
some how), most keys in the natural world are actually a surrogate of some
form or another.
There are problems duplicating the natural key everywhere, especially if its
a composite key; there is no difference in joining whether you use a
surrogate key or a natural one to join.
INeedADip - do a search on 'surrogate key' and 'identity' for some really
good and extensive discussions on the benefits of the surrogate key
approach.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
> There are different schools of thought on using auto-increment fields as
> keys. Personally, I tend to agree with Celko on this one. Auto-increment
> fields have nothing to do with the data in the table, they are an
> artificial
> way of creating uniqueness. A more meaningful key would suit my tastes.
> Using meaningful keys allows you to
> 1. Reuse the column name and data type in all tables, which makes joins
> more
> intuitive.
> Job.deptid = department.deptid
> is easier and more intuitive than
> job.Department = department.id
> 2. Use a key value that makes some sort of sense when you look at it.
> 3. Avoid redundancy in your table and insure normalization.
> Often when we have an auto-incremeneting key, there is another column
> or
> columns that also uniquely identify the row.
> However, there are many in the field (with as much experience as myself or
> Celko) that consider auto-increment keys a requirement in their tables.
> As for the terminology, do a search on celko and punch cards to see why he
> makes such an issue of it. As long as everone knows what you mean by
> "field" and "record" (and we do) I wouldn't lose any sleep over it.
>
> "INeedADip" <INeedADip@.gmail.com> wrote in message
> news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
>|||Thanks Tony.
I believe there was a fairly in depth discussion about this last w,
although I purge my postings frequently and can't locate it. If I recall
correctly, you were one of the folks involved in the discussion. Would you
mind posting a link to it?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> Hi Jim,
> I've got to take the bite and put the surrogate key side forward.
> If a natural key exists on the table (that isn't in reality a generated
> number in itself - for instance deptid would indicate its auto-generated
> some how), most keys in the natural world are actually a surrogate of some
> form or another.
> There are problems duplicating the natural key everywhere, especially if
its
> a composite key; there is no difference in joining whether you use a
> surrogate key or a natural one to join.
> INeedADip - do a search on 'surrogate key' and 'identity' for some really
> good and extensive discussions on the benefits of the surrogate key
> approach.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
Auto-increment
or
he
>|||I think this is one of them...
6d2a8ccf940fe2" target="_blank">http://groups.google.co.uk/group/co...
6d2a8ccf940fe2
but there are many, search on 'surrogate key' rogerson --celko--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eIiy5BGIGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Thanks Tony.
> I believe there was a fairly in depth discussion about this last w,
> although I purge my postings frequently and can't locate it. If I recall
> correctly, you were one of the folks involved in the discussion. Would
> you
> mind posting a link to it?
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> its
> Auto-increment
> or
> he
>

Order By Param

Is there a way to have a report parameter for the selection of what field
the user wants a table to be grouped on? For instance, the parameter would
have 3 options, clientid, clientname, and clientcity. I want to be able to
select one of these and have the table group on this selection.
Or any generic ways or ideas on how to accomplish this?
Thanks!!!I am looking for the same solution. I have a client that I have promised 10
reports to and they gave me a few but want sort capability on 5 different
fields. I could say that those are 5 different reports, but there should be
an easy way to list the fields in a drop down parameter and adjust the ORDER
BY based on this. Shouldn't there?
Thanks in advance.
ANDY
"Amon Borland" wrote:
> Is there a way to have a report parameter for the selection of what field
> the user wants a table to be grouped on? For instance, the parameter would
> have 3 options, clientid, clientname, and clientcity. I want to be able to
> select one of these and have the table group on this selection.
> Or any generic ways or ideas on how to accomplish this?
> Thanks!!!
>
>|||can you pass the field to sort on into the report as a parameter and use a
CASE in your query to conditionally sort based on whatever you passed in as
a parameter?
Bill
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:C80B4FC3-FA92-4ADE-BFEA-D6B0867FDE97@.microsoft.com...
> I am looking for the same solution. I have a client that I have promised
10
> reports to and they gave me a few but want sort capability on 5 different
> fields. I could say that those are 5 different reports, but there should
be
> an easy way to list the fields in a drop down parameter and adjust the
ORDER
> BY based on this. Shouldn't there?
> Thanks in advance.
> ANDY
> "Amon Borland" wrote:
> > Is there a way to have a report parameter for the selection of what
field
> > the user wants a table to be grouped on? For instance, the parameter
would
> > have 3 options, clientid, clientname, and clientcity. I want to be able
to
> > select one of these and have the table group on this selection.
> >
> > Or any generic ways or ideas on how to accomplish this?
> >
> > Thanks!!!
> >
> >
> >|||It seems all my replied are this..
Sorting is easy
DECLARE @.sql as varchar(100)
SET @.sql = 'SELECT * FROM blah WHERE blah = blah ORDER BY '
@.paramete
EXEC sp_executesql @.sq
Grouping on the other hand I don't think you can do dynamically

Order By nvarchar

I have a scenario to sort on an nvarchar (50) field and I need to keep any changes to the sproc in the ORDER BY clause if possible. This field will contain strings such as...

abc-217c, abc-15a, abc-9a, abc-7b, abc-17ar, etc...

The issue I'm having is when the records are sorted, they are returned as...

abc-15a, abc-17ar, abc-217c, abc-7b, abc-9a,etc...ordering numerically on the first numeric character in the string ie, 1,1,2,7,9)

However, I need the numeric component to be treated as a whole number and order in this fashion...

abc-7b, abc-9a, abc-15a, abc-17ar, abc-217c (7,9,15,17,217, etc)

I feel pretty sure that this issue has come up before...can anybody provide a working example that would provide a simple(or not so simple) solution?

Hello my friend,

The SQL to do this would be very complicated. If I were you, I would create extra fields in the table where these codes come from to hold parts of the code. For example: -

FullCode Name Number Section

abc-217c abc 217 c

By breaking this down, you can still select the FullCode and order by Name, Number, Section so it comes out right.

Kind regards

Scotty

|||

try code below:

create

table #test(aaanvarchar(50))

insert

into #test

Values

('abc-217c')

insert

into #test

Values

('abc-15a')

insert

into #test

Values

('abc-9a')

insert

into #test

Values

('abc-7b')

insert

into #test

Values

('abc-17ar')

select

*,substring(aaa, 1,charindex('-',aaa))+

case

whenisnumeric(substring(aaa,charindex('-',aaa)+1,4))= 1then'0'+substring(aaa,charindex('-',aaa)+1,4)whenisnumeric(substring(aaa,charindex('-',aaa)+1,3))= 1then'00'+substring(aaa,charindex('-',aaa)+1,3)whenisnumeric(substring(aaa,charindex('-',aaa)+1,2))= 1then'000'+substring(aaa,charindex('-',aaa)+1,2)whenisnumeric(substring(aaa,charindex('-',aaa)+1,1))= 1then'0000'+substring(aaa,charindex('-',aaa)+1,1)

else

''

end

+

case

whenisnumeric(substring(aaa,charindex('-',aaa)+1,4))= 1thensubstring(aaa,charindex('-',aaa)+5,50)whenisnumeric(substring(aaa,charindex('-',aaa)+1,3))= 1thensubstring(aaa,charindex('-',aaa)+4,50)whenisnumeric(substring(aaa,charindex('-',aaa)+1,2))= 1thensubstring(aaa,charindex('-',aaa)+3,50)whenisnumeric(substring(aaa,charindex('-',aaa)+1,1))= 1thensubstring(aaa,charindex('-',aaa)+2,50)

else

''

end

fieldToOrderBYfrom #test

order

by fieldToOrderBY

drop

table #test

Good luck

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 DATETIME field BUG?

I was under the impression that the DATETIME field was stored
internally in SQL server as
two integers?
Any idea how to get correct ordering of a datetime field?
I have two records containing a datetime field with these values and
this is the order that
order by displays them in when I say "select * from blah ORDER BY
datetimefield"
Dec 30 1989 11:55PM
Dec 31 1989 1:05PM
Dec 31 1989 7:15AM
Dec 31 1989 7:35PM
Dec 31 1989 9:10AM
Dec 31 1989 10:20AM
Dec 31 1989 11:10AM
Dec 31 1989 11:40AM
Jan 1 1990 1:20PM
Jan 1 1990 12:35PM
Why doesn't Order by on a datetime field sort correctly instead of
treating them like strings?Can you show us the result of sp_help tablename and/or the CREATE TABLE /
INSERT / SELECT to reproduce this problem? It looks like they are stored
and/or sorted as varchar, not datetime, or you are sorting the result of a
convert, not the column itself. (The most telltale thing is thathe result
does not contain seconds or milliseconds).
"wolfen42 at gmail.com" <wolfen42@.gmail.com> wrote in message
news:1150996807.502951.137230@.m73g2000cwd.googlegroups.com...
>I was under the impression that the DATETIME field was stored
> internally in SQL server as
> two integers?
> Any idea how to get correct ordering of a datetime field?
> I have two records containing a datetime field with these values and
> this is the order that
> order by displays them in when I say "select * from blah ORDER BY
> datetimefield"
> Dec 30 1989 11:55PM
> Dec 31 1989 1:05PM
> Dec 31 1989 7:15AM
> Dec 31 1989 7:35PM
> Dec 31 1989 9:10AM
> Dec 31 1989 10:20AM
> Dec 31 1989 11:10AM
> Dec 31 1989 11:40AM
> Jan 1 1990 1:20PM
> Jan 1 1990 12:35PM
> Why doesn't Order by on a datetime field sort correctly instead of
> treating them like strings?
>|||wolfen42 at gmail.com,

> Any idea how to get correct ordering of a datetime field?
Are you sure that column is a datetime one?. Seems like it is charecter data
type instead atetime. See next statements.
select
*
from
(
select cast('Dec 30 1989 11:55PM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 1:05PM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 7:15AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 7:35PM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 9:10AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 10:20AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 11:10AM' as varchar(25)) as c1 union all
select cast('Dec 31 1989 11:40AM' as varchar(25)) as c1 union all
select cast('Jan 1 1990 1:20PM' as varchar(25)) as c1 union all
select cast('Jan 1 1990 12:35PM' as varchar(25)) as c1
) as t1
order by
c1
select
*
from
(
select cast('Dec 30 1989 11:55PM' as datetime) as c1 union all
select cast('Dec 31 1989 1:05PM' as datetime) as c1 union all
select cast('Dec 31 1989 7:15AM' as datetime) as c1 union all
select cast('Dec 31 1989 7:35PM' as datetime) as c1 union all
select cast('Dec 31 1989 9:10AM' as datetime) as c1 union all
select cast('Dec 31 1989 10:20AM' as datetime) as c1 union all
select cast('Dec 31 1989 11:10AM' as datetime) as c1 union all
select cast('Dec 31 1989 11:40AM' as datetime) as c1 union all
select cast('Jan 1 1990 1:20PM' as datetime) as c1 union all
select cast('Jan 1 1990 12:35PM' as datetime) as c1
) as t1
order by
c1
go
AMB
"wolfen42 at gmail.com" wrote:

> I was under the impression that the DATETIME field was stored
> internally in SQL server as
> two integers?
> Any idea how to get correct ordering of a datetime field?
> I have two records containing a datetime field with these values and
> this is the order that
> order by displays them in when I say "select * from blah ORDER BY
> datetimefield"
> Dec 30 1989 11:55PM
> Dec 31 1989 1:05PM
> Dec 31 1989 7:15AM
> Dec 31 1989 7:35PM
> Dec 31 1989 9:10AM
> Dec 31 1989 10:20AM
> Dec 31 1989 11:10AM
> Dec 31 1989 11:40AM
> Jan 1 1990 1:20PM
> Jan 1 1990 12:35PM
> Why doesn't Order by on a datetime field sort correctly instead of
> treating them like strings?
>|||wolfen42 at gmail.com wrote:
> I was under the impression that the DATETIME field was stored
> internally in SQL server as
> two integers?
> Any idea how to get correct ordering of a datetime field?
> I have two records containing a datetime field with these values and
> this is the order that
> order by displays them in when I say "select * from blah ORDER BY
> datetimefield"
> Dec 30 1989 11:55PM
> Dec 31 1989 1:05PM
> Dec 31 1989 7:15AM
> Dec 31 1989 7:35PM
> Dec 31 1989 9:10AM
> Dec 31 1989 10:20AM
> Dec 31 1989 11:10AM
> Dec 31 1989 11:40AM
> Jan 1 1990 1:20PM
> Jan 1 1990 12:35PM
> Why doesn't Order by on a datetime field sort correctly instead of
> treating them like strings?
>
Are you sure that field is of type DATETIME? I just inserted these same
values into a table with a DATETIME field, and they sort properly. If I
put them into a VARCHAR field, then they do not.|||Can you provide a sample that works this way? It sorts finr for me.
CREATE TABLE JustADate
(Dt datetime)
INSERT JustADate VALUES('Dec 30 1989 11:55PM')
INSERT JustADate VALUES('Dec 31 1989 1:05PM')
INSERT JustADate VALUES('Dec 31 1989 7:15AM')
INSERT JustADate VALUES('Dec 31 1989 7:35PM')
INSERT JustADate VALUES('Dec 31 1989 9:10AM')
INSERT JustADate VALUES('Dec 31 1989 10:20AM')
INSERT JustADate VALUES('Dec 31 1989 11:10AM')
INSERT JustADate VALUES('Dec 31 1989 11:40AM')
INSERT JustADate VALUES('Jan 1 1990 1:20PM')
INSERT JustADate VALUES('Jan 1 1990 12:35PM')
SELECT *
FROM JustADate
ORDER BY Dt
Dt
--
1989-12-30 23:55:00.000
1989-12-31 07:15:00.000
1989-12-31 09:10:00.000
1989-12-31 10:20:00.000
1989-12-31 11:10:00.000
1989-12-31 11:40:00.000
1989-12-31 13:05:00.000
1989-12-31 19:35:00.000
1990-01-01 12:35:00.000
1990-01-01 13:20:00.000
Roy Harvey
Beacon Falls, CT
On 22 Jun 2006 10:20:07 -0700, "wolfen42 at gmail.com"
<wolfen42@.gmail.com> wrote:

>I was under the impression that the DATETIME field was stored
>internally in SQL server as
>two integers?
>Any idea how to get correct ordering of a datetime field?
>I have two records containing a datetime field with these values and
>this is the order that
>order by displays them in when I say "select * from blah ORDER BY
>datetimefield"
>Dec 30 1989 11:55PM
>Dec 31 1989 1:05PM
>Dec 31 1989 7:15AM
>Dec 31 1989 7:35PM
>Dec 31 1989 9:10AM
>Dec 31 1989 10:20AM
>Dec 31 1989 11:10AM
>Dec 31 1989 11:40AM
>Jan 1 1990 1:20PM
>Jan 1 1990 12:35PM
>Why doesn't Order by on a datetime field sort correctly instead of
>treating them like strings?|||Unless you do this of course
CREATE TABLE JustADate
(Dt datetime)
INSERT JustADate VALUES('Dec 30 1989 11:55PM')
INSERT JustADate VALUES('Dec 31 1989 1:05PM')
INSERT JustADate VALUES('Dec 31 1989 7:15AM')
INSERT JustADate VALUES('Dec 31 1989 7:35PM')
INSERT JustADate VALUES('Dec 31 1989 9:10AM')
INSERT JustADate VALUES('Dec 31 1989 10:20AM')
INSERT JustADate VALUES('Dec 31 1989 11:10AM')
INSERT JustADate VALUES('Dec 31 1989 11:40AM')
INSERT JustADate VALUES('Jan 1 1990 1:20PM')
INSERT JustADate VALUES('Jan 1 1990 12:35PM')
SELECT convert(varchar(20),dt,100) as Dt
FROM JustADate
ORDER BY Dt
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Roy Harvey wrote:
> Can you provide a sample that works this way? It sorts finr for me.
> CREATE TABLE JustADate
> (Dt datetime)
> INSERT JustADate VALUES('Dec 30 1989 11:55PM')
> INSERT JustADate VALUES('Dec 31 1989 1:05PM')
> INSERT JustADate VALUES('Dec 31 1989 7:15AM')
> INSERT JustADate VALUES('Dec 31 1989 7:35PM')
> INSERT JustADate VALUES('Dec 31 1989 9:10AM')
> INSERT JustADate VALUES('Dec 31 1989 10:20AM')
> INSERT JustADate VALUES('Dec 31 1989 11:10AM')
> INSERT JustADate VALUES('Dec 31 1989 11:40AM')
> INSERT JustADate VALUES('Jan 1 1990 1:20PM')
> INSERT JustADate VALUES('Jan 1 1990 12:35PM')
> SELECT *
> FROM JustADate
> ORDER BY Dt
> Dt
> --
> 1989-12-30 23:55:00.000
> 1989-12-31 07:15:00.000
> 1989-12-31 09:10:00.000
> 1989-12-31 10:20:00.000
> 1989-12-31 11:10:00.000
> 1989-12-31 11:40:00.000
> 1989-12-31 13:05:00.000
> 1989-12-31 19:35:00.000
> 1990-01-01 12:35:00.000
> 1990-01-01 13:20:00.000
> Roy Harvey
> Beacon Falls, CT
> On 22 Jun 2006 10:20:07 -0700, "wolfen42 at gmail.com"
> <wolfen42@.gmail.com> wrote:
>|||> SELECT convert(varchar(20),dt,100) as Dt
> FROM JustADate
> ORDER BY Dt
I think it would be more clear what you are getting at if you use a
different alias, e.g.
SELECT convert(varchar(20),dt,100) as DtConverted
FROM JustADate
ORDER BY DtConverted
or
SELECT convert(varchar(20),dt,100)
FROM JustADate
ORDER BY convert(varchar(20),dt,100)|||You know, all of the replies so far have been insisting that this isnt
the way a DATETIME field behaves and that it must be a varchar field
somehow.
I "KNEW" that it couldn't possibly be a varchar field because even
though the table was created by my predecessor, why would this be the
only date/time field in the table that was a varchar instead of a
datetime field?
Ahem... well... I've been reminded once again. If it's an issue that
assumes some sort of consistent behavior on the part of this particular
person... assume the worst.
Sigh.
Thanks for forcing me to look at the table structures one more time and
notice that yes... this one date/time data field was an nvarchar for no
good reason.
Tracy McKibben wrote:
> wolfen42 at gmail.com wrote:
> Are you sure that field is of type DATETIME? I just inserted these same
> values into a table with a DATETIME field, and they sort properly. If I
> put them into a VARCHAR field, then they do not.|||wolfen42 at gmail.com wrote:
> You know, all of the replies so far have been insisting that this isnt
> the way a DATETIME field behaves and that it must be a varchar field
> somehow.
> I "KNEW" that it couldn't possibly be a varchar field because even
> though the table was created by my predecessor, why would this be the
> only date/time field in the table that was a varchar instead of a
> datetime field?
> Ahem... well... I've been reminded once again. If it's an issue that
> assumes some sort of consistent behavior on the part of this particular
> person... assume the worst.
> Sigh.
> Thanks for forcing me to look at the table structures one more time and
> notice that yes... this one date/time data field was an nvarchar for no
> good reason.
>
Sometimes it pays to ask the obvious, easy questions... :-)

ORDER BY clause with unknown column name

can i use an unknown column in an ORDER BY clause with t-sql?
i know it will always be an identity field and it is in the first column.
it is also the primary key.
can i depend on a recordset always being in this order without the the
clause?> can i use an unknown column in an ORDER BY clause with t-sql?
> i know it will always be an identity field and it is in the first column.
> it is also the primary key.
> can i depend on a recordset always being in this order without the the
> clause?
Without the what clause?
Are you using SELECT *? Why? This is a preferably avoidable technique in
production code.
You can try using the constant 1, e.g.
SELECT column1, column2, column3
FROM dbo.Table
ORDER BY 1;
This will order by the first column, usually, but it can cause you problems
later, e.g. compare these:
SELECT
[2] = 'b',
[1] = 'a'
UNION
SELECT
[2] = 'a',
[1] = 'b'
ORDER BY 1;
SELECT
[2] = 'b',
[1] = 'a'
UNION
SELECT
[2] = 'a',
[1] = 'b'
ORDER BY [1];
(There are also some other funny rules and bugs I've seen by using constants
in ORDER BY, I can dig them up if need be... I think Steve Kass has posted a
few here.)
Also, if you are using SELECT * (did I mention this was terrible programming
practice and opens a can of barracudas?), can you really rely on your
co-workers to never change the column structure (either intentionally or
accidentally)?
It is trivial to generate a column list, either up front or on the fly, for
any table you are selecting from (especially if you only have to do it once,
e.g. when you create the view or procedure). So I'm not sure I believe that
you will be gaining anything by using * and not having to know the first
column name, because there are a lot of downsides.|||>> can i use an unknown column in an ORDER BY clause with t-sql?
There is no such thing as an unknown column in t-SQL. Use either a column
name or an alias or expression ( with certain limitations ) in the ORDER BY
clause to sort the data the way you want.
Disregarding the visual representation, to sort by the default identity
column in the table you can use:
ORDER BY $IDENTITY
Note that is is only applicable in SQL 2005.
No, you should not depend on any kind of ordering unless you explicitly
included the ORDER BY clause.
Anith|||In addition to what Aaron said, you have no guarantee that the column "n",
where n is the ordinal position of a column instead of a name, is the column
you actually want to order by. If the table were changed, columns added or
removed, or indexes altered, you could end up with a dog of a query trying
to order by column number.
"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:OvTSanVkGHA.4660@.TK2MSFTNGP05.phx.gbl...
> can i use an unknown column in an ORDER BY clause with t-sql?
> i know it will always be an identity field and it is in the first column.
> it is also the primary key.
> can i depend on a recordset always being in this order without the the
> clause?
>|||order by 1 should do the trick then.
thanks for the warings.
BTW - the column name is known it is just different in different tables.
i inherited what i have and don't want to cahnge too many things because i
have to submit scripts to the DBAs that have to be applied to a couple of
different DBs. just lazy i guess.
thanks much.|||>> can i use an unknown column in an ORDER BY clause with t-sql? <<
No, you have to sort by something. When do not put the ORDER BY in a
cursor (it is not part of a SELECT, another common newbie assumption),
then the engine can out the rows into a sequence in any order. Every
SQL product will be a bit different, depending on physical storage,
parallelism in the hardware, etc.
You might want to read a book and find out why IDENTITY can *never* be
a key. By definition. What you are doing is mimicing a 1950's magnetic
tape file in SQL. The IDENTITY is an exposed physical locator you are
using, the same way we used record positions on a mag tape.
No. This is the definition of a table -- it is a set without any
physical ordering. When you finally read a book on RDBMS, pay
attention to "The Information Prinicple" and some of the other rules
that Dr. Codd set up.
There are some proprietary kludges you can use to destroy portability
and data integrity. For example, there is a ordinal position number
that was removed from Standard SQL a few years ago, but exists in some
products.
All you will get in Newsgroups are the kludges; you need to get an
education. And it will take you at least a year to do that. Your
whole mindset is wrong and you have to unlearn a lot.|||I think the ORDER BY using an ordinal is getting deprecated in a future
version, I'm sure I've read it somewhere...
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXuBpsVkGHA.3536@.TK2MSFTNGP05.phx.gbl...
> Without the what clause?
> Are you using SELECT *? Why? This is a preferably avoidable technique in
> production code.
> You can try using the constant 1, e.g.
> SELECT column1, column2, column3
> FROM dbo.Table
> ORDER BY 1;
> This will order by the first column, usually, but it can cause you
> problems later, e.g. compare these:
> SELECT
> [2] = 'b',
> [1] = 'a'
> UNION
> SELECT
> [2] = 'a',
> [1] = 'b'
> ORDER BY 1;
> SELECT
> [2] = 'b',
> [1] = 'a'
> UNION
> SELECT
> [2] = 'a',
> [1] = 'b'
> ORDER BY [1];
> (There are also some other funny rules and bugs I've seen by using
> constants in ORDER BY, I can dig them up if need be... I think Steve Kass
> has posted a few here.)
> Also, if you are using SELECT * (did I mention this was terrible
> programming practice and opens a can of barracudas?), can you really rely
> on your co-workers to never change the column structure (either
> intentionally or accidentally)?
> It is trivial to generate a column list, either up front or on the fly,
> for any table you are selecting from (especially if you only have to do it
> once, e.g. when you create the view or procedure). So I'm not sure I
> believe that you will be gaining anything by using * and not having to
> know the first column name, because there are a lot of downsides.
>|||"mcnewsxp" <mcourter@.mindspring.com> wrote in message
news:eaZV4KWkGHA.1600@.TK2MSFTNGP04.phx.gbl...
> just lazy i guess.
Famous last words. Be very careful taking the easy/fast way out.
What saves you 10 minutes now, may very well cost you 10 hours later on.
List out all your columns, and specify in every script exactly which column
name you are ordering by. That way when someone changes a table or view, or
adds a column to your select statement, your code will still work.|||Yes, I think that's another danger, but I must confess I would probably find
some of that in my code were I to perform a formal review of the last 5
years of work. ;-)
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:e9x8m5WkGHA.1260@.TK2MSFTNGP05.phx.gbl...
>I think the ORDER BY using an ordinal is getting deprecated in a future
>version, I'm sure I've read it somewhere...|||> You might want to read a book and find out why IDENTITY can *never* be
> a key. By definition. What you are doing is mimicing a 1950's magnetic
> tape file in SQL. The IDENTITY is an exposed physical locator you are
> using, the same way we used record positions on a mag tape.
It can be a SURROGATE KEY without problem.
And, your definition re Codd and Date's work on surrogates is just plain
wrong as well.

> All you will get in Newsgroups are the kludges; you need to get an
> education. And it will take you at least a year to do that. Your
> whole mindset is wrong and you have to unlearn a lot.
Do you even realise how condesending and arrogant you sound?
You have plenty of weaknesses yourself.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1150477719.742027.253030@.i40g2000cwc.googlegroups.com...
> No, you have to sort by something. When do not put the ORDER BY in a
> cursor (it is not part of a SELECT, another common newbie assumption),
> then the engine can out the rows into a sequence in any order. Every
> SQL product will be a bit different, depending on physical storage,
> parallelism in the hardware, etc.
>
> You might want to read a book and find out why IDENTITY can *never* be
> a key. By definition. What you are doing is mimicing a 1950's magnetic
> tape file in SQL. The IDENTITY is an exposed physical locator you are
> using, the same way we used record positions on a mag tape.
>
> No. This is the definition of a table -- it is a set without any
> physical ordering. When you finally read a book on RDBMS, pay
> attention to "The Information Prinicple" and some of the other rules
> that Dr. Codd set up.
> There are some proprietary kludges you can use to destroy portability
> and data integrity. For example, there is a ordinal position number
> that was removed from Standard SQL a few years ago, but exists in some
> products.
> All you will get in Newsgroups are the kludges; you need to get an
> education. And it will take you at least a year to do that. Your
> whole mindset is wrong and you have to unlearn a lot.
>

ORDER BY clause not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.

>From an increasingly balding
Kaz> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegroups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
>
> Kaz
>|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegroups.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left )
Karen|||I'm glad I was able to help.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegroups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left )
> Karen
>

ORDER BY clause not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.

>From an increasingly balding
Kaz
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegr oups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
> Kaz
>
|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen
|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegrou ps.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>
|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left )
Karen
|||I'm glad I was able to help.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegr oups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left )
> Karen
>

ORDER BY clause not sorting correctly!

My problem is simple but I cannot see a way to get around it.
I am trying to do an ORDER BY on a varchar field, and it keeps putting
"_" (underscore) before "0" (zero).
i.e. I want it to work like
"_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
>From an increasingly balding
Kaz> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
What is the column collation? The collation determines sorting and
comparison rules. Consider:
CREATE TABLE dbo.foo
(
col1 varchar(10) COLLATE Latin1_General_CI_AS,
col2 varchar(10) COLLATE Latin1_General_BIN
)
INSERT INTO dbo.foo VALUES('0', '0')
INSERT INTO dbo.foo VALUES('_', '_')
SELECT * FROM dbo.foo
ORDER BY col1
SELECT * FROM dbo.foo
ORDER BY col2
GO
You can also specify an explicit collation in your ORDER BY, although
indexes can't be used to facilitate efficient ordering due to the COLLATE
expression:
SELECT * FROM dbo.foo
ORDER BY col1 COLLATE Latin1_General_BIN
--
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185417750.022600.219110@.i13g2000prf.googlegroups.com...
> My problem is simple but I cannot see a way to get around it.
> I am trying to do an ORDER BY on a varchar field, and it keeps putting
> "_" (underscore) before "0" (zero).
> i.e. I want it to work like
> "_" in ascii is 95, "0" in ascii is 48, therefore "0" comes first
> WHY IS IT DOING THIS?!!! I am pulling my hair out in frustration.
>>From an increasingly balding
> Kaz
>|||Thank you Dan for your response.
My place of work has two versions of SQL server running on different
machines - SQL Server 2000 and SQL Server 7.00.
This helps with the SQL Server 2000 machines, but won't work on SQL
Server 7.0.
Is there any way to get it sort correctly on SQL Server 7.0?
Karen|||> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
Yes, Karen. Collations were introduced in SQL Server 2000. In SQL 7 and
earlier versions, you can CAST are CONVERT to varbinary to get binary
sorting you want:
SELECT * FROM dbo.foo
ORDER BY CAST(col1AS varbinary(10))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185511629.895148.96300@.e9g2000prf.googlegroups.com...
> Thank you Dan for your response.
> My place of work has two versions of SQL server running on different
> machines - SQL Server 2000 and SQL Server 7.00.
> This helps with the SQL Server 2000 machines, but won't work on SQL
> Server 7.0.
> Is there any way to get it sort correctly on SQL Server 7.0?
> Karen
>|||Thank you SO much Dan, you have saved my sanity (or what little of it
I have left :) )
Karen|||I'm glad I was able to help.
--
Dan Guzman
SQL Server MVP
"kaz" <snow-berry@.animail.net> wrote in message
news:1185766768.945039.300220@.x35g2000prf.googlegroups.com...
> Thank you SO much Dan, you have saved my sanity (or what little of it
> I have left :) )
> Karen
>