Post reply

unique result

  • April 3, 2010 at 6:04 pm

    #146254

    Size DBName Date

    1329476 ABC 2009-06-08 06:00:00.843

    1329476 ABC 2009-06-15 06:00:01.120

    1373372 ABC 2009-06-22 06:00:00.270

    1373372 ABC 2009-06-29 06:00:00.743

    1373372 ABC 2009-07-06 06:00:00.713

    1373372 ABC 2009-07-13 06:00:01.130

    1373372 ABC 2009-07-20 06:00:01.063

    1388286 ABC 2009-08-10 06:00:00.240

    1388286 ABC 2009-08-17 06:00:00.427

    Hoa can i get the Required result below fro mthe above data.

    1329476 ABC 2009-06-08 06:00:00.843

    1373372 ABC 2009-06-29 06:00:00.743

    1388286 ABC 2009-08-10 06:00:00.240

  • April 3, 2010 at 10:53 pm

    #1145412

    Tara, from your expected result set, i see u want the minimum date for the combination of size and DB name..

    (if the above statement is right, then can u pls edit your desired result., it seems contradictory)

    you could try something like :

    SELECT dbname, size, MIN(date) FROM YOUR_TABLE_NAME

    GROUP BY dbname, size

    Also, please provide your table strucutre, sample data in ready-to-use format... this will help us in resolving the issue soon, which wil save both of our times...:-)

  • April 3, 2010 at 11:59 pm

    #1145415

    Tara,

    To get your result a GROUP BY clause has to be used.

    When a GROUP BY clause is used, the required columns have to present in either with the AGGREGATE functions (like MIN() as above) or column should be mentioned in the GROUP BY part.

    John

  • April 4, 2010 at 4:20 am

    #1145434

    DECLARE @Table

    TABLE (

    name SYSNAME NOT NULL,

    size INTEGER NOT NULL,

    date_time DATETIME NOT NULL,

    PRIMARY KEY (name, size, date_time)

    );

    -- Sample data

    INSERT @Table

    (name, size, date_time)

    SELECT 'ABC', 1329476, '2009-06-08T06:00:00.843' UNION ALL

    SELECT 'ABC', 1329476, '2009-06-15 06:00:01.120' UNION ALL

    SELECT 'ABC', 1373372, '2009-06-22 06:00:00.270' UNION ALL

    SELECT 'ABC', 1373372, '2009-06-29 06:00:00.743' UNION ALL

    SELECT 'ABC', 1373372, '2009-07-06 06:00:00.713' UNION ALL

    SELECT 'ABC', 1373372, '2009-07-13 06:00:01.130' UNION ALL

    SELECT 'ABC', 1373372, '2009-07-20 06:00:01.063' UNION ALL

    SELECT 'ABC', 1388286, '2009-08-10 06:00:00.240' UNION ALL

    SELECT 'ABC', 1388286, '2009-08-17 06:00:00.427';

    -- One possible solution

    SELECT T1.name,

    T1.size,

    T1.date_time

    FROM @Table T1

    WHERE date_time =

    (

    SELECT MIN(T2.date_time)

    FROM @Table T2

    WHERE T2.name = T1.name

    AND T2.size = T1.size

    )

    ORDER BY

    T1.name,

    T1.size,

    T1.date_time;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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