SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    yet another distinct thread

    I need to make a field distinct based on max of a date but also return the ID of that specific field.

    I tried:

    select location, MAX(lastUpdateDT), ID
    from allLocations
    group by location

    I need this data set:

    F56:65:32:21 | 01/04/2009 | 1
    F56:65:32:21 | 02/20/2009 | 2
    F56:65:32:23 | 04/06/2009 | 3
    F56:65:32:23 | 03/04/2009 | 4
    F56:65:32:55 | 05/03/2009 | 5
    F56:65:32:55 | 06/01/2009 | 6

    To return:

    F56:65:32:21 | 02/20/2009 | 2
    F56:65:32:23 | 04/06/2009 | 3
    F56:65:32:55 | 06/01/2009 | 6

    This above query obviously doesn't work because the ID field will be arbitrary and not the necessarily the record that got returned.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    there are actually eleven different ways that you can write the query for this problem

    see http://jan.kneschke.de/projects/mysql/groupwise-max

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yep, you need to use a nested query, I think
    Code SQL:
    DROP TABLE IF EXISTS dst;
    CREATE TABLE dst(
      id INTEGER AUTO_INCREMENT,
      lastUpdateDT DATE,
      location VARCHAR(50),
      PRIMARY KEY(id)
    );
     
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:21' , '2009.01.04' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:21' , '2009.02.20' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:23' , '2009.04.06' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:23' , '2009.03.04' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:55' , '2009.05.03' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:55' , '2009.06.01' );
     
    SELECT * FROM dst;
     
    SELECT x.*, y.id
    FROM (
      SELECT location, MAX(lastUpdateDT) AS lastUpdateDT
      FROM dst
      GROUP BY location
    ) AS x
    INNER JOIN dst AS y
      ON y.location=x.location
      AND y.lastUpdateDT=x.lastUpdateDT;
    result:
    Code:
    +--------------+--------------+----+
    | location     | lastUpdateDT | id |
    +--------------+--------------+----+
    | F56:65:32:21 | 2009-02-20   |  2 | 
    | F56:65:32:23 | 2009-04-06   |  3 | 
    | F56:65:32:55 | 2009-06-01   |  6 | 
    +--------------+--------------+----+
    3 rows in set (0.01 sec)
    Last edited by tripy; Mar 4, 2009 at 16:45. Reason: :-) looks like I went the way n° 2. Nice link Rudy, thanks.

  4. #4
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So I had to scrap sorta how I was doing the original solution, which was query of queries, since I cant do any subqeries of a query of a query or CONCAT for that matter.

    But I ran into a problem with this:

    from Astro_peruser ( LEFT JOIN Astro_peruser as co2 ( LEFT OUTER JOIN ( Base JOIN Account ON Base.baseOwnerID = Account.accountID ) ON Astro_peruser.baseID = Base.baseID ) ON Astro_peruser.lastUpdatedDT > co2.lastUpdatedDT and Astro_peruser.location = co2.location )

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    remove a few of those parentheses

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tripy View Post
    Yep, you need to use a nested query, I think
    Code SQL:
    DROP TABLE IF EXISTS dst;
    CREATE TABLE dst(
      id INTEGER AUTO_INCREMENT,
      lastUpdateDT DATE,
      location VARCHAR(50),
      PRIMARY KEY(id)
    );
     
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:21' , '2009.01.04' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:21' , '2009.02.20' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:23' , '2009.04.06' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:23' , '2009.03.04' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:55' , '2009.05.03' );
    INSERT INTO dst (location, lastUpdateDT) VALUES ('F56:65:32:55' , '2009.06.01' );
     
    SELECT * FROM dst;
     
    SELECT x.*, y.id
    FROM (
      SELECT location, MAX(lastUpdateDT) AS lastUpdateDT
      FROM dst
      GROUP BY location
    ) AS x
    INNER JOIN dst AS y
      ON y.location=x.location
      AND y.lastUpdateDT=x.lastUpdateDT;
    result:
    Code:
    +--------------+--------------+----+
    | location     | lastUpdateDT | id |
    +--------------+--------------+----+
    | F56:65:32:21 | 2009-02-20   |  2 | 
    | F56:65:32:23 | 2009-04-06   |  3 | 
    | F56:65:32:55 | 2009-06-01   |  6 | 
    +--------------+--------------+----+
    3 rows in set (0.01 sec)
    I ended up doing it a little backwards since the y table in my cases needed to be the *:

    Code SQL:
    SELECT y.*
    FROM dst INNER JOIN (
      SELECT location, MAX(lastUpdateDT) AS lastUpdateDT
      FROM dst
      GROUP BY location
    ) AS x ON y.location=x.location
      AND y.lastUpdateDT=x.lastUpdateDT;

    But it worked I think, so thanks a bunch.

  7. #7
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    remove a few of those parentheses

    Yeah I forgot SQL can be finicky with extra parentheses.


Bookmarks

Posting Permissions

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