Order by?

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

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

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

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

sql

ORDER BY with SELECT DISTINCT

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

Order by question, pls help

Hello,
I need to sort my data by location name, but the first record I need to
return is the one with location "Corporate" and then location name from A-Z.
How can I use order by to return "Corporate" records first and then return
"Atlanta", "Boston"...?
Thanks in advance!ORDER BY CASE LocationName WHEN 'Corporate' THEN 1 ELSE 2 END, LocationName;
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:072B9390-EFDB-4A9C-8D2B-91399F6AB0C8@.microsoft.com...
> Hello,
> I need to sort my data by location name, but the first record I need to
> return is the one with location "Corporate" and then location name from
> A-Z.
> How can I use order by to return "Corporate" records first and then return
> "Atlanta", "Boston"...?
> Thanks in advance!|||Try:
select
*
from
MyTable
order by
case when Location = 'Corporate' then 0 else 1 end
, Location
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jean" <Jean@.discussions.microsoft.com> wrote in message
news:072B9390-EFDB-4A9C-8D2B-91399F6AB0C8@.microsoft.com...
Hello,
I need to sort my data by location name, but the first record I need to
return is the one with location "Corporate" and then location name from A-Z.
How can I use order by to return "Corporate" records first and then return
"Atlanta", "Boston"...?
Thanks in advance!|||Tom / Aaron,
Thanks for your help! It works great!
Jean
"Jean" wrote:

> Hello,
> I need to sort my data by location name, but the first record I need to
> return is the one with location "Corporate" and then location name from A-
Z.
> How can I use order by to return "Corporate" records first and then return
> "Atlanta", "Boston"...?
> Thanks in advance!

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 problem within a View

Hi,

I have created a view which uses 3 tables, i also have a sort on one of the columns. However when I open the view the sort does not work. It does however sort the view correctly when executing the query within design view

Can anyone explain this or is it a bug within SQL Server Express 2005?

thanks

David

This is an expected behavior. Any ORDER BY that is attached to a view when it is defined is basically ignored. If you want the columns of a view to be ordered, the order by must be specified at the time you construct your actual query from the view. The long and short of it is that you cannot pre-set the ORDER BY behavior by definiting it as part of a view definition.

ORDER BY parameter

How can I write that SORT depends on parameter, something like:
ORDER BY column1 case when @.param=0 then DESC else ASC end
Regards,SStandard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:
SELECT
CASE @.flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @.flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
..
CASE @.flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
CASE @.flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.
SELECT ...
CASE WHEN @.flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @.flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @.flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;|||hi,
probably this link should answer your question:
http://www.aspfaq.com/2501
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"simon" wrote:

> How can I write that SORT depends on parameter, something like:
> ORDER BY column1 case when @.param=0 then DESC else ASC end
> Regards,S
>
>sql

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 in Strore Prod

I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody hel
p?
CREATE PROCEDURE SP_GetCorpFinalReport
(
@.SortOrder nvarchar
)
AS
SELECT * FROM CorpFinalReport Order By
CASE @.SortOrder
WHEN 'custno' THEN custno
WHEN 'gross' THEN gross
WHEN 'net' THEN net
WHEN 'npv' THEN npv
WHEN 'plimpact' THEN plimpact
--else RecNo
end DESC
GO
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...de59ba32b430c3
*****************************************
some response please!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...l-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...680a157c0dfad7
*****************************************
|||Gabe,
First of all, be sure to change the declaration of @.SortOrder, because
it only holds 1 character now nvarchar is the same as nvarchar(1).
Sysname or nvarchar(bigger-number) is ok.
Then try something like this:
create procedure yourProcedure (
@.SortOrder sysname
) as
select * from CorpFinalReport
order by
case when @.SortOrder = 'custno' then custno end desc,
case when @.SortOrder = 'gross' then gross end desc,
case when @.SortOrder = 'net' then net end desc,
case when @.SortOrder = 'npv' then npv end desc,
case when @.SortOrder = 'plimpact' then plimpact end desc,
RecNo desc
Also, it is not a good idea to name your stored procedure SP_...,
because names that begin with sp_ have owner/db resolution rules that
are designed for system stored procedures.
Steve Kass
Drew University
Gabe Wong via SQLMonster.com wrote:

>I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody he
lp?
>CREATE PROCEDURE SP_GetCorpFinalReport
>(
>@.SortOrder nvarchar
>)
>AS
>SELECT * FROM CorpFinalReport Order By
>CASE @.SortOrder
>WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
>WHEN 'npv' THEN npv
>WHEN 'plimpact' THEN plimpact
>--else RecNo
>end DESC
>GO
>*****************************************
>* This message was posted via http://www.sqlmonster.com
>*
>* Report spam or abuse by clicking the following URL:
>* http://www.sqlmonster.com/Uwe/Abuse...de59ba32b430c3
>*****************************************
>
|||You are going to have to handle that by executing a SQL string like:
DECLARE @.SQL VARCHAR(1000)
SELECT @.SQL='SELECT * FROM CorpFinalRport ORDER BY '+@.SortOrder
EXEC(@.SQL)
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c7db6820f2c6415584de59ba32b430c3@.SQLMonster.c om...
> I want to order by a table dynamicly base on user criteria. If user ckick
on gross, the table sort by gross. Below is my strore prod. The table is
displayed but when user click on gross, the data remain same, in other
words, it not sorted! Can anybody help?
> CREATE PROCEDURE SP_GetCorpFinalReport
> (
> @.SortOrder nvarchar
> )
> AS
> SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
> GO
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.sqlmonster.com/Uwe/Abuse...de59ba32b430c3
> *****************************************
|||It worl find now, but one more thing, it order by Gross value, the value order by the first char only, why? for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
is it i should write : order by max(groos)?
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...l-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...b0c07e0de1186a
*****************************************
|||What datatype is Gross of in your table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:2b1b743f2fd34951a7b0c07e0de1186a@.SQLMonster.c om...
> It worl find now, but one more thing, it order by Gross value, the value order by the first char
only, why? for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> is it i should write : order by max(groos)?
> *****************************************
> * A copy of the whole thread can be found at:
> * http://www.sqlmonster.com/Uwe/Forum...l-server/17779
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse...b0c07e0de1186a
> *****************************************
|||i CAST it to int, now it work perfectly, Thanks ALL!!! if posible come to Malaysia, i buy u dinner!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum...l-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...bba0c949a38069
*****************************************
|||> some response please!
Geez, have patience. You did post well outside of business hours in the
western hemisphere...
sql

Order By in Strore Prod

I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody help?
CREATE PROCEDURE SP_GetCorpFinalReport
(
@.SortOrder nvarchar
)
AS
SELECT * FROM CorpFinalReport Order By
CASE @.SortOrder
WHEN 'custno' THEN custno
WHEN 'gross' THEN gross
WHEN 'net' THEN net
WHEN 'npv' THEN npv
WHEN 'plimpact' THEN plimpact
--else RecNo
end DESC
GO
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3
*****************************************some response please!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=d0adf5a5f71c4ebc98680a157c0dfad7
*****************************************|||Gabe,
First of all, be sure to change the declaration of @.SortOrder, because
it only holds 1 character now nvarchar is the same as nvarchar(1).
Sysname or nvarchar(bigger-number) is ok.
Then try something like this:
create procedure yourProcedure (
@.SortOrder sysname
) as
select * from CorpFinalReport
order by
case when @.SortOrder = 'custno' then custno end desc,
case when @.SortOrder = 'gross' then gross end desc,
case when @.SortOrder = 'net' then net end desc,
case when @.SortOrder = 'npv' then npv end desc,
case when @.SortOrder = 'plimpact' then plimpact end desc,
RecNo desc
Also, it is not a good idea to name your stored procedure SP_...,
because names that begin with sp_ have owner/db resolution rules that
are designed for system stored procedures.
Steve Kass
Drew University
Gabe Wong via SQLMonster.com wrote:
>I want to order by a table dynamicly base on user criteria. If user ckick on gross, the table sort by gross. Below is my strore prod. The table is displayed but when user click on gross, the data remain same, in other words, it not sorted! Can anybody help?
>CREATE PROCEDURE SP_GetCorpFinalReport
>(
>@.SortOrder nvarchar
>)
>AS
>SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
>GO
>*****************************************
>* This message was posted via http://www.sqlmonster.com
>*
>* Report spam or abuse by clicking the following URL:
>* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3
>*****************************************
>|||You are going to have to handle that by executing a SQL string like:
DECLARE @.SQL VARCHAR(1000)
SELECT @.SQL='SELECT * FROM CorpFinalRport ORDER BY '+@.SortOrder
EXEC(@.SQL)
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c7db6820f2c6415584de59ba32b430c3@.SQLMonster.com...
> I want to order by a table dynamicly base on user criteria. If user ckick
on gross, the table sort by gross. Below is my strore prod. The table is
displayed but when user click on gross, the data remain same, in other
words, it not sorted! Can anybody help?
> CREATE PROCEDURE SP_GetCorpFinalReport
> (
> @.SortOrder nvarchar
> )
> AS
> SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
> GO
> *****************************************
> * This message was posted via http://www.sqlmonster.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3
> *****************************************|||It worl find now, but one more thing, it order by Gross value, the value order by the first char only, why? for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
is it i should write : order by max(groos)?
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=2b1b743f2fd34951a7b0c07e0de1186a
*****************************************|||What datatype is Gross of in your table?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gabe Wong via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:2b1b743f2fd34951a7b0c07e0de1186a@.SQLMonster.com...
> It worl find now, but one more thing, it order by Gross value, the value order by the first char
only, why? for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> is it i should write : order by max(groos)?
> *****************************************
> * A copy of the whole thread can be found at:
> * http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/17779
> *
> * Report spam or abuse by clicking the following URL:
> * http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=2b1b743f2fd34951a7b0c07e0de1186a
> *****************************************|||i CAST it to int, now it work perfectly, Thanks ALL!!! if posible come to Malaysia, i buy u dinner!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/17779
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=b40370fa97644873a0bba0c949a38069
*****************************************|||> some response please!
Geez, have patience. You did post well outside of business hours in the
western hemisphere...

Order By in Strore Prod

I want to order by a table dynamicly base on user criteria. If user ckick on
gross, the table sort by gross. Below is my strore prod. The table is displ
ayed but when user click on gross, the data remain same, in other words, it
not sorted! Can anybody hel
p?
CREATE PROCEDURE SP_GetCorpFinalReport
(
@.SortOrder nvarchar
)
AS
SELECT * FROM CorpFinalReport Order By
CASE @.SortOrder
WHEN 'custno' THEN custno
WHEN 'gross' THEN gross
WHEN 'net' THEN net
WHEN 'npv' THEN npv
WHEN 'plimpact' THEN plimpact
--else RecNo
end DESC
GO
****************************************
*
* This message was posted via http://www.droptable.com
*
* Report spam or abuse by clicking the following URL:
* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3[/u
rl]
****************************************
*some response please!
****************************************
*
* A copy of the whole thread can be found at:
* http://www.droptable.com/Uwe/Forum...ql-server/17779
*
* Report spam or abuse by clicking the following URL:
* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=d0adf5a5f71c4ebc98680a157c0dfad7[/u
rl]
****************************************
*|||Gabe,
First of all, be sure to change the declaration of @.SortOrder, because
it only holds 1 character now nvarchar is the same as nvarchar(1).
Sysname or nvarchar(bigger-number) is ok.
Then try something like this:
create procedure yourProcedure (
@.SortOrder sysname
) as
select * from CorpFinalReport
order by
case when @.SortOrder = 'custno' then custno end desc,
case when @.SortOrder = 'gross' then gross end desc,
case when @.SortOrder = 'net' then net end desc,
case when @.SortOrder = 'npv' then npv end desc,
case when @.SortOrder = 'plimpact' then plimpact end desc,
RecNo desc
Also, it is not a good idea to name your stored procedure SP_...,
because names that begin with sp_ have owner/db resolution rules that
are designed for system stored procedures.
Steve Kass
Drew University
Gabe Wong via droptable.com wrote:

>I want to order by a table dynamicly base on user criteria. If user ckick on gross,
the table sort by gross. Below is my strore prod. The table is displayed but when u
ser click on gross, the data remain same, in other words, it not sorted! Can anybody
he
lp?
>CREATE PROCEDURE SP_GetCorpFinalReport
>(
>@.SortOrder nvarchar
> )
>AS
>SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
>GO
> ****************************************
*
>* This message was posted via http://www.droptable.com
>*
>* Report spam or abuse by clicking the following URL:
>* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=c7db6820f2c6415584de59ba32b430c3[/
url]
> ****************************************
*
>|||You are going to have to handle that by executing a SQL string like:
DECLARE @.SQL VARCHAR(1000)
SELECT @.SQL='SELECT * FROM CorpFinalRport ORDER BY '+@.SortOrder
EXEC(@.SQL)
"Gabe Wong via droptable.com" <forum@.droptable.com> wrote in message
news:c7db6820f2c6415584de59ba32b430c3@.SQ
droptable.com...
> I want to order by a table dynamicly base on user criteria. If user ckick
on gross, the table sort by gross. Below is my strore prod. The table is
displayed but when user click on gross, the data remain same, in other
words, it not sorted! Can anybody help?
> CREATE PROCEDURE SP_GetCorpFinalReport
> (
> @.SortOrder nvarchar
> )
> AS
> SELECT * FROM CorpFinalReport Order By
> CASE @.SortOrder
> WHEN 'custno' THEN custno
> WHEN 'gross' THEN gross
> WHEN 'net' THEN net
> WHEN 'npv' THEN npv
> WHEN 'plimpact' THEN plimpact
> --else RecNo
> end DESC
> GO
> ****************************************
*
> * This message was posted via http://www.droptable.com
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.droptable.com/Uwe/Abuse...4de59ba32b430c3">
> ****************************************
*|||It worl find now, but one more thing, it order by Gross value, the value ord
er by the first char only, why? for example i have 3 value
132
222
1122
now it order by
222
132
1122
but what i want is the biggest come first.
is it i should write : order by max(groos)?
****************************************
*
* A copy of the whole thread can be found at:
* http://www.droptable.com/Uwe/Forum...ql-server/17779
*
* Report spam or abuse by clicking the following URL:
* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=2b1b743f2fd34951a7b0c07e0de1186a[/u
rl]
****************************************
*|||What datatype is Gross of in your table?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gabe Wong via droptable.com" <forum@.droptable.com> wrote in message
news:2b1b743f2fd34951a7b0c07e0de1186a@.SQ
droptable.com...
> It worl find now, but one more thing, it order by Gross value, the value order by
the first char
only, why? for example i have 3 value
> 132
> 222
> 1122
> now it order by
> 222
> 132
> 1122
> but what i want is the biggest come first.
> is it i should write : order by max(groos)?
> ****************************************
*
> * A copy of the whole thread can be found at:
> * http://www.droptable.com/Uwe/Forum...ql-server/17779
> *
> * Report spam or abuse by clicking the following URL:
> * [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=2b1b743f2fd34951a7b0c07e0de1186a[
/url]
> ****************************************
*|||i CAST it to int, now it work perfectly, Thanks ALL!!! if posible come to Ma
laysia, i buy u dinner!
****************************************
*
* A copy of the whole thread can be found at:
* http://www.droptable.com/Uwe/Forum...ql-server/17779
*
* Report spam or abuse by clicking the following URL:
* [url]http://www.droptable.com/Uwe/Abuse.aspx?aid=b40370fa97644873a0bba0c949a38069[/u
rl]
****************************************
*|||> some response please!
Geez, have patience. You did post well outside of business hours in the
western hemisphere...

ORDER BY decreases performance by 40x?

I am astounded. I haven't read any where that adding a sort order to a query
would drastically increase read time for the same query.
This query performed on a table with 360,000 records:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
ORDER BY [SEARCHKEY] DESC
Takes 40 seconds! While:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
Takes 1 second. Wow.
For now we will query the server without the ORDER BY and do a sort in the
Client application.
It seems like adding an ORDER BY on a large table or view increases the read
time by an order of magnitude.
Removing DESC speeds up the query somewhat.
Any thoughts? Thanks in advance...Hi John
Do you have an index on ID?
What happens if you ORDER by the table column, rather than the column alias,
which is an expression ?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
>|||"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
Take off the rtrim(), and is your ID column indexed in both directions? I
know that more ofther your index is ascending and we always want the max
value on top. So set your index to be descending instead.
HTH
__Stephen|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
Is there an index on SEARCHKEY? Have you looked at the execution plan to
make sure the index is being used?
The "TOP 1000 " causes the query to stop after 1000 rows are retrieved.
Using the ORDER BY forces the entire 360000 rows to be sorted before getting
the top 1000, This can be time consuming, especially when there is no index
to be used.
Your plan to to the sorting in the client has a drawback: you will have
different results doing it that way. Look at this set or data:
36
55
79
28
44
If you take the top 2 and then sort them in descending order, you get
55
36
If you sort them first and then take the top 2, you get
79
55
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
>
Oh, my bad, I did not notice you were sorting on the result of the
calculation. This can really slow things down as it prevents an index from
being used. See the difference if you take Kalen's advice and ORDER BY ID
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks for the responses guys...
All of them have merit. Bob, you hit the nail on the head when you reminded
me that the ORDER BY is performed on all 360,000 records before the TOP
1000 are selected. I feel like a real dope. Doing an ORDER BY on ID (which
is Indexed ascending) cut the time in half. I can see how Indexing
Descending will help even more.
What really fixed it for us is that we have an indexed INV_DATE field in the
view.
So I tried using
WHERE INV_DATE > DATEADD(DAY,-30,GETDATE()) and got it down to 2 seconds.
This gives me the last 30 days worth of invoices which will work in most
cases for a Browse. We supply other methods in the Client app to get to
specific records like exact Inv#, Date Range, etc in case the needed record
is not in the last 30 days.
Adding the WHERE clause immediately reduced the number of records that
needed to be sorted.
Once again, thank you all for your speedy replies.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:eDDoaKL4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> John Kotuby wrote:
> Oh, my bad, I did not notice you were sorting on the result of the
> calculation. This can really slow things down as it prevents an index from
> being used. See the difference if you take Kalen's advice and ORDER BY ID
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>

Order by clause

If I use the order by clause to sort on a date, where the date and
time stamp are the exact same for multiple records, how does SQL
output the data?
At random... or does it look at the primary key?"Chirag Patel" <romeo9225@.yahoo.com> wrote...
> If I use the order by clause to sort on a date, where the date and
> time stamp are the exact same for multiple records, how does SQL
> output the data?
> At random... or does it look at the primary key?

Generally speaking these behaviors are considered "undefined". As such you
cannot rely on it even if it "appears" to always do the same thing and if
you absolutely need the set ordered then you have to include it in the order
clause.

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?
Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>
|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
>

order by case

I'm hoping there's an easy solution to this. When ordering with a case
statement this works:
select * from blah
order by case when @.sort = 1 then customer_no
else lastname end
But if I want the case statement to sort on multiple columns:
select * from blah
order by case when @.sort = 1 then customer_no
else lastname, firstname, customer_no end -- problem
It doesn't like the lastname, firstname, customer_no part.
Thanks for any suggestions.SG,
Try (as example(:
DECLARE @.SORT TINYINT
SET @.SORT = 0
SELECT *
FROM AUTHORS
ORDER BY CASE WHEN @.SORT = 1 THEN AU_LNAME ELSE STATE END, CITY
HTH
Jerry
"sg" <sg@.noemail.com> wrote in message
news:uwdKHY4yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> I'm hoping there's an easy solution to this. When ordering with a case
> statement this works:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname end
> But if I want the case statement to sort on multiple columns:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname, firstname, customer_no end -- problem
> It doesn't like the lastname, firstname, customer_no part.
> Thanks for any suggestions.
>|||CASE is an expression and can only return one value. Using data type
precedence, all possible outcomes must either be or be convertible to a
common data type. You cannot use CASE for logic flow, e.g. to replace IF /
ELSE.
So, you need to break this out a little differently.
ORDER BY CASE @.sort
WHEN 1 THEN customer_no
ELSE lastname END,
CASE @.sort WHEN 1 THEN NULL ELSE firstname END
Though unless customer_no is not a key, this will probably work equally
well:
ORDER BY CASE @.sort
WHEN 1 THEN customer_no END,
lastname, firstname
If customer_no is sorted on first, then the rest of the order by clause
won't have any effect, because every customer_no is unique. If the first
ORDER BY is NULL, then the lastname, firstname sort order is obeyed.
In either case, adding customer_no to the end of the order by is only going
to come into play in the rare case that users have identical first/last
names, in which case, does it really matter which customer_no is displayed
first?
For some more information on this topic, see http://www.aspfaq.com/2501
"sg" <sg@.noemail.com> wrote in message
news:uwdKHY4yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> I'm hoping there's an easy solution to this. When ordering with a case
> statement this works:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname end
> But if I want the case statement to sort on multiple columns:
> select * from blah
> order by case when @.sort = 1 then customer_no
> else lastname, firstname, customer_no end -- problem
> It doesn't like the lastname, firstname, customer_no part.
> Thanks for any suggestions.
>sql

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > I am creating a stored procedure to return a sorted list
> >
> > Currently like this
> >
> > DECLARE @.SORT tinyint
> > SET @.SORT = 1 --could be anything passed in
> >
> > SELECT * FROM Table
> > ORDER BY
> >
> > CASE
> > WHEN 1 THEN Col1
> > WHEN 2 THEN Col2
> > ...etc
> >
> > Now i would like to add 'ASC' or 'DESC' like follows
> >
> > CASE
> > WHEN 1 THEN Col1 ASC
> > WHEN 2 THEN Col1 DESC
> > ...etc
> >
> > But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> >
> > IS there a way to make this work?
> >
> >
>

ORDER BY calculated field

Hello all,
I have a query i’d like to sort based on a field derived from other fields
from that same query.
I’d like to know if my field would then be calculated twice (Once in the
SELECT clause, another one in the ORDER BY clause). In this case it would
probably be better to use a subquery.
More concretly here are my 2 options :
Option 1 :
SELECT
data2.entity_nr,
data2.column_b,
data2.column_c,
data2.column_d,
data2.column_e,
data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
FROM
data2
ORDER BY
data2.column_b-data2.column_c-data2.column_d+data2.column_d DESC --column f
Option 2 :
SELECT * FROM (
SELECT
data2.entity_nr,
data2.column_b,
data2.column_c,
data2.column_d,
data2.column_e,
data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
FROM
data2
)derived
ORDER by derived.column_f DESC
Which one would you suggest ? (maybe a third one ;))
Tx
NicolasHi Nico
using derived tables will probably have less cost . But why dont you use
execution plan and see for yourself.
Regards
R.D
"Nico" wrote:

> Hello all,
> I have a query i’d like to sort based on a field derived from other fiel
ds
> from that same query.
> I’d like to know if my field would then be calculated twice (Once in the
> SELECT clause, another one in the ORDER BY clause). In this case it would
> probably be better to use a subquery.
> More concretly here are my 2 options :
> Option 1 :
> SELECT
> data2.entity_nr,
> data2.column_b,
> data2.column_c,
> data2.column_d,
> data2.column_e,
> data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
> FROM
> data2
> ORDER BY
> data2.column_b-data2.column_c-data2.column_d+data2.column_d DESC --colu
mn f
> Option 2 :
> SELECT * FROM (
> SELECT
> data2.entity_nr,
> data2.column_b,
> data2.column_c,
> data2.column_d,
> data2.column_e,
> data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
> FROM
> data2
> )derived
> ORDER by derived.column_f DESC
> Which one would you suggest ? (maybe a third one ;))
> Tx
> Nicolas
>|||>I'd like to know if my field would then be calculated twice
Also note that you can use an alias in the order by. So there is no need to
repeat the expression.
SELECT
data2.entity_nr,
data2.column_b,
data2.column_c,
data2.column_d,
data2.column_e,
data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
FROM
data2
ORDER BY column_f DESC
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Nico" <Nico@.discussions.microsoft.com> wrote in message
news:248BE97E-1C68-48FA-B5DE-B02B089B700D@.microsoft.com...
> Hello all,
> I have a query i'd like to sort based on a field derived from other fields
> from that same query.
> I'd like to know if my field would then be calculated twice (Once in the
> SELECT clause, another one in the ORDER BY clause). In this case it would
> probably be better to use a subquery.
> More concretly here are my 2 options :
> Option 1 :
> SELECT
> data2.entity_nr,
> data2.column_b,
> data2.column_c,
> data2.column_d,
> data2.column_e,
> data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
> FROM
> data2
> ORDER BY
> data2.column_b-data2.column_c-data2.column_d+data2.column_d
> DESC --column f
> Option 2 :
> SELECT * FROM (
> SELECT
> data2.entity_nr,
> data2.column_b,
> data2.column_c,
> data2.column_d,
> data2.column_e,
> data2.column_b-data2.column_c-data2.column_d+data2.column_d AS column_f,
> FROM
> data2
> )derived
> ORDER by derived.column_f DESC
> Which one would you suggest ? (maybe a third one ;))
> Tx
> Nicolas
>

ORDER BY before UNION syntax error

I have a list of Nationalities which I want to sort alphabetically except
for the value of Nationality which is "not disclosed" which I would like to
put at the top of the list.
I'm trying to do this with the following query but there seems to be a
problem with putting the ORDER BY in front of the UNION keyword
:
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
WHERE (Nationality <>'not disclosed')
ORDER BY Nationality
UNION
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
WHERE (Nationality = 'not disclosed')
I tried using brackets around the first part of the query but that didn't
work.
Any help much appreciated.
PeteHi
You can add an extra columns (examples are not tested!)
SELECT TOP 100 PERCENT NationalityID, Nationality, 0 as OrderBy
FROM dbo.Nationality
WHERE Nationality <>'not disclosed'
UNION
SELECT TOP 100 PERCENT NationalityID, Nationality, 1
FROM dbo.Nationality
WHERE Nationality = 'not disclosed'
ORDER BY OrderBy, Nationality
Although you do not need a UNION in this example:
SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality = 'not
disclosed' THEN 1 ELSE 0 END as OrderBy
FROM dbo.Nationality
ORDER BY OrderBy, Nationality
If you want to remove this from the result set you can use a derived table.
SELECT NationalityID, Nationality
FROM
( SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality =
'not disclosed' THEN 1 ELSE 0 END as OrderBy
FROM dbo.Nationality ) A
ORDER BY OrderBy, Nationality
John
"Italian Pete" wrote:

