CODEWITHSUNDEEP
sqlsql-serverjoinrownum
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Rownum order is incorrect after join - SQL Server

http://sqlfiddle.com/#!18/97fbe/1 - fiddle

I have tried to demo my real life scenario as much as possible

Tables:

CREATE TABLE [OrderTable] 
(
    [id] int,
    [OrderGroupID] int,
    [Total] int,
    [fkPerson] int,
    [fkitem] int

    PRIMARY KEY (id)
) 

INSERT INTO [OrderTable] (id, OrderGroupID, Total ,[fkPerson], [fkItem]) 
VALUES
  ('1', '1', '20', '1', '1'),
  ('2', '1', '45', '2', '2'),
  ('3', '2', '32', '1', '1'),
  ('4', '2', '30', '2', '2'),
  ('5', '2', '32', '1', '1'),
  ('6', '2', '32', '3', '1'),
  ('7', '2', '32', '4', '1'),
  ('8', '2', '32', '4', '1'),
  ('9', '2', '32', '5', '1');

CREATE TABLE [Person] 
(
    [id] int,
    [Name] varchar(32)

    PRIMARY KEY (id)
) 

INSERT INTO [Person] (id, Name) 
VALUES
  ('1', 'Fred'),
  ('2', 'Sam'),
  ('3', 'Ryan'),
  ('4', 'Tim'),
  ('5', 'Gary');

CREATE TABLE [Item] 
(
    [id] int,
    [ItemNo] varchar(32),
    [Price] int

    PRIMARY KEY (id)
) 

INSERT INTO [Item] (id, ItemNo, Price) 
VALUES
  ('1', '453', '23'),
  ('2', '657', '34');

Query:

WITH TABLE1 AS 
(
    SELECT 
        -- P.ID AS [PersonID], 
        -- P.Name, 
        SUM(OT.[Total]) AS [Total], 
        i.[id] AS [ItemID],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
        ot.fkperson,
        [fkItem]
    FROM 
        OrderTable OT
    -- INNER JOIN Person P ON P.ID = OT.fkperson
    INNER JOIN 
        Item I ON I.[id] = OT.[fkItem]
    GROUP BY 
        -- P.ID, P.Name,
        i.id, ot.fkperson, [fkItem]
)
SELECT 
    t1.[fkperson],
    P.[Name],
    t1.[itemid],
    t1.[total],
    t1.[rownum]
    -- Totalrows = (SELECT MAX(rownum) FROM TABLE1)
FROM 
    TABLE1 T1
INNER JOIN 
    Person P ON P.ID = T1.fkperson
INNER JOIN 
    Item I ON I.[id] = T1.[fkItem]

Result:

| fkperson | Name | itemid | total | rownum |
+----------+------+--------+-------+--------+
|        1 | Fred |      1 |    84 |      1 |
|        3 | Ryan |      1 |    32 |      2 |
|        4 |  Tim |      1 |    64 |      3 |
|        5 | Gary |      1 |    32 |      4 |
|        2 |  Sam |      2 |    75 |      5 |

which is the result I want. However, my real-life example is giving me the row number in a weird order. I know its an issue with a join because when i comment these join:

INNER JOIN 
    Person P ON P.ID = T1.fkperson
INNER JOIN 
    Item I ON I.[id] = T1.[fkItem]

out it works fine.

| fkperson | Name | itemid | total | rownum |
|----------|------|--------|-------|--------|
|        1 | Fred |      1 |    84 |      4 |
|        3 | Ryan |      1 |    32 |      3 |
|        4 |  Tim |      1 |    64 |      5 |
|        5 | Gary |      1 |    32 |      1 |
|        2 |  Sam |      2 |    75 |      2 |

Has anyone got any advice on how the join would be causing these weird rownumber ordering? Or point me in the right direction. Thanks

Upvotes: 2

Views: 178

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You are using order by (select null). That means indeterminate ordering. And the order can change from one invocation of the query to another.

You should not be depending on default ordering, even by an external order by. If you want values in a particular order, specify that ordering explicitly in the order by in the windowing clause.

Upvotes: 2

marc_s
marc_s

Reputation: 754588

Any relational database is inherently UNordered - and you won't get any guaranteed order UNLESS you explicitly ask for it - by means of an ORDER BY clause on your outer query.

You need to add the ORDER BY explicitly - like this:

WITH TABLE1 AS 
(
    .....
)
SELECT 
    (list of columns ....)
FROM 
    TABLE1 T1
INNER JOIN 
    Person P ON P.ID = T1.fkperson
INNER JOIN 
    Item I ON I.[id] = T1.[fkItem]
ORDER BY 
    T1.rownum

Upvotes: 3

Related Questions