Quick SQL Drill (Sorting)

Question

Quick SQL Drill (Sorting)

John Jakob

SSCommitted

Points: 1720
More actions
January 10, 2005 at 4:46 pm

#65330

For your New Year's resolution you've decided to measure your weight twice a month, each month. You build the following table in SQL:
CREATE TABLE Weight
(
   WeightID    INT      NOT NULL PRIMARY KEY,
   WeightDate DATETIME NOT NULL,
   WeightValue INT      NOT NULL
)
GO
And populate it as follows:
INSERT INTO Weight VALUES(1, '12/1/04', 170)
INSERT INTO Weight VALUES(2, '12/15/04', 181)
INSERT INTO Weight VALUES(3, '1/1/05',   192)
INSERT INTO Weight VALUES(4, '1/15/05', 185)
Which of the following queries should you use to display your weight history in proper sequence?
-- Query #1
SELECT CONVERT(VARCHAR(8), WeightDate, 10) AS [Weight Date],
       WeightValue
FROM   Weight
ORDER BY [Weight Date]
-- Query #2
SELECT CONVERT(VARCHAR(8), WeightDate, 10) AS [WeightDate],
       W.WeightValue
FROM   Weight W
ORDER BY W.WeightDate
-- Query #3
SELECT CONVERT(VARCHAR(8), WeightDate, 10) AS [Weight Date],
       W.WeightValue
FROM   Weight W
ORDER BY W.WeightDate

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 1

Good one.

Without cranking up QA, I think Query #1 will give you the result in the wrong order, mm-dd-yy instead of ccyymmdd like you want it. Queries #2 and 3 should give it to you in date order.

There is no "i" in team, but idiot has two.

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 2

Because your column is defined datetime ( Correct datatyping ! &nbsp it is best to order on the column itself because it handles datetime very good and if there is an index defined for that column, it can be used to support the ordering. If you order on the converted column (char(xx)) this ordering will be done after the select of the resultset and according to the rules of the new datatype !

and because of a litle buggy thing in sqlserver only query 3 will give you the correct order.(In query 2 you have used a columnalias [WeightDate] that is equal to the column name and it is confused! )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Yeah, there was a topic on this a few months back where someone was showing this and questioned how it happened. And there may have been a Question Of The Day on it as well.

Johan Bijnens SSC Guru Points: 135373 More actions · Answer 4

That's what I like about this site and the QOD's.

Sometimes things are kept in mind because of an "Aha-Erlebnis"