CODEWITHSUNDEEP
sqlsql-servert-sqlsql-order-by
Steve-o169
Steve-o169

Reputation: 2146

ORDER BY not sorting as intended SQL

I'm having an issue sorting with the ORDER BY clause and was hoping someone could assist me in figuring out why this is not sorting as intended.

I am pulling data points from multiple databases and adding them to a temporary table for output in an SSRS report. I've also included a WHILE statement to insert NULL values to fill the intended space with empty rows if there are not enough records.

I've changed most of the column names to protect potentially sensitive information, but the column in question is v1 and ob.LineNumber.

USE db    GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[db1]
@PR_ID INT

AS
BEGIN

SET NOCOUNT ON;

CREATE TABLE #temp
(v1 INT,
v2 VARCHAR(50),
v3 VARCHAR(50),
v4 VARCHAR(50),
v5 FLOAT,
v6 FLOAT,
v7 FLOAT,
v8 FLOAT,
v9 VARCHAR(50),
v10 FLOAT,
v11 VARCHAR(50),
v12 VARCHAR(50),
v13 VARCHAR(50),
v14 VARCHAR(50),
v15 FLOAT)

DECLARE @RowCount INT, @h INT
SET @RowCount = 11

INSERT INTO #temp(v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15)
SELECT  ob.LineNumber AS v1,
        NULL,
        NULL,
        NULL,
        CASE WHEN ob.Length IS NULL THEN 0 ELSE ob.Length END AS v5,
        CASE WHEN ob.Width IS NULL THEN 0 ELSE ob.Width END AS v6,
        CASE WHEN ob.Length IS NULL THEN 0 ELSE (ob.Length * ob.Width) END AS v7,
        NULL,
        sovg.Grade AS v9,
        ob.AYB As v10,
        ob.CC AS v11,
        NULL,
        NULL,
        ob.PC AS v14,
        ob.VT AS v15

FROM t_ob WITH (NOLOCK)
    LEFT JOIN t_otpr otpr WITH (NOLOCK) ON ob.ID=otpr.ID
    LEFT JOIN t_pr pr WITH (NOLOCK) ON otpr.PR_ID=pr.PR_ID
    LEFT JOIN t_otb otb WITH (NOLOCK) ON ob.ID=otb.ID
    LEFT JOIN t_b b WITH (NOLOCK) ON otb.B_ID=b.B_ID
    LEFT JOIN t_G sovg WITH (NOLOCK) ON ob.G_ID=sovg.G_ID

WHERE       pr.PR_ID = @PR_ID
ORDER BY    ob.LineNumber ASC

SET @h = (SELECT COUNT(*) FROM #temp)

WHILE  @h < @RowCount OR @h % @RowCount > 0
BEGIN
    INSERT INTO #temp (v1) VALUES (NULL)
    SET @h = @h + 1
END

SELECT * FROM #temp 

END

And my results come up like this:

v1
----------
6
2
8
9
5
4
3
7

Can anyone see where I'm going wrong?

Upvotes: 1

Views: 111

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

The problem is the ORDER BY only happens on the INSERT.

Databases work because of relational set theory. Adhering to that theory is what allows for many of the optimizations and practices that make them both fast to query and safe for simultaneous access. In this context, a database table is a relational set, and relational sets are not ordered by definition.

You can insert into the table in any order you want, but that order does not matter when it's time to retrieve the data again. To ensure you get a specific order, you must declare that order at the time you query the table:

SELECT * FROM #temp ORDER BY v1

Additionally, the ORDER BY clause at the end of INSERT statement serves no functional purpose and can be removed.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271111

If you are referring to the result set from:

SELECT * FROM #temp

Then what you are doing wrong is assuming that the result set is in a particular order. The only way to get a result set in a particular order is to include an explicit ORDER BY clause for the outermost SELECT.

Upvotes: 4

Related Questions