locked
Sorting records in a Group By RRS feed

  • Question

  • Hi,

    I've a problem with the following stored procedure Select . It does compile and run, but doesn't return the sorted result I wanted, which was to have the records from tblPieces (alias Pcs) sorted by (in order) Pcs.fkBatchId, Pcs.fkProfileCode, Pcs.Colour. What happens instead, I think, is that the correct records are selected, but in the record creation order.

    CREATE PROCEDURE dbo.LoadOneBatch
    (
        @BatchId int, -- the pkBatchId of the batch required
        @OnlyNew bit -- If true, only consider batches that haven't ever been loaded.
    )
    AS
        SET NOCOUNT OFF
        SELECT Bat.pkBatchId,
            Pcs.*
        FROM tblBatches AS Bat
        JOIN (
            SELECT Bat1.pkBatchId,
            Pcs1.fkProfileCode,
            Pcs1.Colour
            FROM tblBatches AS Bat1
            JOIN tblPieces AS Pcs1 ON Pcs1.fkBatchId = Bat1.pkBatchId 
            WHERE Bat1.pkBatchId = @BatchId
            GROUP BY Bat1.pkBatchId, Pcs1.fkProfileCode, Pcs1.Colour
            ) SubQ ON SubQ.pkBatchId = Bat.pkBatchId
        JOIN tblPieces AS Pcs ON Pcs.fkBatchId = Bat.pkBatchId 
        WHERE (@OnlyNew = 1 And Bat.IsLoaded = 0 And
                Bat.IsCompleted = 0 And Bat.pkBatchId = @BatchId) Or
            (@OnlyNew = 0 And Bat.pkBatchId = @BatchId)
    GO
    EXEC LoadOneBatch @BatchId = 1, @OnlyNew = 0
    

    The DDL for the two tables is:

    CREATE TABLE [tblBatches](
    	[Stamp] timestamp NOT NULL,
    	[pkBatchId] int IDENTITY(1,1) NOT NULL,
    	[BatchNo] varchar(50) NULL,
    	[SubmitTime] [datetime] NULL,
    	[FinishTime] [datetime] NULL,
    	[IsLoaded] bit NULL,
    	[IsCompleted] bit NULL,
    	PRIMARY KEY ( [pkBatchId] ASC )
    )
    
    CREATE TABLE [tblPieces](
    	[Stamp] timestamp NOT NULL,
    	[pkPieceId] int IDENTITY(1,1) NOT NULL,
    	[fkBarId] int NULL,
    	[fkBatchId] int NULL,
    	[PieceNo] varchar(12) NOT NULL,
    	[Descrip] varchar(48) NULL,
    	[Position] real NULL,
    	[LeadPrep] char(1) NOT NULL,
    	[TailPrep] char(1) NOT NULL,
    	[Length] real NOT NULL,
    	[fkProfileCode] varchar(10) NOT NULL,
    	[Colour] varchar(5) NOT NULL,
    	PRIMARY KEY ( [pkPieceId] ASC )
    )
    

    The data records output are roughly:

    pkBatchId   Stamp	            pkPieceId	fkBarId	fkBatchId	PieceNo	        Descrip	                Position	LeadPrep	TailPrep	Length	fkProfileCode	Colour
    1       	0x00000000000036B2	1	        NULL	1	        000000000001	18960 /003/F>1      N2	NULL	    \	        /	        913	    6000	        wht
    1       	0x00000000000036B3	2	        NULL	1	        000000000002	18960 /003/F<1      N2	NULL    	\	        /	        913	    6000	        wht
    
    Friday, April 21, 2006 4:40 PM

Answers

All replies

  • add ORDER BY Pcs.fkBatchId, Pcs.fkProfileCode, Pcs.Colour after your WHERE clause

     

     

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

    Friday, April 21, 2006 4:55 PM
  • Just to reinforce what Denis said, tables don't have order unless you specify an order.  Adding a clustered index to a table will "most" of the time make results come out in a given way, but there is no guarantee.

    The GROUP BY clause tells SQL Server to group together like rows for aggregates, but there is no contract to do it any any given order (which is why there is not ascending or decending order modifier on the columns)

    Friday, April 21, 2006 10:28 PM
  • Thanks to you and Denis.

     Louis Davidson - SQL Server MVP wrote:

    Just to reinforce what Denis said, tables don't have order unless you specify an order. Adding a clustered index to a table will "most" of the time make results come out in a given way, but there is no guarantee.

    The GROUP BY clause tells SQL Server to group together like rows for aggregates, but there is no contract to do it any any given order (which is why there is not ascending or decending order modifier on the columns)



    So in this case do I need the Group By and the attendant subselect, or do I get the same result from a simple query with a specified Order?
    Saturday, April 22, 2006 4:47 PM
  • Since you aren't doing any aggregates, you might be able to rewrite this query without a GROUP BY.  I say might because the GROUP BY might be eliminating duplicates.  If it isn't, then you might just remove the GROUP BY and add the ORDER BY.

    You might also be able to get rid of the derived table, but without knowing the data, it is too hard to guess.

     

    Sunday, April 23, 2006 3:28 PM