Post reply

Different order of records using distinct

  • June 23, 2011 at 4:29 am

    #239853

    Hello,

    I have the next code:

    SELECT udf_datum([Timestamp]) AS datum

    INTO #T

    FROM Table1

    The function udf_datum returns a string (format: MM/DD/YYYY)

    S1: select datum from #T

    S2: select distinct datum from #T

    The resultset of S1 is in a different order than the resultset of S2. The first row returned by S1 contains the value 10/26/2009. The first row returned by S2 contains the value 10/31/2009.

    Does SQL Server use an internal system of ordering records ?

    Thanks and regards,

    Douwe

  • June 23, 2011 at 4:42 am

    #1342908

    Unless you use an ORDER BY clause, SQL Server will return the rows in whichever order it deems most expeditious. Often that will be in the order of the clustered index, but that can't be guaranteed. Please will you post the definition of your function?

    John

  • June 23, 2011 at 5:19 am

    #1342925

    This is my function (made by a colleague):

    ALTER FUNCTION [dbo].[udf_datum] (@invoer smalldatetime)

    RETURNS nvarchar(10) AS

    BEGIN

    declare @uitvoer nvarchar(10)

    set @uitvoer = CONVERT(nvarchar(10), @invoer, 101)

    return @uitvoer

    END

  • June 23, 2011 at 6:50 am

    #1342985

    OK, the only way to guarantee you get the same order is by putting ORDER BY datum at the end of all your SELECT statements.

    John

  • June 23, 2011 at 6:56 am

    #1342994

    OK.

    Thanks.

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

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