Problem with ORDER BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with ORDER BY

Author

Topic

edpfister
Starting Member

5 Posts

Posted - 2009-08-05 : 16:57:37

I have the following query (somewhat simplified for clarity).

SELECT InfoRun.SerialNumber, InfoRun.RunNumber, InfoRun.DateTimeStarted, InfoRun.StationName (others removed)
FROM InfoRun
WHERE (InfoRun.DateTimeStarted >= '7/11/2009' AND InfoRun.DateTimeStarted < '7/18/2009 12:00:01 AM')
ORDER BY InfoRun.SerialNumber,InfoRun.RunNumber

This returns the data (see snippet below) I am interested in but the ordering is not quite what I expect. The results I am getting back seem to be equivilent of doing an "ORDER BY InfoRun.SerialNumber, InfoRun.StationName, InfoRun.RunNumber" but that is not what I am doing. Any ideas why this would occur.

Thanks.

----------------

040903250015 83 BT
040903250015 88 BT
040903250015 90 BT
040903250015 91 BT
040903250015 93 BT
040903250015 102 BT
040903250015 103 BT
040903250015 113 BT
040903250015 120 BT
040903250015 89 BTPMTS
040903250015 94 BTPMTS
040903250015 98 BTPMTS
040903250015 119 BTPMTS
040903250015 81 Toad - Prod
040903250015 82 Toad - Prod
040903250015 84 Toad - Prod
040903250015 85 Toad - Prod
040903250015 86 Toad - Prod
040903250015 92 Toad - Prod
040903250015 95 Toad - Prod
040903250015 96 Toad - Prod
040903250015 97 Toad - Prod
040903250015 100 Toad - Prod
040903250015 101 Toad - Prod
040903250015 112 Toad - Prod
040903250015 114 Toad - Prod
040903250015 115 Toad - Prod
040903250015 116 Toad - Prod
040903250015 118 Toad - Prod
040903250015 121 Toad - Prod
040903250015 122 Toad - Prod
040903250015 123 Toad - Prod
040903250015 87 Toad - Repair
040903250015 99 Toad - Repair
040903250015 104 Toad - Repair
040903250015 105 Toad - Repair
040903250015 106 Toad - Repair
040903250015 107 Toad - Repair
040903250015 108 Toad - Repair
040903250015 109 Toad - Repair
040903250015 110 Toad - Repair
040903250015 111 Toad - Repair
040903250015 117 Toad - Repair

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-05 : 17:27:04

Sorry but I cannot see the relation between your shown statement and your shown output.
Also I dont have a clue about the datatypes which are significant for sorting.

No, you're never too old to Yak'n'Roll if you're too young to die.

edpfister
Starting Member

5 Posts

Posted - 2009-08-05 : 18:11:39

Ok, sorry if my example wasn't clear. Here is a slightly different query that has not been edited in any way and a sampling of the data (full set of data is too large to post). I just thought it would be hard to see the problem with all that data.

-----
Query
-----

SELECT InfoRun.SerialNumber,InfoRun.RunNumber,InfoRun.Status,InfoRun.DateTimeStarted,InfoRun.DateTimeCompleted,InfoRun.ElapsedTime,InfoRun.ProjectVersion,InfoRun.StationName,InfoRun.StationNumber,InfoRun.FailedTests
FROM Dubai.dbo.InfoRun
WHERE (InfoRun.SerialNumber >= '040903250015' AND InfoRun.SerialNumber <= '040903250015')
ORDER BY InfoRun.SerialNumber,InfoRun.RunNumber

-----
Data
-----

040903250015 6 CANCELED 3/26/09 8:35 AM 3/26/09 8:40 AM 274 138.1.35 (REV_7) BT BT_
040903250015 78 FAIL 6/27/09 2:37 PM 6/27/09 2:38 PM 60 138.1.35 BT BT_01 Chip X InfoFails Scanner Stitching
040903250015 79 CANCELED 6/27/09 2:40 PM 6/27/09 2:40 PM 27 138.1.35 BT BT_01
040903250015 80 FAIL 6/27/09 2:52 PM 6/27/09 2:53 PM 64 138.1.35 BT BT_02 Power On ScannerScanCalibDone() - Scanner Initialization Timed Out
040903250015 83 PASS 7/13/09 11:59 AM 7/13/09 12:02 PM 155 5.1.24 BT BT_
040903250015 88 PASS 7/13/09 3:07 PM 7/13/09 3:10 PM 170 5.1.24 BT BT_
040903250015 90 FAIL 7/14/09 3:00 PM 7/14/09 3:02 PM 91 5.1.26 BT BT_01 Scan - MTF YLocs: 27,
040903250015 91 FAIL 7/14/09 3:04 PM 7/14/09 3:05 PM 79 5.1.26 BT BT_01 Scan - MTF YLocs: 27,
040903250015 93 PASS 7/15/09 3:25 PM 7/15/09 3:27 PM 154 5.1.26 BT BT_01
040903250015 102 PASS 7/15/09 4:39 PM 7/15/09 4:42 PM 155 5.1.26 (REV_3) BT BT_01
040903250015 103 FAIL 7/15/09 4:45 PM 7/15/09 4:47 PM 92 5.1.26 (REV_3) BT BT_02 Scan - MTF YLocs: 28,
040903250015 113 PASS 7/16/09 11:32 AM 7/16/09 11:35 AM 149 5.1.26 BT BT_01
040903250015 120 FAIL 7/16/09 2:40 PM 7/16/09 2:41 PM 89 5.1.26 (REV_3) BT BT_01 Scan - MTF YLocs: 7,
040903250015 3 FAIL 3/25/09 5:52 PM 3/25/09 5:55 PM 137 138.1.35 (REV_7) BT(Repair) BT_ Power On Scannerscan cissm pwrcaldone; , Write Error
040903250015 4 CANCELED 3/25/09 5:56 PM 3/25/09 5:57 PM 77 138.1.35 (REV_7) BT(Repair) BT_
040903250015 5 CANCELED 3/25/09 6:09 PM 3/25/09 6:11 PM 101 138.1.35 (REV_7) BT(Repair) BT_
040903250015 9 PASS 3/27/09 12:40 PM 3/27/09 12:42 PM 123 138.1.35 (REV_9) BT(Repair) BT_
040903250015 1 PASS 3/25/09 11:45 AM 3/25/09 11:45 AM 8 138.1.35 (REV_4) BTPMTS BTPMTS_01
040903250015 89 PASS 7/13/09 4:29 PM 7/13/09 4:29 PM 15 5.1.24 BTPMTS BTPMTS_
040903250015 94 PASS 7/15/09 3:36 PM 7/15/09 3:36 PM 7 5.1.26 (REV_2) BTPMTS BTPMTS_
040903250015 98 PASS 7/15/09 4:20 PM 7/15/09 4:20 PM 8 5.1.26 (REV_3) BTPMTS BTPMTS_
040903250015 119 PASS 7/16/09 2:29 PM 7/16/09 2:30 PM 36 5.1.26 (REV_3) BTPMTS BTPMTS_

-----
Data Types
-----
Name Data Type Length Allow Nulls
SerialNumber varchar 20 Yes
RunNumber int 4 Yes
GroupRunNumber int 4 Yes
ProjectVersion varchar 50 Yes
DateTimeStarted datetime 8 Yes
DateTimeSCompleted datetime 8 Yes
ElapsedTime int 4 Yes
StationNumber varchar 50 Yes
StationName varchar 50 Yes
Status varchar 50 Yes
FailTests text 16 Yes
Modified_TimeStamp datetime 8 Yes
FailedTestsDetails text 16 Yes

johnconstraint
Starting Member

23 Posts

Posted - 2009-08-06 : 10:20:05

I tried this, working for me. Not sure what the problem is....

declare @t table (sl varchar(20), run int, sta varchar(50))
insert @t
SELECT '040903250015', 83, 'BTPMTS' UNION ALL
SELECT '040903250015', 88, 'BT' UNION ALL
SELECT '040903250015', 90, 'BT' UNION ALL
SELECT '040903250015', 91, 'BTPMTS' UNION ALL
SELECT '040903250015', 93, 'BT' UNION ALL
SELECT '040903250015', 102, 'BT' UNION ALL
SELECT '040903250015', 103, 'BT' UNION ALL
SELECT '040903250015', 113, 'BT' UNION ALL
SELECT '040903250015', 120, 'BT' UNION ALL
SELECT '040903250016', 89, 'BTPMTS' UNION ALL
SELECT '040903250016', 94, 'BTPMTS' UNION ALL
SELECT '040903250016', 98, 'BTPMTS' UNION ALL
SELECT '040903250016', 119, 'BTPMTS' UNION ALL
SELECT '040903250017', 81, 'Toad - Prod' UNION ALL
SELECT '040903250017', 82, 'Toad - Prod' UNION ALL
SELECT '040903250017', 84, 'Toad - Prod' UNION ALL
SELECT '040903250017', 85, 'Toad - Prod' UNION ALL
SELECT '040903250017', 86, 'Toad - Prod' UNION ALL
SELECT '040903250017', 92, 'Toad - Prod' UNION ALL
SELECT '040903250015', 95, 'Toad - Prod' UNION ALL
SELECT '040903250015', 96, 'Toad - Prod' UNION ALL
SELECT '040903250015', 97, 'Toad - Prod' UNION ALL
SELECT '040903250015', 100, 'Toad - Prod' UNION ALL
SELECT '040903250015', 101, 'Toad - Prod' UNION ALL
SELECT '040903250015', 112, 'Toad - Prod' UNION ALL
SELECT '040903250015', 114, 'Toad - Prod' UNION ALL
SELECT '040903250015', 115, 'Toad - Prod' UNION ALL
SELECT '040903250015', 116, 'Toad - Prod' UNION ALL
SELECT '040903250015', 118, 'Toad - Prod' UNION ALL
SELECT '040903250015', 121, 'Toad - Prod' UNION ALL
SELECT '040903250015', 122, 'Toad - Prod' UNION ALL
SELECT '040903250015', 123, 'Toad - Prod' UNION ALL
SELECT '040903250018', 87, 'Toad - Repair' UNION ALL
SELECT '040903250018', 99, 'Toad - Repair' UNION ALL
SELECT '040903250018', 104, 'Toad - Repair' UNION ALL
SELECT '040903250015', 105, 'Toad - Repair' UNION ALL
SELECT '040903250015', 106, 'Toad - Repair' UNION ALL
SELECT '040903250015', 107, 'Toad - Repair' UNION ALL
SELECT '040903250015', 108, 'Toad - Repair' UNION ALL
SELECT '040903250015', 109, 'Toad - Repair' UNION ALL
SELECT '040903250015', 110, 'Toad - Repair' UNION ALL
SELECT '040903250015', 111, 'Toad - Repair' UNION ALL
SELECT '040903250015', 117, 'Toad - Repair'

SELECT * FROM @t
ORDER BY sl, run

edpfister
Starting Member

5 Posts

Posted - 2009-08-06 : 12:42:28

Yeah, I don't see any reason why this would be behaving like this. It's not critical I guess, so I can live with it for the time being. Perhaps something is happening on the Excel side or something (where the query is being executed in my case). Thanks for looking into it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources