Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Location
    Vienna, Austria
    Posts
    3

    Unanswered: Select problem, with having, I need a MySQL King

    Hi everybody!

    I whant to get from the following table this result with one select with a MySQL 4.0.x Database.

    id, belongs_to, version, title
    5, 1, 1, '1 Task changed version 1'
    8, 2, 3, '2 Task changed version 3'
    3, 3, 0, '3 Task'
    9, 4, 1, '4 Task changed version 1'

    I tried with SELECT * from task group by belongs_to; but it returns only the task with version 0.

    Does somebody know how I can solve that problem?
    There are no subselects allowed and I can not change the mysql version.

    Thanks
    Markus



    DROP TABLE IF EXISTS task;
    CREATE TABLE task (
    id integer(14) NOT NULL auto_increment,
    belongs_to integer(14) NOT NULL,
    version integer(4) NOT NULL,
    title varchar(250) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(belongs_to, version)
    );

    INSERT INTO task (id, belongs_to, version, title) VALUES (1, 1, 0, '1 Task');
    INSERT INTO task (id, belongs_to, version, title) VALUES (2, 2, 0, '2 Task');
    INSERT INTO task (id, belongs_to, version, title) VALUES (3, 3, 0, '3 Task');
    INSERT INTO task (id, belongs_to, version, title) VALUES (4, 4, 0, '4 Task');
    INSERT INTO task (id, belongs_to, version, title) VALUES (5, 1, 1, '1 Task changed version 1');
    INSERT INTO task (id, belongs_to, version, title) VALUES (6, 2, 1, '2 Task changed version 1');
    INSERT INTO task (id, belongs_to, version, title) VALUES (7, 2, 2, '2 Task changed version 2');
    INSERT INTO task (id, belongs_to, version, title) VALUES (8, 2, 3, '2 Task changed version 3');
    INSERT INTO task (id, belongs_to, version, title) VALUES (9, 4, 1, '4 Task changed version 1');

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select t1.id
         , t1.belongs_to
         , t1.version
         , t1.title
      from task as t1
    inner
      join task as t2
        on t1.belongs_to
         = t2.belongs_to
    group
        by t1.id
         , t1.belongs_to
         , t1.version
         , t1.title
    having t1.version
         = max(t2.version)
    and i think you could probably drop the id column altogether
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Location
    Vienna, Austria
    Posts
    3
    Thanks but I have one more question
    If i want now the count of this taks with a[CODE] where like
    Code:
    select t1.id
         , t1.belongs_to
         , t1.version
         , t1.title
      from task as t1
    inner
      join task as t2
        on t1.belongs_to
         = t2.belongs_to
    WHERE t1.title like '%3%'
    group
        by t1.id
         , t1.belongs_to
         , t1.version
         , t1.title
    having t1.version
         = max(t2.version)
    it will return me something like
    Code:
    id, | belongs_to,  | version,  | title
     3, | 3,           |0,         | '3 Task'
     8, | 2,           |3,         | '2 Task changed version 3'
    and my select count(*) ... should return 2
    but I can not use count(*) in here becouse it would count the elements in the groub by.

    Hi :-)
    Markus

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if i understand what you are trying to count,
    Code:
    select count(*)
      from (
           select t1.id
                , t1.belongs_to
                , t1.version
                , t1.title
             from task as t1
           inner
             join task as t2
               on t1.belongs_to
                = t2.belongs_to
            WHERE t1.title like '%3%'
           group
               by t1.id
                , t1.belongs_to
                , t1.version
                , t1.title
           having t1.version
                = max(t2.version)  
           ) as dt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Location
    Vienna, Austria
    Posts
    3
    I am using a MySQL 4.0.x so there are no subselects

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh yes, you did say that, and i overlooked it

    well, you could take another approach

    you want the latest row for each belongs_to, right? but then you just want to count them?

    this will give you the same answer --
    Code:
    select count(distinct belongs_to) as cnt
      from task  
     where title like '%3%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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