Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Unanswered: DISTINCT applies sort to derived table

    Hi there,

    I'm using SQL server 2005, so hope this is the right place for the below query.

    Not sure if I'm coming at this problem the wrong way but I'm having issues with DISTINCT.

    Given an example table:

    CREATE TABLE [dbo].[TEMP_Example](
    [name] [nchar](10) NULL,
    [enddate] [datetime] NULL
    ) ON [PRIMARY]

    Filled with a list of names and dates (as you might expect). Firstly, I want to list the names in order of date -

    SELECT name, enddate
    FROM TEMP_Example
    ORDER BY enddate DESC

    This gives me the results:

    bob 2010-10-11 00:00:00.000
    bob 2009-09-11 00:00:00.000
    henry 2009-09-10 00:00:00.000
    jane 2009-08-15 00:00:00.000
    margaret 2009-08-15 00:00:00.000
    mark 2008-04-15 00:00:00.000
    mark 2006-07-11 00:00:00.000
    benji 2005-07-08 00:00:00.000

    So far so good, but now if I want to remove duplicate names, ignoring the date, I come across a problem. The way I assumed I could do it would be to use the below SQL:

    SELECT DISTINCT name
    FROM (SELECT name, enddate
    FROM TEMP_Example
    ORDER BY enddate DESC) AS ordered_list

    However, this gives the results:

    benji
    bob
    henry
    jane
    margaret
    mark

    It's re-orded the set.

    I have a feeling I'm asking something unreasonable of the database and there is a much more obvious simple way to do it. But I can't think how!

    I'd really like a list like this (non duplicated list of names still in date order):

    bob
    henry
    jane
    margaret
    mark
    benji

    I guess distinct firstly sorts the results before looping through and removing duplicates. is there another way to keep the sort order of the derived table? Is there another way I should be approaching this?

    I'd be very grateful for any help!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT name
    FROM   TEMP_Example
    GROUP BY name
    ORDER BY MAX(enddate) DESC
    Your sample data has no overlapping names and dates. Could Jane have a date after the 8th of July, for example?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT name
    FROM   (
            SELECT name
                 , Max(enddate) As max_enddate
            FROM   your_table
            GROUP
                BY name
           ) As a_subquery
    ORDER
        BY max_enddate
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2007
    Posts
    3
    Ah that works great, thanks! There should have been overlapping dates in the sample data, but my random data making abilities failed. Your SQL appears to cope with overlapping dates too though so brillo.

    I guess it works due to the order in which the engine process the query - with yours it does the ORDER BY early on, whereas distinct is jsut manipulating the end result? Does that make sense?

    Thanks again anyway!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I really should reload the page - ignore my reply, as Poots has pointed out, you don't even need to subquery
    George
    Home | Blog

  6. #6
    Join Date
    Oct 2007
    Posts
    3
    Quote Originally Posted by gvee
    I really should reload the page - ignore my reply, as Poots has pointed out, you don't even need to subquery
    Thanks anyway, it's good to see different ways to do the same thing!

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gvee
    I really should reload the page - ignore my reply, as Poots has pointed out, you don't even need to subquery
    Or even a derived table
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    po-TAY-to
    po-TAH-to
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by alun1111
    I guess it works due to the order in which the engine process the query - with yours it does the ORDER BY early on, whereas distinct is jsut manipulating the end result? Does that make sense?
    These are logically different operations, but I think you have pretty well got it.

    In order to perform a distinct, SQL Server has to order all the rows (it then gets all like values together). You can see this in the execution plan. As such, any other order by is lost (in fact I'll bet SQL Server ignored your order by clause completely). There is no way to get your order back because the distinct meant you lost the date attribute. By using group by and MAX() George and I retained a date value for each distinct name and were able to order by that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •