Oracle DBA to learn MS SQL: column

Showing posts with label column. Show all posts

Friday, March 30, 2012

order of columns in composite index

I'm know that for composite indexes, the recommendation is always to specify
the most selective column first, but can someone please explain why this
makes such a huge difference in perfomance' I have a table where column A
only has 1 unique value and column B is basically unique among all rows. A
query that has A and B in the WHERE clause takes ALOT longer if my composite
index was created with (A, B) instead of (B, A). Thanks for any help.
BobBy the way, I did verify that in both cases, my index is being used, since I
know that index statistics are gathered based on the first column.
"Bob Gabor" <rjg@.mindspring.com> wrote in message
news:Yclkf.8556$N45.2454@.newsread1.news.atl.earthlink.net...
> I'm know that for composite indexes, the recommendation is always to
> specify the most selective column first, but can someone please explain
> why this makes such a huge difference in perfomance' I have a table
> where column A only has 1 unique value and column B is basically unique
> among all rows. A query that has A and B in the WHERE clause takes ALOT
> longer if my composite index was created with (A, B) instead of (B, A).
> Thanks for any help.
> Bob
>|||It depends on the query. Rules like the one you quote are just
general guidelines. For example, if one of the columns is used
in a range or LIKE comparison, it may be best to index that column
first regardless of selectivity. Depending on the query and data,
the two-column statistics may be more or less accurate predictors
of row count for the index in one order than in the other, also.
If you look at the query plans in more detail, you may be able
to see whether the faster solution is resulting in a better plan that
the other ordering can't allow, or if the faster solution is a result
of better row count estimates.
Steve Kass
Drew University
Bob Gabor wrote:

>I'm know that for composite indexes, the recommendation is always to specif
y
>the most selective column first, but can someone please explain why this
>makes such a huge difference in perfomance' I have a table where column A
>only has 1 unique value and column B is basically unique among all rows. A
>query that has A and B in the WHERE clause takes ALOT longer if my composit
e
>index was created with (A, B) instead of (B, A). Thanks for any help.
>Bob
>
>|||>> is always to specify
the most selective column first, but can someone please explain why
this
makes such a huge difference in perfomance' <<
there is only one hard and fast rule in our trade:
there are no hard and fast rules in database programming.
;)
For instance, if you frequently join on some column, putting it first
frequently speeds up joins.|||It does depend on many things, but I can give you some insight into why this
might be a problem (though I can not say it is necessarily a problem for
your application).
If you have a 2-column index with a non-selective leading column and a very
selective secondary column, it can cause additional I/O when compared to a
query run over an index with the columns defined in the opposite order
(selective column first). If the query does a s

on the first column and
then later columns, this could be less efficient.
SELECT col1, col2 FROM Table WHERE col1=4 and col3 > 5;
I will point out that it can vary from database engine to database engine.
It can vary on the predicates being used. It can vary based on the mix of
queries and the hardware. In short, it really does depend. However, it is
generally good to index selective fields since the cost of searching and the
cost of maintaining these indexes in updates is less than non-selective
columns.
Another reason to potentially pick a more selective leading index column,
all other factors being equal, is that SQL Server builds histograms on the
leading column. If it is very unselective, this can make the process of
cardinality estimation more difficult for the optimizer. This could cause
errors that lead to less than optimal plans being picked in some cases.
I hope that this gives you some insights into the internals to understand
why it might matter.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1133653242.800468.130470@.g47g2000cwa.googlegroups.com...
> the most selective column first, but can someone please explain why
> this
> makes such a huge difference in perfomance' <<
> there is only one hard and fast rule in our trade:
> there are no hard and fast rules in database programming.
> ;)
> For instance, if you frequently join on some column, putting it first
> frequently speeds up joins.
>

ORDER BY with alias

Hi NG,
in my following query I get the error "Invalid column name 'price'" but
everything seems ok:
SELECT row_number() over (order by price) as row_num,
dbo.getProductPrice('1234') as price
MS describes the use of aliases in ORDER BY
http://msdn2.microsoft.com/ms188385.aspx
Any ideas?
Thanks
Andre ScheiberleAndre Scheiberle wrote:
> Hi NG,
> in my following query I get the error "Invalid column name 'price'" but
> everything seems ok:
> SELECT row_number() over (order by price) as row_num,
> dbo.getProductPrice('1234') as price
> MS describes the use of aliases in ORDER BY
> http://msdn2.microsoft.com/ms188385.aspx
> Any ideas?
> Thanks
> Andre Scheiberle
ORDER BY in a query is different to ORDER BY in a ranking function. In
the latter case you can only reference base columns, not aliases. Try
the following (I assume you omitted the FROM clause in error).
SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price
FROM
(SELECT dbo.getProductPrice('1234')
FROM tbl) AS T(price) ;
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
--|||does dbo.getProductPrice('1234')
return a table or a scalar value?
--
"Andre Scheiberle" wrote:

> Hi NG,
> in my following query I get the error "Invalid column name 'price'" but
> everything seems ok:
> SELECT row_number() over (order by price) as row_num,
> dbo.getProductPrice('1234') as price
> MS describes the use of aliases in ORDER BY
> http://msdn2.microsoft.com/ms188385.aspx
> Any ideas?
> Thanks
> Andre Scheiberle
>|||ok, in this example it works, but in my Application I use this Query:
SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price, article
FROM
products,
(SELECT dbo.getProductPrice(products.article)M tbl) AS T(price) ;
Now an error appears "The multi-part identifier "products.article" could not
be bound."
Thanks
Andre
"David Portas" wrote:

> Andre Scheiberle wrote:
> ORDER BY in a query is different to ORDER BY in a ranking function. In
> the latter case you can only reference base columns, not aliases. Try
> the following (I assume you omitted the FROM clause in error).
> SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price
> FROM
> (SELECT dbo.getProductPrice('1234')
> FROM tbl) AS T(price) ;
> --
> 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
> --
>|||The table return a scalar value
"Omnibuzz" wrote:
> does dbo.getProductPrice('1234')
> return a table or a scalar value?
> --
>
>
> "Andre Scheiberle" wrote:
>|||try this then.
SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price, article
from
(SELECT dbo.getProductPrice(article), article
FROM
products) AS T(price, article)
"Omnibuzz" wrote:
> does dbo.getProductPrice('1234')
> return a table or a scalar value?
> --
>
>
> "Andre Scheiberle" wrote:
>|||Andre Scheiberle wrote:
> ok, in this example it works, but in my Application I use this Query:
> SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num, price, article
> FROM
> products,
> (SELECT dbo.getProductPrice(products.article)M tbl) AS T(price) ;
> Now an error appears "The multi-part identifier "products.article" could n
ot
> be bound."
> Thanks
> Andre
>
You can't reference a table from the outer query in a derived table
subquery. Try this (assuming your function is scalar):
SELECT ROW_NUMBER() OVER (ORDER BY price) AS row_num,
price, article
FROM
(SELECT dbo.getProductPrice(article), article
FROM products) AS T(price,article) ;
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
--|||Here is how a SELECT works in SQL ... at least in theory. Real
products will optimize things, but the code has to produce the same
results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The <table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items. The original table no longer exists.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The AS
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause have been executed; you cannot use
them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a

mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.

ORDER BY using @variables

Hi
I realise you can't declare a column name as a @.variable:
SELECT name, address FROM table WHERE name = 'smith' ORDER BY @.column
....but other than using dynamic SQL, is there a better way i can ORDER a query using various columns?
thankssomething like this perhaps?order
by case @.flag
when 1 then column1
when 2 then column2
when 3 then column3
else null end|||Ordering logic belongs at the presentation layer, not the database layer.|||WHAT??!!!!

please explain, oh database guru

you're suggesting that we ditch the ORDER BY clause altogether?

something about that idea just doesn't sit too well with me...|||I'm gonna report this thread|||Thanks for that r937, works spot on!

Just have another question, for some reason i get the following error when trying to add the case statement to a UNION query:

ORDER BY items must appear in the select list if the statement contains a UNION operator.

Now i know that you can define a single ORDER BY clause in a union query and that it should appear after the last SELECT which it is. I've tried manually putting the ORDER BY clause into the query and it works fine, but it just doesnt like the case statement for some reason? Is there a way round it?

cheers|||Just note two conditions on performing UNIONS :-

The number and the order of the columns must be the same in all queries.
The data types must be compatible.|||Is there a way round it?there might be, but i can't really help because i can't see the query from here|||sorry, should have put it in - this is a simlified version of my MSSQL query - it still brings up the same error:

SELECT business_name, address1
FROM VENUE
WHERE (business_name LIKE '%' + @.v_name + '%')
UNION
SELECT business_name, address1
FROM AHOTELS
WHERE business_name Like '%' + @.v_name + '%'
ORDER BY CASE @.sortBy
WHEN 1 THEN business_name
WHEN 2 THEN postcode
WHEN 3 THEN town
ELSE NULL END|||WHAT??!!!!

please explain, oh database guru
Ordering is normally a presentation issue. Databases should not be concerned with the manner in which data is presented.
you're suggesting that we ditch the ORDER BY clause altogether?
Of course not. Would you suggest that we ditch cursors just because they are often misused?|||it still brings up the same error:which is... ?|||Ordering is normally a presentation issue. Databases should not be concerned with the manner in which data is presented.next time i see you post a SELECT statement with an ORDER BY clause, you are gonna get hit with a big can of whoopass from me, then|||This is the error:

ORDER BY items must appear in the select list if the statement contains a UNION operator.|||well, that's pretty clear, isn't it ;)

SELECT business_name, address1, postcode, town
FROM VENUE
WHERE (business_name LIKE '%' + @.v_name + '%')
UNION
SELECT business_name, address1, postcode, town
FROM AHOTELS
WHERE business_name Like '%' + @.v_name + '%'
ORDER BY CASE @.sortBy
WHEN 1 THEN business_name
WHEN 2 THEN postcode
WHEN 3 THEN town
ELSE NULL END|||but I'm sure they don't want it in the result set|||but I'm sure they don't want it in the result set
so what's your suggestion in that case?

are you going to join the blindman parade and suggest that ordering should be done in the front end application?

if you don't include postcode and town in the result set, and then pass that result set to the front end app, then how are you gonna sort by postcode or town??

come on, brett, you're a smart guy, i'd like to see your solution|||hi, sorry, the code i pasted was a shortened version and the original did include postcode and town (apologies, will not shorten code in the future!)

I'm using the column names in the first SELECT, is this right?

Also, as i've mentioned, when i use a single ORDER BY, eg:

...
ORDER BY business_name

this works, but if i try:

...
ORDER BY CASE @.sortBy
WHEN 1 THEN business_name
ELSE NULL END

This surely should do the same thing (assuming @.sortBy is 1) but does not work and comes up with that error!! (btw i'm using MS SQL server managemnt studio, and the error comes up when i try to execute(save) the stored proc!)|||Nobody open a can of whoopass on me please :)

ORDER BY CASE
WHEN @.sortBy = 1 THEN business_name
ELSE NULL END

EDIT - misread the SQL - sorry no real change made|||When heating a can of whoopass, should I mix it with 1 can of water or 1 can of milk?

Also, will one can of whoopas be enough to feed everybody in my parade? Or will you open up a family-size can of whoopass for me?

You won't find ORDER BY in my select statements unless specifically requested by the developers and then it would be against my advice. But anyway, I consider the ordering of data in result sets to be at most a minor transgression.|||Here is the essence of the problem, then?

create table test1
(col1 int, col2 varchar(30))

create table test2
(col3 int, col4 varchar(30))

insert into test1 values (1, 'hello')
insert into test1 values (3, 'aloha')
insert into test2 values (2, 'bye')
insert into test2 values (4, 'auf wiedersehen')

select col1, col2 from test1
union
select col3, col4 from test2
order by col1

select col1, col2 from test1
union
select col3, col4 from test2
order by case when 1 = 1 then col1
when 1 = 2 then col2 end -- OK. Dummy cases, but it generates an error.

drop table test1
drop table test2

Now, that is a conundrum. This will take some thought...|||just found a blog here (http://www.sqlblogs.com/top/ng/group~22/~117449~__order-by-and-UNION/index.aspx)which refers to a ANSI SQL-92 standard (??) that you can't use Expressions in an ORDER clause when used with a UNION!! Does this sound right??

If so, are there any other methods which i can try which do the same as a UNION?

ta|||yup MCrowley, that is the problem in essence!!|||This is ugly, but it gets the job done. Performance-wise it is probably the same thing:

select * from
(select col1, col2 from test1
union
select col3 , col4 from test2) a
order by case when 1 = 1 then col1
when 1 = 2 then col2 end

EDIT: Removed confusing extra characters.|||New problem:

select * from
(select col1, col2 from test1
union
select col3 , col4 from test2) a
order by case when 1 = 2 then col1
when 1 = 1 then col2 end

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'hello' to a column of data type int.

Are all of your datatypes in the order by the same, or at least similar datatypes?|||yup, they are all varchar apart from @.sortBy which is an int. Like i said before, the UNION works fine on its own and with a single ORDER BY clause, its only when the case statement gets thrown into it that is has an error!|||Then you should be all set with the solution in post 23. the problem only comes up when you have an int and a varchar as a result of the case statement.|||Ok thanks MCrowley, will try it!

Wednesday, March 28, 2012

ORDER BY question: splitting string into 2 orders?

I have a column named "LIST" in a table with strings like the following:

151231-1002-02-1001
151231-1001-02-1001
151231-1002-02-1002
151231-1003-02-1001
etc...

What I'd like to do is include an ORDER BY statement that splits the
string, so that the order would be by the second set of four numbers
(i.e. between the first and second - marks), followed by the third set
of two numbers, and then by the last set of four numbers.

How would I do something like this?

--
Sugapablo - russpghREMOVE@.stargate.net
http://www.sugapablo.com | ICQ: 902845If this is a fixed width column with fixed formats, you can use substring to
parse the value like:

SELECT *
FROM tbl
ORDER BY SUBSTRING(col, 8, 4),
SUBSTRING(col, 13, 2),
RIGHT(col, 4) ;

If these are variable length formatted, then you have more work to do:

SELECT *
FROM tbl
ORDER BY SUBSTRING(col,
CHARINDEX('-', col) + 1,
CHARINDEX('-', col,
CHARINDEX('-', col) + 1) -
CHARINDEX('-', col) - 1),
REVERSE(SUBSTRING(REVERSE(col),
CHARINDEX('-', REVERSE(col)) + 1,
CHARINDEX('-', REVERSE(col),
CHARINDEX('-', REVERSE(col)) + 1) -
CHARINDEX('-', REVERSE(col)) - 1)),
REVERSE(SUBSTRING(REVERSE(col), 1,
CHARINDEX('-', REVERSE(col)) - 1)) ;

Another trick is to use PARSENAME function. Note that the return expression
for PARSENAME function is unicode though. See SQL Server Books Online for
more details.

SELECT *
FROM tbl
ORDER BY PARSENAME(REPLACE(col, '-', '-'), 3),
PARSENAME(REPLACE(col, '-', '-'), 2),
PARSENAME(REPLACE(col, '-', '-'), 1) ;

If each of these portions are of business significance, why are you
representing them as a single column? If consolidation is needed for certain
specific requirements, you can use a view for such representation.

--
- Anith
( Please reply to newsgroups only )

Order by problem

Hi,
How can I order the column in the correct order if my numbers are in string
fields? I am now getting
1
11
10
etc...
Regards,you could always convert/CAST the field in to numerics and sort on this
column?
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I order the column in the correct order if my numbers are in
string
> fields? I am now getting
> 1
> 11
> 10
> etc...
> Regards,
>
>|||Hi
CREATE TABLE #Test
(
col VARCHAR(10)
)
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('11')
INSERT INTO #Test VALUES ('10')
SELECT * FROM #Test ORDER BY col ASC
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can I order the column in the correct order if my numbers are in
> string
> fields? I am now getting
> 1
> 11
> 10
> etc...
> Regards,
>
>|||I f you are sure that only numeric data is inserted you could cast this
as follow to an INT (or any other numeric type)
Create Table #temp
(
Col varchar(10)
)
INSERt INTO #Temp
Select '1'
INSERt INTO #Temp
Select '11'
INSERt INTO #Temp
Select '10'
Select * from #Temp order by col
Select * from #Temp order by CAST(col AS INT)
Drop table #temp
HTH, Jens Suessmeyer.|||I f you are sure that only numeric data is inserted you could cast this
as follow to an INT (or any other numeric type)
Create Table #temp
(
Col varchar(10)
)
INSERt INTO #Temp
Select '1'
INSERt INTO #Temp
Select '11'
INSERt INTO #Temp
Select '10'
Select * from #Temp order by col
Select * from #Temp order by CAST(col AS INT)
Drop table #temp
HTH, Jens Suessmeyer.|||Ricky
Yes , but what if he has a literal characters in the column as well. CAST
conversion will fail
"Ricky" <MSN.MSN.com> wrote in message
news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> you could always convert/CAST the field in to numerics and sort on this
> column?
> "EDom" <technical@.peoplewareindia.com> wrote in message
> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> How can I order the column in the correct order if my numbers are in
> string
>> fields? I am now getting
>> 1
>> 11
>> 10
>> etc...
>> Regards,
>>
>|||Hi,
I do have char attached to the numbers.
A1, A11, A10, A11B, A21C
etc
Regards,
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK7ROcpwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Ricky
> Yes , but what if he has a literal characters in the column as well. CAST
> conversion will fail
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> > you could always convert/CAST the field in to numerics and sort on this
> > column?
> >
> > "EDom" <technical@.peoplewareindia.com> wrote in message
> > news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How can I order the column in the correct order if my numbers are in
> > string
> >> fields? I am now getting
> >> 1
> >> 11
> >> 10
> >> etc...
> >>
> >> Regards,
> >>
> >>
> >>
> >
> >
>|||Good Point Uri!!!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eK7ROcpwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Ricky
> Yes , but what if he has a literal characters in the column as well. CAST
> conversion will fail
>
> "Ricky" <MSN.MSN.com> wrote in message
> news:uzx3lXpwFHA.2880@.TK2MSFTNGP10.phx.gbl...
> > you could always convert/CAST the field in to numerics and sort on this
> > column?
> >
> > "EDom" <technical@.peoplewareindia.com> wrote in message
> > news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> How can I order the column in the correct order if my numbers are in
> > string
> >> fields? I am now getting
> >> 1
> >> 11
> >> 10
> >> etc...
> >>
> >> Regards,
> >>
> >>
> >>
> >
> >
>|||Hi,
I dont get it correct
1A
11A
10A
this gives me the same result even I do sorting.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#YowWYpwFHA.664@.tk2msftngp13.phx.gbl...
> Hi
> CREATE TABLE #Test
> (
> col VARCHAR(10)
> )
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('11')
> INSERT INTO #Test VALUES ('10')
> SELECT * FROM #Test ORDER BY col ASC
>
> "EDom" <technical@.peoplewareindia.com> wrote in message
> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > How can I order the column in the correct order if my numbers are in
> > string
> > fields? I am now getting
> > 1
> > 11
> > 10
> > etc...
> >
> > Regards,
> >
> >
> >
>|||Hi
SELECT * FROM #Test ORDER BY right('0000'+col,4) ASC
"EDom" <technical@.peoplewareindia.com> wrote in message
news:eFapS40wFHA.2656@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I dont get it correct
> 1A
> 11A
> 10A
> this gives me the same result even I do sorting.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:#YowWYpwFHA.664@.tk2msftngp13.phx.gbl...
>> Hi
>> CREATE TABLE #Test
>> (
>> col VARCHAR(10)
>> )
>> INSERT INTO #Test VALUES ('1')
>> INSERT INTO #Test VALUES ('11')
>> INSERT INTO #Test VALUES ('10')
>> SELECT * FROM #Test ORDER BY col ASC
>>
>> "EDom" <technical@.peoplewareindia.com> wrote in message
>> news:eEWcaSpwFHA.720@.TK2MSFTNGP10.phx.gbl...
>> > Hi,
>> >
>> > How can I order the column in the correct order if my numbers are in
>> > string
>> > fields? I am now getting
>> > 1
>> > 11
>> > 10
>> > etc...
>> >
>> > Regards,
>> >
>> >
>> >
>>
>

Order By not working as thought

I have a table that has a two column clustered index. Col 1 is int and
col 2 is char, defined as desc.
When I run a query selecting only those two columns and use an Order By
Col1 ASC, the result set returned has the Col 1 returned in DESC. And
when I use an Order By Col 1 DESC, the result set returned has the Col1
returned in ASC.
The plan shows only a clustered index seek. So even though my
clustered index is defined as desc, why would that affect the way the
Order By clause chooses to return the results? ASC in the Order By
should still return the rows from 1 to 100 not from 100 to 1 even
though the clustered index is desc.
Am I wrong in my thinking...'
Thanks!!Are you saying that SQL Server reverses the meaning of ASN and DESC on your ORDER BY when have an
index defined as DESC instead of ASC. If so, it is a bug and should be reported to MS (and see first
if such a bug has been reported, test recent service pack etc). Post a repro script if you want us
to rest or recent build or 2005.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mghale" <martinghale@.yahoo.com> wrote in message
news:1131465273.531584.225150@.o13g2000cwo.googlegroups.com...
>I have a table that has a two column clustered index. Col 1 is int and
> col 2 is char, defined as desc.
> When I run a query selecting only those two columns and use an Order By
> Col1 ASC, the result set returned has the Col 1 returned in DESC. And
> when I use an Order By Col 1 DESC, the result set returned has the Col1
> returned in ASC.
> The plan shows only a clustered index seek. So even though my
> clustered index is defined as desc, why would that affect the way the
> Order By clause chooses to return the results? ASC in the Order By
> should still return the rows from 1 to 100 not from 100 to 1 even
> though the clustered index is desc.
> Am I wrong in my thinking...'
> Thanks!!
>|||That's exactly what I found. Actually it's a client of mine. They
came to me asking if this is the way it should be returning the
results. The funny thing is that if you add a column to the select
that is not included in the clustered index definition, the execution
plan shows a SORT and the result set is returned in the correct oder as
specified by the Order By clause.
When the select list only contained the two columns that make up the
clustered index, the execuation plan showed NO sort being performed and
returned the results backwards as specified by the Order By clause.
Thanks for the reply. I will advise my client.
Martin|||I you sure this query isn't a view? Views are supposed to be unordered
and an ORDER BY in a view will not exhibit the same (unsupported)
behaviour in 2005 as in 2000.
--
David Portas
SQL Server MVP
--|||Good point, David!
Martin, if you can produce a repro script, we are happy to check it out for you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1131476712.154075.133400@.g43g2000cwa.googlegroups.com...
>I you sure this query isn't a view? Views are supposed to be unordered
> and an ORDER BY in a view will not exhibit the same (unsupported)
> behaviour in 2005 as in 2000.
> --
> David Portas
> SQL Server MVP
> --
>|||David,
SQL 2000 support ORDER BY in query view using the TOP statement:
SELECT TOP 100 percent FROM table
ORDER BY column ASC | DESC
--
** * Esta msg foi Ãºtil pra vocÃª ? EntÃ£o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"David Portas" wrote:
> I you sure this query isn't a view? Views are supposed to be unordered
> and an ORDER BY in a view will not exhibit the same (unsupported)
> behaviour in 2005 as in 2000.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks guys.
This isn't a view but a base table.
I'll see if I can get the client to allow me to post the DDL for the
table.
Essentially it is a table with x columns. Column one and two are Age
(INT) and Suffix(Char). The rest of the columns vary in type. The
table has a DESC clusterd index on Age, Suffix.
The select statement that produces the 'strange' results selects Age,
Suffice from tablename where Age in value, value, value, and Suffix not
in value, value Order By Age ASC
Then they execute this statement the ordering of Age (expected to be
from lowest to highest) is actually reversed and is listed from highest
to lowest. When you change the ASC to DESC in the Order By clause you
get just the opposite results. The order returned is the opposite to
what you expect to be returned according to the Oder By clause sort
order.
Also when we add a column to the select list that is not part of the
clustered index key and also add the column to the Order By clause
(i.e. Order By Age, ColNotInIndex) the execution plan shows a SORT
phase and the results are returned correctly as specified by the Order
By clause, either DESC or ASC.
Very strange and not what I expected although I have been researching
the forums and it looks like others have run into similar issues when
using DESC Clusterd Indexes...|||Yes, but that doesn't guarantee that you get the data back in that order when you query the view
(unless that query has a similar ORDER BY, of course). Using TOP 100 PERCENT in a view definition
has always been considered a hack, and in 2005 we will typically not see this have any impact of the
ordering of the rows returned from the view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rodrigo Fernandes" <RodrigoFernandes@.discussions.microsoft.com> wrote in message
news:B8B5EFE3-848F-4CE2-BA9E-5C89F2868AFB@.microsoft.com...
> David,
> SQL 2000 support ORDER BY in query view using the TOP statement:
> SELECT TOP 100 percent FROM table
> ORDER BY column ASC | DESC
> --
> ** * Esta msg foi Ãºtil pra vocÃª ? EntÃ£o marque-a como tal. ***
> Regards,
> Rodrigo Fernandes
>
> "David Portas" wrote:
>> I you sure this query isn't a view? Views are supposed to be unordered
>> and an ORDER BY in a view will not exhibit the same (unsupported)
>> behaviour in 2005 as in 2000.
>> --
>> David Portas
>> SQL Server MVP
>> --
>>|||I agree that using Top 100 percent is a hack but it also commonly used
so far as I have seen at my clients.
But still in my case were are hitting a base table and not a view.
Just a single base table. The fact is that using the Order By clause
should cause a SORT to be performed prior to the result set being
returned to ensure the data is in fact in the order specified by the
Order By clause. For whatever reason, on this table with a DESC
Clustered Index, the SORT is not being performed if the only columns in
the Order By Clause are key columns in the DESC Clustered Index. I
know SQL Server is not just ignoring the Order By clause because
depending on the sort order in the clause, either ASC or DESC it is
returning the rows in the exact opposite order. It's like it is using
the sorted (clustered) data and thinking that it is already ordered
correctly then applying the ASC or DESC directly to the order of the
clustered index which in this case in DESC. Perhaps that is why it
produces backwards results when using the Order By clause with ASC. It
doesn't sort but simply leaves the oder as it is in the ordered
(clustered) index which returns the results in a DESC order. Then when
you specify DESC in the order by clause SQL Server just reverses the
order of teh clusterd index which returns the rows in an ASC order.
Definitely not the right behavior for the Order By clause but it is my
best guess as to why this is happening. This is not a complicated
table structure and a very simple SQL Statement. Not alot of room for
human error on this one...|||SQL Server doesn't need a SORT as it can use the index to traverse the rows in the correct (!)
order. Look at the execution plan for the index usage and you will see ORDERED FORWARD or BACKWARD.
This is letting the execution engine that it must follow the index linked list to retrieve the rows,
not tie IAM page. But in this case, SQL Server obviously does it wrong. Again, with a repro we can
try it and verify etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mghale" <martinghale@.yahoo.com> wrote in message
news:1131481374.194917.233520@.z14g2000cwz.googlegroups.com...
>I agree that using Top 100 percent is a hack but it also commonly used
> so far as I have seen at my clients.
> But still in my case were are hitting a base table and not a view.
> Just a single base table. The fact is that using the Order By clause
> should cause a SORT to be performed prior to the result set being
> returned to ensure the data is in fact in the order specified by the
> Order By clause. For whatever reason, on this table with a DESC
> Clustered Index, the SORT is not being performed if the only columns in
> the Order By Clause are key columns in the DESC Clustered Index. I
> know SQL Server is not just ignoring the Order By clause because
> depending on the sort order in the clause, either ASC or DESC it is
> returning the rows in the exact opposite order. It's like it is using
> the sorted (clustered) data and thinking that it is already ordered
> correctly then applying the ASC or DESC directly to the order of the
> clustered index which in this case in DESC. Perhaps that is why it
> produces backwards results when using the Order By clause with ASC. It
> doesn't sort but simply leaves the oder as it is in the ordered
> (clustered) index which returns the results in a DESC order. Then when
> you specify DESC in the order by clause SQL Server just reverses the
> order of teh clusterd index which returns the rows in an ASC order.
> Definitely not the right behavior for the Order By clause but it is my
> best guess as to why this is happening. This is not a complicated
> table structure and a very simple SQL Statement. Not alot of room for
> human error on this one...
>|||Thanks for the clarification on the SORT. I will check the execution
plan. I guess I assumed that any time you include an Order By clause
in your query, the DBMS would perform a SORT as the final step to order
the rows according to the Order By clause.
I'll work with the client today to get the DDL for the table and
indexes, the exact SQL statement, the the levels of the OS and SQL
Server.
I won't be able to provide a sampling of data as my client is an
insurance company and restricted by HIPPA.
Thanks for all the feedback.
Martin|||OK,
I didn't get the client to provide their actual table DDL but we have
created a test script that reproduces the same outcome on three
different servers and found something very interesting. When using
just the first column of the clustered index which is an INT in the
WHERE clause, the Oder By clause is applied correctly. When we also
include the second column of the clustered index which is a char in the
WHERE clause, the Order By clause is applied incorrectly, the reverse
of what it should ordered according to the Order By clause. Here's the
info to reproduce the issue...
1. First create table and populate with data and do not add the
clusterd index yet...
---
-- Create Table
---
create table table_one
(
column_one int not null ,
column_two char(1) collate sql_latin1_general_cp1_ci_as not null
)
go
---
-- Insert Test Data
---
insert table_one values (1,'a')
insert table_one values (2,'b')
insert table_one values (3,'c')
insert table_one values (3,'k')
insert table_one values (3,'l')
insert table_one values (3,'x')
insert table_one values (4,'d')
insert table_one values (5,'e')
insert table_one values (6,'f')
insert table_one values (6,'m')
insert table_one values (6,'n')
insert table_one values (6,'x')
insert table_one values (7,'g')
insert table_one values (7,'s')
insert table_one values (8,'h')
insert table_one values (8,'q')
insert table_one values (9,'i')
insert table_one values (9,'o')
insert table_one values (9,'p')
insert table_one values (9,'x')
insert table_one values (10,'j')
insert table_one values (10,'p')
go
2. Run the following SELECT statements and make not of the correct
oder returned by each query. The comments to the side specify what
order the result set is returned and whether a SORT operation was part
of the execution plan. All these statements return the data and
perform a SORT as expected as no clustered index exists yet.
---
-- No ORDER BY Clause
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- No SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
-- order by column_one
go
---
-- ORDER BY Clause on Column_One
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
order by column_one
go
---
-- ORDER BY Clause on Column_One DESC
---
SELECT column_one, column_two -- Descending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
order by column_one desc
go
---
-- ORDER BY Clause on Column_One ASC
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
and (column_two not IN ('x'))
order by column_one asc
go
---
-- No AND Clause
-- No ORDER BY Clause
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- No SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
-- order by column_one
go
---
-- No AND Clause
-- ORDER BY Clause on Column_One
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
order by column_one
go
---
-- No AND Clause
-- ORDER BY Clause on Column_One DESC
---
SELECT column_one, column_two -- Descending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
order by column_one desc
go
---
-- No AND Clause
-- ORDER BY Clause on Column_One ASC
---
SELECT column_one, column_two -- Ascending order returned
FROM table_one -- SORT in Execution Plan
WHERE (column_one IN ('3','6','9'))
-- and (column_two not IN ('x'))
order by column_one asc
go
3. Create the Clustered Index with the following...
---
-- Create Clustered Index
---
create clustered index cluster_001
on table_one
(column_one desc, column_two)
go
4. Re-execute the above SELECT statements. Before sure to compare the
order of the result set returned with the order specified in the
comment. You will see on a few of the statements,l namely the SELECTS
that include both columns in the WHERE clause, the oder is returned in
the reverse as what it should be returned...
We have verified this behavior on three different servers and a
workstation. We are on Windows 2003 Server and SQL Server 2000 SP3.
We have not seen any mention of a fix for this in SP4 of SQL 2005
although we are going to execute this test on both of those
environments today as well as open an issue with Microsoft Support.
Have a look, see for your self. Very interesting results...
Thanks for all the replies!
Martin|||Definitely looks like a bug to me.
After creating the clustered index many of the resultsets are in an
incorrect order. I have run the script on SQL2K SP4 (8.00.2039).
The weird part is, that it even goes wrong if the first column is not
indexed as descending, as long as the second column is indexed as
descending. IOW, it also goes wrong with the index definition:
create clustered index cluster_001
on table_one
(column_one, column_two desc)
BTW: a work around is to change the column name in the ORDER BY clause
to a non trivial expression, for example ORDER BY RTRIM(column_one)
Gert-Jan
mghale wrote:
> OK,
> I didn't get the client to provide their actual table DDL but we have
> created a test script that reproduces the same outcome on three
> different servers and found something very interesting. When using
> just the first column of the clustered index which is an INT in the
> WHERE clause, the Oder By clause is applied correctly. When we also
> include the second column of the clustered index which is a char in the
> WHERE clause, the Order By clause is applied incorrectly, the reverse
> of what it should ordered according to the Order By clause. Here's the
> info to reproduce the issue...
> 1. First create table and populate with data and do not add the
> clusterd index yet...
> ---
> -- Create Table
> ---
> create table table_one
> (
> column_one int not null ,
> column_two char(1) collate sql_latin1_general_cp1_ci_as not null
> )
> go
> ---
> -- Insert Test Data
> ---
> insert table_one values (1,'a')
> insert table_one values (2,'b')
> insert table_one values (3,'c')
> insert table_one values (3,'k')
> insert table_one values (3,'l')
> insert table_one values (3,'x')
> insert table_one values (4,'d')
> insert table_one values (5,'e')
> insert table_one values (6,'f')
> insert table_one values (6,'m')
> insert table_one values (6,'n')
> insert table_one values (6,'x')
> insert table_one values (7,'g')
> insert table_one values (7,'s')
> insert table_one values (8,'h')
> insert table_one values (8,'q')
> insert table_one values (9,'i')
> insert table_one values (9,'o')
> insert table_one values (9,'p')
> insert table_one values (9,'x')
> insert table_one values (10,'j')
> insert table_one values (10,'p')
> go
> 2. Run the following SELECT statements and make not of the correct
> oder returned by each query. The comments to the side specify what
> order the result set is returned and whether a SORT operation was part
> of the execution plan. All these statements return the data and
> perform a SORT as expected as no clustered index exists yet.
> ---
> -- No ORDER BY Clause
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- No SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> -- order by column_one
> go
> ---
> -- ORDER BY Clause on Column_One
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> order by column_one
> go
> ---
> -- ORDER BY Clause on Column_One DESC
> ---
> SELECT column_one, column_two -- Descending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> order by column_one desc
> go
> ---
> -- ORDER BY Clause on Column_One ASC
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> and (column_two not IN ('x'))
> order by column_one asc
> go
> ---
> -- No AND Clause
> -- No ORDER BY Clause
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- No SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> -- order by column_one
> go
> ---
> -- No AND Clause
> -- ORDER BY Clause on Column_One
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> order by column_one
> go
> ---
> -- No AND Clause
> -- ORDER BY Clause on Column_One DESC
> ---
> SELECT column_one, column_two -- Descending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> order by column_one desc
> go
> ---
> -- No AND Clause
> -- ORDER BY Clause on Column_One ASC
> ---
> SELECT column_one, column_two -- Ascending order returned
> FROM table_one -- SORT in Execution Plan
> WHERE (column_one IN ('3','6','9'))
> -- and (column_two not IN ('x'))
> order by column_one asc
> go
> 3. Create the Clustered Index with the following...
> ---
> -- Create Clustered Index
> ---
> create clustered index cluster_001
> on table_one
> (column_one desc, column_two)
> go
> 4. Re-execute the above SELECT statements. Before sure to compare the
> order of the result set returned with the order specified in the
> comment. You will see on a few of the statements,l namely the SELECTS
> that include both columns in the WHERE clause, the oder is returned in
> the reverse as what it should be returned...
> We have verified this behavior on three different servers and a
> workstation. We are on Windows 2003 Server and SQL Server 2000 SP3.
> We have not seen any mention of a fix for this in SP4 of SQL 2005
> although we are going to execute this test on both of those
> environments today as well as open an issue with Microsoft Support.
> Have a look, see for your self. Very interesting results...
> Thanks for all the replies!
> Martin|||Thanks for the replies everyone. Gert-Jan - thanks for the work-around
suggestion.
My client is opening an issue with MS and we are also going to test for
the same behavior on SQL 2005...
Martinsql

Monday, March 26, 2012

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)

Friday, March 23, 2012

ORDER BY DESC

I want to select 3 columns so that the result set is sorted descending on
each column. But this doesn't seem to get me the results that I want:
SELECT col1, col2, col3
FROM tbl1
ORDER BY col1, col2, col3 DESC
And this does not work (syntax error):
...ORDER BY col1 DESC, col2 DESC, col3 DESC
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneMike,
This should work:
> ...ORDER BY col1 DESC, col2 DESC, col3 DESC
Can you show the actual query?
Andrew J. Kelly SQL MVP
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:urUl$OLBGHA.3164@.TK2MSFTNGP10.phx.gbl...
>I want to select 3 columns so that the result set is sorted descending on
>each column. But this doesn't seem to get me the results that I want:
> SELECT col1, col2, col3
> FROM tbl1
> ORDER BY col1, col2, col3 DESC
> And this does not work (syntax error):
> ...ORDER BY col1 DESC, col2 DESC, col3 DESC
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> This should work:
Whoops! Nevermind, you are correct. One of the commas in the order by
clause was acidentally a decimal.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

Order By date and time of insert

I just wanted to know is there any way to order the rows of table based on date and time of insert. I dont have date column in my table. I have to insert values into the table. Based on the system date and time of the values inserted into the table, can i order the rows??As you have posted a question in the articles section it is being moved to SQL Server Forum .

MODERAOTR.|||for that you need to add a new column to the table and store system date and time in that column for every new insert and finally sort the data by that column.

Order By computed columns

Hi all,
I have a long runing query took 70s and returns only 124 rows. I found the
problem is that it uses a compute column in the Order By clause. Something
like this
SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
If I took that Order By away, it only takes 2s. (That make me think my C#
client code can sort better than that :P )
Can anyone show me what are the ways I can do to optimize it?
Any thoughts are appreciated.
ConradConrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
If that expression is in the SELECT clause you can use
ORDER BY n
where n is the ordinal number of the expression in the SELECT clause.
E.g.:
SELECT col1, col2, (col4 * 0.25) / 100, ...
FROM ...
ORDER BY 3
Will sort the resultset by the value of the 3rd column in the SELECT
clause.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
BnAeboIAn+Ja2WD/GUp486uA
=1vFd
--END PGP SIGNATURE--|||If you can do it in the client side, then do it.
AMB
"Conrad Chan" wrote:

> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||I will say only if db really cannot do a better job.
Thanks
Conrad
"Alejandro Mesa" wrote:
> If you can do it in the client side, then do it.
>
> AMB
> "Conrad Chan" wrote:
>|||Unfortunately it doesn't make a difference :<
Conrad
"MGFoster" wrote:

> Conrad Chan wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> If that expression is in the SELECT clause you can use
> ORDER BY n
> where n is the ordinal number of the expression in the SELECT clause.
> E.g.:
> SELECT col1, col2, (col4 * 0.25) / 100, ...
> FROM ...
> ORDER BY 3
> Will sort the resultset by the value of the 3rd column in the SELECT
> clause.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
> BnAeboIAn+Ja2WD/GUp486uA
> =1vFd
> --END PGP SIGNATURE--
>|||You can simplify the ORDER BY clause to
ORDER BY COALESCE(Table1.Field1, ''), COALESCE(CAST(Table2.Field2 AS
nvarchar), '''')
or even to
ORDER BY Table1.Field1, Table2.Field2
HTH,
Gert-Jan
Conrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Conrad,
In General it should never take SQL Server 68 seconds to sort 124
records... SOmething else is going on here ... Run the query in Query
Analyzer with ShowPlan ON, and see what step in the showplan is taking that
long...
"Conrad Chan" wrote:

> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Thanks CBretana,
I did look into query analyzer. 90% is done on the Sort. The only thing I
found may be interested is that the estimated row count is 15,000 compared
with 124 row count.
Conrad
"CBretana" wrote:
> Conrad,
> In General it should never take SQL Server 68 seconds to sort 124
> records... SOmething else is going on here ... Run the query in Query
> Analyzer with ShowPlan ON, and see what step in the showplan is taking tha
t
> long...
>
> "Conrad Chan" wrote:
>|||Then you have a filter in the query somewhere, which is reducing the output
from 15,000 to 124, and the sort is happening on the entire 15k recordset,
not the final 124... Suggestion
Rewrite the query as
Select <Stuff>
From (SubSquery: Select Stuff
From <Tables>
Where <Here goes filter predicate tha treduces 15k - 124)
Order By <Order by Clause>
Then inner query must process the filter and deliver the 124 records to the
outer part, where the Order By is...
See if that works...
"Conrad Chan" wrote:
> Thanks CBretana,
> I did look into query analyzer. 90% is done on the Sort. The only thing
I
> found may be interested is that the estimated row count is 15,000 compared
> with 124 row count.
> Conrad
> "CBretana" wrote:
>|||No luck. I simply try to do exactly like you suggest. SQL is smart enough
to realize they are the same. (It is too smart to be stupid)
However, for testing purpose, if I put a TOP inside my sub-select it does
return in 2s.
SELECT * FROM (
SELECT TOP 124 * FROM ...
) ORDER BY 1, 4
Conrad
"CBretana" wrote:
> Then you have a filter in the query somewhere, which is reducing the outpu
t
> from 15,000 to 124, and the sort is happening on the entire 15k recordset,
> not the final 124... Suggestion
> Rewrite the query as
> Select <Stuff>
> From (SubSquery: Select Stuff
> From <Tables>
> Where <Here goes filter predicate tha treduces 15k - 124)
> Order By <Order by Clause>
> Then inner query must process the filter and deliver the 124 records to th
e
> outer part, where the Order By is...
> See if that works...
> "Conrad Chan" wrote:
>sql

Order by column alias

I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:

1WITH ProjectListAS2(3SELECT4Id,5Name,6Created,7(SELECTCOUNT(*)FROM UserProjectsWHERE ProjectId = p.Id)AS NumberOfUsers,8 ROW_NUMBER()OVER (ORDER BY Id)AS'RowNumber'9FROM Projects p10)11SELECT *12FROM ProjectList13WHERE RowNumberBETWEEN 50AND 60;

This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.
When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error:

Msg 207, Level 16, State 1, Line 10
Invalid column name 'NumberOfUsers'.

I read this in the documentation:

When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specifiedto represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.

So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this

Hi i am not 100% sure if its work but try

select * from ProjectList where RowNumber between 50 and 60

order by 4

|||

Maybe you can try something like this with another inner query?

WITH ProjectListAS
(
Select RR.*,
ROW_NUMBER()OVER (ORDER BY NumberOfUsers)AS'RowNumber'
from (SELECT
Id,
Name,
Created,
(SELECTCOUNT(*)FROM UserProjectsWHERE ProjectId = p.Id)AS NumberOfUsers,
FROM Projects p) RR
)
SELECT *
FROM ProjectList
WHERE RowNumberBETWEEN 50AND 60;

|||

"ORDER BY 4" apparently does not work with windowed functions. This is the error message i got:

Msg 5308, Level 16, State 1, Line 1

Windowed functions do not support integer indices as ORDER BY clause expressions.

|||

jpazgier:
Maybe you can try something like this with another inner query?
WITH ProjectListAS
(
Select RR.*,
ROW_NUMBER()OVER (ORDER BY NumberOfUsers)AS'RowNumber'
from (SELECT
Id,
Name,
Created,
(SELECTCOUNT(*)FROM UserProjectsWHERE ProjectId = p.Id)AS NumberOfUsers,
FROM Projects p) RR
)
SELECT *
FROM ProjectList
WHERE RowNumberBETWEEN 50AND 60;

Works like a charm! Thank you :)

Order by clause work incorrect

when i try the following SQL batch, I get a result-set which is not order by
datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.

who can help me?thanks in advance
...
select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

DROP TABLE ##stuff_fifo
select * from #fifo_temp

the following can get a correct result:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_dateIf I am not mistaken, you have no influence on the physical order of recordsets saved in tables in MSSQL, so your table #fifo_temp will not be ordered by out_date.

Choose the order of recordsets when extracting the data from the table, so use:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')

DROP TABLE ##stuff_fifo
select * from #fifo_temp
ORDER BY out_date

Regards

kbk|||KBK is correct. A table has no inherant "order" for either columns or rows, although a result set has order for both. The only place where order makes any sense (or difference) is in the result set.

-PatP|||Set Out_Date as your clustered index and the data will be ordered the way you want, but if the order is important to you then it is best to specify it each time you select from the dataset using and ORDER BY clause.|||I see the problem.Thank all kindly friends.

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 problem

Hello,

Ive got a column which stores integers ranging from 0-200. I need to order them so that 1 is first, and 0 is last like 1,2,2,3,4,6,8...98...0,0,0

My Order By clause statement looks like 'ORDER BY column_name', but obviously this will put the '0' records at the top. Is there a way around this?

Thanks, Curt.

Do you have just one 0 record or more than 1?

|||

Use Case in your Order By clause:

OrderbyCASEWhen column_name=0then 201else column_nameEND

|||

i think you'll have to use union something like

select PID, Name from tt2 where PID%10 = 0
union
select PID, Name from tt2 where PID%10 <>0

thanks,

satish.

|||

Thanks limno, that works perfect.

sql

ORDER BY clause - newbie question

Hi,

Is there any way of passing a variable instead of a hard-coded column name in the ORDER BY clause? E.g.

declare @.OrderCol int
set @.OrderCol = 1 select * from tbl_Box order by @.OrderCol

I know the above code won't run. What I need is be able to determine to sort column at run-time so that instead of writing four different stored procedures with hard-coded order by clauses, I could pass the sort column as an extra parameter to a generic stored procedure. Is that possible at all?

Any help will be appreciated.

Cheers,

Vladislav

Hi Vladislav,

Yes, you can. In your scenario, you would:

declare @.s nvarchar(255),
@.c nvarchar(100)

set @.s = 'select * from tblBox order by '
set @.c = '1' --or 2 or 'BoxNumber' etc.

set @.s = @.s + @.c

exec sp_executesql @.s

Cheers

Rob

|||

Hi Rob,

Thanks a lot. This should certainly help. What I was also looking for is be able to create the following stored procedure

MyDB_sp_GetBoxesByCustomerId [param 1] @.CustomerId int, [param 2] @.SortColumn nvarchar(128)

After some data manipulation, this stored procedure would return a resultset sorted based on the input column name. I would use this stored procedure in my .NET application.

Thanks to your advice, I now know I can build an SQL string and, using sp_exectesql, run it in a .NET program, but I was hoping to find a solution to keep all the 'messy' SQL manipulations inside the stored procedure. Do you think this will be possible?

Once again, thanks your your help.

Cheers,

Vladislav

|||

Hi,

Maybe you can use a construction like:

Select * From Table
Order by Case @.xSort
When 1 Then ColumnName1
When 2 Then ColumnName2
When 3 Then ColumnName3
End

The @.xSort would need to be an input parameter to your procedure

Best regards Georg
www.l4ndash.com - Log4net Dashboard / Log4net viewer|||

Thanks alot, Georg. This is certainly a better solution.

Regards,

Vladislav

|||If the possible sort columns are not type-compatible, you will need to do this:

...

order by

case @.xSort when 1 then ColumnName1 end,

case @.xSort when 2 then ColumnName2 end,

case @.xSort when 3 then ColumnName3 end

If you don't do this, the CASE statement will raise an exception the

first time you sort by a column containing a value that cannot be

converted to the highest-precedence type of the three columns.

This version will also avoid unnecessary type conversion in the CASE

statement that could lead to a slower-running query, if an index can't

be used as a result.

Steve Kass

Drew University|||

Drew,

Thanks a bunch. I tried the initial version. As you predicted, I got an exception because my query indeed had a column that could not be converted to the first column. With nothing in MSDN, I was just about to rewrite the stored procedure, when I thought I should check out the forum once more.

Once again, thank a lot.

Cheers,

Vladislav

Order by Clause

Hi,
I have column with values January, February and so on. I need to perform
sort based on months instead the system sorts it by Alphabetical Order. Any
Hint?
Thanks
MannyManny Chohan wrote:
> Hi,
> I have column with values January, February and so on. I need to perform
> sort based on months instead the system sorts it by Alphabetical Order. An
y
> Hint?
> Thanks
> Manny
How about storing dates as DATETIME / SMALLDATETIME rather than
strings? If it's too late to do that then you can try:
SELECT mth
FROM tbl
ORDER BY CONVERT(DATETIME,mth+' 01 2000',1) ;
David Portas
SQL Server MVP
--|||do you not have a real date? if not, then do you at least have a year as
well? if not, sorting my month number is pretty meaningless. (e.g. Jan
05 comes after Dec 04).
Manny Chohan wrote:
> Hi,
> I have column with values January, February and so on. I need to perform
> sort based on months instead the system sorts it by Alphabetical Order. An
y
> Hint?
> Thanks
> Manny|||Manny Chohan wrote:

> I have column with values January, February and so on. I need to
> perform sort based on months instead the system sorts it by
> Alphabetical Order. Any Hint?
Create a table with months:
Table Months
ID int NOT NULL,
MonthName varchar(20)
and do a join on that. Normally it would be better to save the month
ID instead of the full text.
HTH,
Stijn Verrept.|||... order by
charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Augus
t*September*October*November*December*')
Steve Kass
Drew University
Manny Chohan wrote:

>Hi,
>I have column with values January, February and so on. I need to perform
>sort based on months instead the system sorts it by Alphabetical Order. Any
>Hint?
>Thanks
>Manny
>|||Thanks. It worked.
"Steve Kass" wrote:

> ... order by
> charindex(monthnamecol+'*','January*Febr
uary*March*April*May*June*July*Aug
ust*September*October*November*December*
')
> Steve Kass
> Drew University
> Manny Chohan wrote:
>
>|||I apologize for the off-topic comment, but this one is just too good to pass
:

> do you not have a real date?
How many times have I been asked that question - not necesarily in the same
context, but still... :)
ML
http://milambda.blogspot.com/|||David with all due respect, I'd suggest a datetime format that carries the
century. Yes, you specify the year 2000 but we should all be developing cod
e
that doesn't leave ambiguity because you never know what SQL Server defaults
will be in the future and more impportantly how your technique will be
applied to other situations.
CONVERT(DATETIME,mth+' 01 2000',101) --mm/dd/yyyy format
And, we should be thinking globally so it really should be:
CONVERT(DATETIME,mth+' 01 2000',112) --yyyymmdd format
Just my two cents,
Joe
"David Portas" wrote:

> Manny Chohan wrote:
> How about storing dates as DATETIME / SMALLDATETIME rather than
> strings? If it's too late to do that then you can try:
> SELECT mth
> FROM tbl
> ORDER BY CONVERT(DATETIME,mth+' 01 2000',1) ;
> --
> David Portas
> SQL Server MVP
> --
>|||Joe from WI wrote:
> David with all due respect, I'd suggest a datetime format that carries the
> century. Yes, you specify the year 2000 but we should all be developing c
ode
> that doesn't leave ambiguity because you never know what SQL Server defaul
ts
> will be in the future and more impportantly how your technique will be
> applied to other situations.
> CONVERT(DATETIME,mth+' 01 2000',101) --mm/dd/yyyy format
>
A good point.

> And, we should be thinking globally so it really should be:
> CONVERT(DATETIME,mth+' 01 2000',112) --yyyymmdd format
>
That does work although on the face of it the string is wrong. 112
defines the format as YYYYMMDD, which is not what you have specified.
There is some implict conversion at work here so I'd stick to the 101
version because the string that's passed complies with the documented
format and behaviour for CONVERT.
David Portas
SQL Server MVP
--sql

Friday, March 30, 2012

order of columns in composite index

ORDER BY with alias

ORDER BY using @variables

Wednesday, March 28, 2012

ORDER BY question: splitting string into 2 orders?

Order by problem

Order by problem

Order by problem

Order By not working as thought

Monday, March 26, 2012

ORDER BY Issue on funky field names

ORDER BY Issue on funky field names

Friday, March 23, 2012

ORDER BY DESC

Order By date and time of insert

Order By computed columns

Order by column alias

Order by clause work incorrect

ORDER BY clause with unknown column name

Order By clause problem

ORDER BY clause - newbie question

Order by Clause

Oracle DBA to learn MS SQL

Blog Archive

About Me