> I have a list of Nationalities which I want to sort alphabetically except
> for the value of Nationality which is "not disclosed" which I would like t
o
> put at the top of the list.
> I'm trying to do this with the following query but there seems to be a
> problem with putting the ORDER BY in front of the UNION keyword
> :
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> WHERE (Nationality <>'not disclosed')
> ORDER BY Nationality
> UNION
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> WHERE (Nationality = 'not disclosed')
> I tried using brackets around the first part of the query but that didn't
> work.
> Any help much appreciated.
> Pete|||Perfect!! Works a treat.
Thanks John
"John Bell" wrote:
> Hi
> You can add an extra columns (examples are not tested!)
> SELECT TOP 100 PERCENT NationalityID, Nationality, 0 as OrderBy
> FROM dbo.Nationality
> WHERE Nationality <>'not disclosed'
> UNION
> SELECT TOP 100 PERCENT NationalityID, Nationality, 1
> FROM dbo.Nationality
> WHERE Nationality = 'not disclosed'
> ORDER BY OrderBy, Nationality
> Although you do not need a UNION in this example:
> SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality = 'n
ot
> disclosed' THEN 1 ELSE 0 END as OrderBy
> FROM dbo.Nationality
> ORDER BY OrderBy, Nationality
> If you want to remove this from the result set you can use a derived table
.
> SELECT NationalityID, Nationality
> FROM
> ( SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality =
> 'not disclosed' THEN 1 ELSE 0 END as OrderBy
> FROM dbo.Nationality ) A
> ORDER BY OrderBy, Nationality
> John
> "Italian Pete" wrote:
>|||Italian wrote on Fri, 27 May 2005 04:59:53 -0700:
> Perfect!! Works a treat.
> Thanks John
> "John Bell" wrote:
>
Rather than using a UNION, you can do this in a single SELECT which should
be more efficient:
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
ORDER BY CASE WHEN Nationality = 'not disclosed' THEN 0 ELSE 1 END
Dan|||Daniel wrote to Italian Pete on Fri, 27 May 2005 15:25:34 +0100:

> Italian wrote on Fri, 27 May 2005 04:59:53 -0700:
>
> Rather than using a UNION, you can do this in a single SELECT which should
> be more efficient:
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> ORDER BY CASE WHEN Nationality = 'not disclosed' THEN 0 ELSE 1 END
>
Just noticed that's almost the same as Italian Pete posted. However, this
gives you just the 2 columns you wanted and doesn't require a derived table.
Dan|||Daniel wrote to Daniel Crichton on Fri, 27 May 2005 15:39:22 +0100:

> Just noticed that's almost the same as Italian Pete posted. However, this
> gives you just the 2 columns you wanted and doesn't require a derived
> table.
You know what, I need more caffeine and sleep. I meant John Bell.
:\
Dan

ORDER BY [Date] isnt working since its not a DateTime (its a varchar!)

Hi everyone.

I know, I know, it should have been a datetime from the start...but here's the problem.

I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:

ORDER BY [Date]

Are there any work arounds for this? Is there some way of doing:

ORDER BY covert(datetime, [Date], 103) or something?

Cheers
AndrewSELECT Convert(datetime, [Date], 103), Columnnames FROM TABLENAME
ORDER BY 1|||Thanks heaps, I'll give it a go!

Andrew|||Worked perfectly, though I chosed to:

SELECT convert(datetime, [Date], 103) AS 'Converted'
WHERE ...
ORDER BY Converted

just for looks :)

Thanks again!