Professional Web Applications Themes

Group By problem - MySQL

Hello, I have a problem of grouping data in a table Here's my table mysql> select * from url_histo; +----+---------------------+------------+--------+ | id | timestamp | retourhttp | id_url | +----+---------------------+------------+--------+ | 7 | 2006-05-04 12:37:48 | 200 | 1 | | 8 | 2006-05-04 12:37:50 | 200 | 1 | | 9 | 2006-05-04 14:47:47 | 200 | 2 | | 10 | 2006-05-04 14:47:56 | 201 | 2 | | 11 | 2006-05-04 14:48:01 | 201 | 1 | +----+---------------------+------------+--------+ I want to have a query that return | 10 | 2006-05-04 14:47:56 | 201 | 2 | | ...

  1. #1

    Default Group By problem

    Hello,

    I have a problem of grouping data in a table



    Here's my table

    mysql> select * from url_histo;
    +----+---------------------+------------+--------+
    | id | timestamp | retourhttp | id_url |
    +----+---------------------+------------+--------+
    | 7 | 2006-05-04 12:37:48 | 200 | 1 |
    | 8 | 2006-05-04 12:37:50 | 200 | 1 |
    | 9 | 2006-05-04 14:47:47 | 200 | 2 |
    | 10 | 2006-05-04 14:47:56 | 201 | 2 |
    | 11 | 2006-05-04 14:48:01 | 201 | 1 |
    +----+---------------------+------------+--------+

    I want to have a query that return

    | 10 | 2006-05-04 14:47:56 | 201 | 2 |
    | 11 | 2006-05-04 14:48:01 | 201 | 1 |


    This query should return the last (temporarly speaking) data of each
    id_url. There is a field timestamp, it should be easy but I am
    completely lost with "Group by"

    mysql> select id,id_url,retourhttp,max(timestamp) from url_histo group
    by id_url;

    +----+--------+------------+---------------------+
    | id | id_url | retourhttp | max(timestamp) |
    +----+--------+------------+---------------------+
    | 7 | 1 | 200 | 2006-05-04 14:48:01 |
    | 9 | 2 | 200 | 2006-05-04 14:47:56 |
    +----+--------+------------+---------------------+


    The result is a mix of lines... I do not understand the way to do it



    If anyone has a clue... thanks in advance

    Dim Guest

  2. #2

    Default Re: Group By problem

    Dim wrote:
    > Here's my table
    >
    > mysql> select * from url_histo;
    > +----+---------------------+------------+--------+
    > | id | timestamp | retourhttp | id_url |
    > +----+---------------------+------------+--------+
    > | 7 | 2006-05-04 12:37:48 | 200 | 1 |
    > | 8 | 2006-05-04 12:37:50 | 200 | 1 |
    > | 9 | 2006-05-04 14:47:47 | 200 | 2 |
    > | 10 | 2006-05-04 14:47:56 | 201 | 2 |
    > | 11 | 2006-05-04 14:48:01 | 201 | 1 |
    > +----+---------------------+------------+--------+
    >
    > I want to have a query that return
    >
    > | 10 | 2006-05-04 14:47:56 | 201 | 2 |
    > | 11 | 2006-05-04 14:48:01 | 201 | 1 |
    >
    >
    > This query should return the last (temporarly speaking) data of each
    > id_url. There is a field timestamp, it should be easy but I am
    > completely lost with "Group by"
    >
    > mysql> select id,id_url,retourhttp,max(timestamp) from url_histo group
    > by id_url;
    >
    > +----+--------+------------+---------------------+
    > | id | id_url | retourhttp | max(timestamp) |
    > +----+--------+------------+---------------------+
    > | 7 | 1 | 200 | 2006-05-04 14:48:01 |
    > | 9 | 2 | 200 | 2006-05-04 14:47:56 |
    > +----+--------+------------+---------------------+
    This gives you the maximal timestamp for each id_url. All other fields
    (those you do not mention in the GROUP BY) give you an arbitrary result.
    > The result is a mix of lines... I do not understand the way to do it
    You are looking for the rows holding the group-wise maximum, which is a
    separate Chapter in the fine manual:

    [url]http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html[/url]

    Greetings
    Kai

    --
    This signature is left as an exercise for the reader.
    Kai Ruhnau Guest

  3. #3

    Default Re: Group By problem

    So...

    SELECT *
    FROM url_histo u1
    WHERE timestamp = (
    SELECT MAX( timestamp )
    FROM url_histo
    WHERE u1.id_url = id_url )

    strawberry Guest

  4. #4

    Default group by problem

    Hi all, on a query I've this result:
    69 86
    77 84
    112 91
    114 91
    119 91
    124 92
    125 92
    133 93
    ....

    first is the idfolder, second is the idclient.

    Now When I do a group by idfolder, I've a total of 77 rows (see the result
    above for the first part). If I do a group by idclient, then I only get 3
    results

    Why ? Have you an idea ?

    Here is the query:
    select folder.idfolder, folder.idclient from folderstatus
    inner join statustype on folderstatus.idstatustype=statustype.idstatus
    inner join folder on folderstatus.iddossier = folder.idfolder
    group by folder.idfolder
    having max(idstatustype) = (select idstatus from statustype where
    statustype.name like '%search%' limit 0,1)

    if I do a group by folder.idclient, then I only get 3 rows instead of about
    50.

    Bob



    Bob Guest

  5. #5

    Default Re: group by problem

    On 20 Mar, 15:38, "Bob Bedford" <com> wrote: 

    Not easy to tell if we don't know what data is in he tables.

    Captain Guest

  6. #6

    Default Re: group by problem

    > Not easy to tell if we don't know what data is in he tables.

    I can't export datas as they are confidential.
    anyway the result are strange when I group by a field like it doesn't take
    all results.
    Is the problem from "having" ?



    Bob Guest

  7. #7

    Default Re: group by problem

    On Mar 20, 4:01 pm, "Bob Bedford" <com> wrote: 
    >
    > I can't export datas as they are confidential.
    > anyway the result are strange when I group by a field like it doesn't take
    > all results.
    > Is the problem from "having" ?[/ref]

    Why not present some dummy data (5 - 10 rows, say) together with the
    results you'd expect from that data. Then we'd have a chance, (but
    don't make it too easy!)

    strawberry Guest

  8. #8

    Default Re: group by problem

    On Tue, 20 Mar 2007 16:38:36 +0100, Bob Bedford wrote: 

    You haven't told us anything about your statustype table, its contents,
    etc, yet, and you're selecting only SOME of the records, based on that.
    How many, we certainly cannot say.

    --
    Cunningham's Second Law:
    It's always more complex than you expect, even when you take
    Cunningham's Second Law into account.
    Peter Guest

  9. #9

    Default Re: group by problem

    as a general request, here is the script:
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for client
    -- ----------------------------
    CREATE TABLE `client` (
    `idclient` mediumint(8) unsigned NOT NULL auto_increment,
    `clientname` varchar(50) NOT NULL default '',
    PRIMARY KEY (`idclient`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    -- ----------------------------
    -- Table structure for folder
    -- ----------------------------
    CREATE TABLE `folder` (
    `idfolder` mediumint(8) unsigned NOT NULL auto_increment,
    `datefolder` date default NULL,
    `idperson` mediumint(8) unsigned NOT NULL default '0',
    `idclient` mediumint(9) NOT NULL default '0',
    PRIMARY KEY (`idfolder`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    -- ----------------------------
    -- Table structure for folderstatus
    -- ----------------------------
    CREATE TABLE `folderstatus` (
    `idfolderstatus` mediumint(8) unsigned NOT NULL auto_increment,
    `idfolder` mediumint(8) unsigned NOT NULL default '0',
    `idtypestatus` mediumint(8) unsigned NOT NULL default '0',
    PRIMARY KEY (`idfolderstatus`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    -- ----------------------------
    -- Table structure for person
    -- ----------------------------
    CREATE TABLE `person` (
    `idperson` mediumint(8) unsigned NOT NULL auto_increment,
    `personname` varchar(50) NOT NULL default '',
    PRIMARY KEY (`idperson`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    -- ----------------------------
    -- Table structure for typestatus
    -- ----------------------------
    CREATE TABLE `typestatus` (
    `idtypestatus` mediumint(8) unsigned NOT NULL auto_increment,
    `typestatus` varchar(50) NOT NULL default '',
    `idcheck` tinyint(1) unsigned NOT NULL default '0',
    PRIMARY KEY (`idtypestatus`)
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    -- ----------------------------
    -- Records
    -- ----------------------------
    INSERT INTO `client` VALUES ('1', 'client1');
    INSERT INTO `client` VALUES ('2', 'client2');
    INSERT INTO `client` VALUES ('3', 'client3');
    INSERT INTO `client` VALUES ('4', 'client4');
    INSERT INTO `client` VALUES ('5', 'client5');
    INSERT INTO `folder` VALUES ('1', '2006-01-01', '1', '1');
    INSERT INTO `folder` VALUES ('2', '2006-02-05', '1', '3');
    INSERT INTO `folder` VALUES ('3', '2006-03-08', '2', '1');
    INSERT INTO `folder` VALUES ('4', '2006-03-09', '2', '2');
    INSERT INTO `folder` VALUES ('5', '2006-05-05', '2', '3');
    INSERT INTO `folder` VALUES ('6', '2006-12-08', '3', '2');
    INSERT INTO `folderstatus` VALUES ('1', '1', '1');
    INSERT INTO `folderstatus` VALUES ('2', '1', '2');
    INSERT INTO `folderstatus` VALUES ('3', '1', '3');
    INSERT INTO `folderstatus` VALUES ('4', '2', '1');
    INSERT INTO `folderstatus` VALUES ('5', '2', '3');
    INSERT INTO `folderstatus` VALUES ('6', '2', '4');
    INSERT INTO `folderstatus` VALUES ('7', '3', '4');
    INSERT INTO `folderstatus` VALUES ('8', '3', '1');
    INSERT INTO `folderstatus` VALUES ('9', '4', '1');
    INSERT INTO `folderstatus` VALUES ('10', '4', '4');
    INSERT INTO `folderstatus` VALUES ('11', '4', '3');
    INSERT INTO `folderstatus` VALUES ('12', '1', '3');
    INSERT INTO `folderstatus` VALUES ('13', '1', '3');
    INSERT INTO `person` VALUES ('1', 'aaaaa');
    INSERT INTO `person` VALUES ('2', 'bbbbbb');
    INSERT INTO `person` VALUES ('3', 'cccccc');
    INSERT INTO `person` VALUES ('4', 'ddddd');
    INSERT INTO `person` VALUES ('5', 'eeeeee');
    INSERT INTO `person` VALUES ('6', 'ffffff');
    INSERT INTO `person` VALUES ('7', 'gggggg');
    INSERT INTO `typestatus` VALUES ('1', 'status1', '1');
    INSERT INTO `typestatus` VALUES ('2', 'status2', '1');
    INSERT INTO `typestatus` VALUES ('3', 'status3', '1');
    INSERT INTO `typestatus` VALUES ('4', 'status4', '0');
    INSERT INTO `typestatus` VALUES ('5', 'status5', '1');

    The query (seems to work)
    select person.idperson,
    folder.datefolder, folder.idclient, folder.idfolder
    from folderstatus
    inner join typestatus on folderstatus.idtypestatus=typestatus.idtypestatus
    inner join folder on folderstatus.idfolder = folder.idfolder
    inner join person on folder.idperson = person.idperson
    inner join client on folder.idclient = client.idclient
    group by folder.idfolder
    having (max(folderstatus.idtypestatus) = (select idtypestatus from
    typestatus where typestatus.typestatus like '%4%' limit 0,1)
    and folder.datefolder between '2006-01-01' and '2007-01-01')

    But what I don't know how to do now:
    I'd like all the records from folderstatus where last (I mean bigger
    idfolderstatus) for every folder linked to the typestatus has a
    typestatus.idcheck to 0.

    Let's explain again in pseudo-code:
    select * from folderstatus inner join typestatus where folderstatus =
    max(folderstatus for this folder) and folderstatus.idcheck = 0
    So if max(folderstatus) = 4 in this case, then return idfolder (as idcheck
    in typestatus = 0) else don't return the row.
    How to do this ?

    Thanks for help.

    Bob



    Bob Guest

  10. #10

    Default Re: group by problem

    On Mar 20, 7:43 pm, "Bob Bedford" <com> wrote: 

    Have`you tried looking at the ubiquitous "strawberry query" in this
    and other newsgroups?

    strawberry Guest

  11. #11

    Default Re: group by problem

    tried this for my other post, but return 0 values:

    select person.idperson, folderstatus.idtypestatus,typestatus.idcheck,
    folder.datefolder, folder.idclient, folder.idfolder
    from typestatus
    inner join folderstatus on typestatus.idtypestatus =
    folderstatus.idtypestatus
    inner join folder on folderstatus.idfolder = folder.idfolder
    inner join person on folder.idperson = person.idperson
    inner join client on folder.idclient = client.idclient
    group by folder.idfolder
    having (folderstatus.idtypestatus = max(folderstatus.idtypestatus) and
    typestatus.idcheck = 0)



    Bob Guest

  12. #12

    Default Re: group by problem

    Can't find in this NG. Google didn't help much.



    Bob Guest

  13. #13

    Default Re: group by problem

    add this to the script:
    INSERT INTO `folderstatus` VALUES ('14', '3', '5');
    update `typestatus` set idcheck = 0 where idtypestatus = 5;

    Now the query:
    select folder.idperson, folderstatus.idtypestatus,typestatus.idcheck,
    folderstatus.idfolderstatus,
    folder.datefolder, folder.idfolder
    from typestatus
    inner join folderstatus on typestatus.idtypestatus =
    folderstatus.idtypestatus
    inner join folder on folderstatus.idfolder = folder.idfolder
    inner join person on folder.idperson = person.idperson
    inner join client on folder.idclient = client.idclient
    where typestatus.idcheck = 0
    group by idtypestatus, idfolder
    having (idfolderstatus = max(folderstatus.idfolderstatus))

    I've 4 results but IdFolder 3 is shown twice and I only want the bigger
    idfolderstatus for every folder.

    How to do so ? I seem close to the final result, I've tried to group by
    different manners but no way. Something's wrong and I'm not a MySQL gourou,
    so please help me.

    Bob



    Bob Guest

  14. #14

    Default Re: group by problem

    On Mar 20, 8:28 pm, "Bob Bedford" <com> wrote: 
    Well, you must be using a different version of google! I get 66 hits
    in this ng!

    Anyway, here's an example of what I'm talking about. Incidentally,
    this is an example of a groupwise max problem.
    You may find it helpful to include fs2.* in the SELECT part of the
    query in order to better understand what's going on.
    I've tried to use your naming convention but I probably ed up
    somewhere. The GROUP BY part of this statement is a bit of a
    nonsense. It just says 'In the event of a tie, pick one. I don't care
    which.' In reality you'd probably want to handle ties a little more
    thoughtfully:

    SELECT fs1 . *
    FROM `folderstatus` fs1
    LEFT JOIN folderstatus fs2 ON fs2.idfolderstatus <> fs1.idfolderstatus
    AND fs1.idfolder = fs2.idfolder
    AND fs2.idtypestatus > fs1.idtypestatus
    WHERE isnull( fs2.idtypestatus )
    GROUP BY fs1.idfolder
    ORDER BY fs1.idfolder

    strawberry Guest

  15. #15

    Default Re: group by problem

    > Anyway, here's an example of what I'm talking about. Incidentally, 

    Great it works !

    Now the pain will be to transpose to my DB with at least 20 linked
    table...but I got the idea.

    Thanks for your help.

    Bob



    Bob Guest

Similar Threads

  1. Replies: 0
    Last Post: January 4th, 06:35 PM
  2. Sum and Group problem with MSSQL
    By zu in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 2nd, 04:11 PM
  3. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  4. problem getting User Group Memebership in Active Directory
    By Patrick Olurotimi Ige in forum ASP.NET Security
    Replies: 2
    Last Post: May 10th, 12:49 AM
  5. group by problem on form
    By kderaedt in forum Microsoft Access
    Replies: 1
    Last Post: July 8th, 07:55 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139