Sort By Date??
-
Hi i am using this sql statement on (SQL Server 2000) and tryin to sort the results oin the tl.Date but as i have already casted it to a nvarchar it sorts it like a char so im just wondering if anyone has any idea how to sort it by date correctly. thanks in advance Tim
select SUBSTRING(CAST(tl.Date as nvarchar), 0, 12) as 'Date', tft.BodyWeight from Person as p
left join TrainingLog tl on tl.PersonId = p.Id
left join TrainingFitnessTesting tft on tl.Id = tft.TrainingLogId
where p.Id = 8
and tl.Date >= '01 January 2005'
and tl.Date <= '03 January 2006' and tft.BodyWeight IS NOT NULL
order by tl.Date asc
-
2 things which work (although they're not entirely satisfactory) are:
1. Use a different alias:
select SUBSTRING(CAST(tl.Date as nvarchar), 0, 12) as 'MyDate'...
2. Cast it back to a date:
...order by cast(tl.Date as date) asc
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
the second option def sems to be the more appropriate one for my problem but when i try to cast it as a date i get this error it seems a bit crazy to me
Server: Msg 243, Level 16, State 1, Line 1
Type Date is not a defined system type.
-
Oops... datetime, rather than date...
...order by cast(tl.Date as datetime) asc
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Excellent work my good man ha ha… thanks very much tho I really appreciate it
-
If tl.Date is a DateTime column to begin with, you don't need to do any of that.... the Order By tl.Date will work correctly even though you've Cast it in the Select.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Have you tried it Jeff? That doesn't seem to work for me...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Won't work. I had a similar issue the other day. Once you've created a column alias with the same name as a column in the table there's no way (that I found) to reference the original column in the order by clause.
I think it's because the column aliases are evaluated before the order by, so when it comes to sort, only the alias is available, not the original column. (Order of execution explaining why you can reference a column alias in the order by, but not in the where clause)
DECLARE @test-2 TABLE (SortOrder FLOAT PRIMARY KEY, Letter char(1))
INSERT INTO @test-2 VALUES (1.2,'a')
INSERT INTO @test-2 VALUES (0.8,'b')
INSERT INTO @test-2 VALUES (1.8,'c')
INSERT INTO @test-2 VALUES (2.1,'d')
INSERT INTO @test-2 VALUES (0.2,'e')
INSERT INTO @test-2 VALUES (1.25,'f')
INSERT INTO @test-2 VALUES (2.4,'g')
SELECT CAST(SortOrder AS INT) AS Sort_Order, Letter
FROM @test-2 T
ORDER BY T.SortOrder
SELECT CAST(SortOrder AS INT) AS SortOrder, Letter
FROM @test-2 T
ORDER BY SortOrder
SELECT CAST(SortOrder AS INT) AS SortOrder, Letter
FROM @test-2 T
ORDER BY T.SortOrder
Have a look at the execution plans for the 3.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GM beat me to it but using his test, I came up with the same correct results all 3 times...
SortOrder,Letter
0,e
0,b
1,a
1,f
1,c
2,d
2,g
Ryan, Yep, I tried it and you are correct (I forgot about this little problem) but only sometimes (which, of course, makes the sort useless as you suggest). If I move the primary key tothe other column in GilaMonster's example, THEN the results are different, but not the first time...
Sort_Order,Letter
0,e
0,b
1,a
1,f
1,c
2,d
2,g
(7 row(s) affected)
SortOrder,Letter
0,b
0,e
1,f
1,c
1,a
2,d
2,g
(7 row(s) affected)
SortOrder,Letter
0,b
0,e
1,f
1,c
1,a
2,d
2,g
(7 row(s) affected)
As you can see, it comes out just fine on the first sort and this works everytime you run it... however, secondary sorts in the same run don't cut it. And, Ryan is correct... this is likely because you can refer to aliases in Order By. I guess I just haven't run into this problem because I normally don't alias conversions the same as the original column name.
Thanks for the eye opener on this one... another Microsoft "feature"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You could alias the original column to a different name in a derived table, then the 'Date' alias in the main select field list is unique.
select
SUBSTRING(CAST(tl.tlDate as nvarchar), 0, 12) as 'Date', tft.BodyWeight
from Person as p
left join (
select [Date] as tlDate, PersonId, Id
from TrainingLog
) tl on tl.PersonId = p.Id
left join TrainingFitnessTesting tft on tl.Id = tft.TrainingLogId
where p.Id = 8
and tl.tlDate >= '01 January 2005'
and tl.tlDate <= '03 January 2006' and tft.BodyWeight IS NOT NULL
order by tl.tlDate asc
By the way, the tl. and tft. references in the where clause are turning your left joins into inner joins.
-
Um, her test, actually
I didn't think of the subquery, thanks for that idea.
I was doing query optimisation when I ran into this. I moved the clustered index on a table onto the column that was ordered by most often (a datetime) and noticed that SQL was still doing a very expensive sort (45% of the query) when it wasn't necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Dang... Your name is even posted right under your handle, too! I missed that... sorry, Gail.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply