Select problem, with having, I need a MySQL King

moresun · #1 (**permalink**) 01-17-05, 11:53

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');

r937 · #2 (**permalink**) 01-17-05, 15:28

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

moresun · #3 (**permalink**) 01-17-05, 19:51

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

r937 · #4 (**permalink**) 01-17-05, 20:14

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

moresun · #5 (**permalink**) 01-17-05, 21:16

I am using a MySQL 4.0.x so there are no subselects

r937 · #6 (**permalink**) 01-17-05, 21:31

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%'

Internet Services

Web Development

Digital Marketing

Consumer Tech