Professional Web Applications Themes

Help for quite complex query. - MySQL

Hallo, I have a quite complex query: I have a table create tabel event [...] y INT date TIMESTAMP group INT [...] I'd like to know the value of y of the row which have the MAX(date) in 'group'. something like that SELECT y FROM event WHERE date is the max of (GROUP BY group) How can I DO? THnakyou in advance, Andrea....

  1. #1

    Default Help for quite complex query.

    Hallo,
    I have a quite complex query:
    I have a table

    create tabel event
    [...]
    y INT
    date TIMESTAMP
    group INT
    [...]

    I'd like to know the value of y of the row which have the MAX(date) in
    'group'.

    something like that
    SELECT y FROM event WHERE date is the max of (GROUP BY group)

    How can I DO?
    THnakyou in advance,
    Andrea.


    _andrea.l Guest

  2. #2

    Default Re: Help for quite complex query.

    Hi!

    If you use MySQL 4.1 upwards, you can do it with

    select y from event where date = (select max(date) from event)

    Be aware that this query could return more than one row if there are 2
    events at the same time.

    With MySQL 4.0 (and before), you would have to make 2 queries to get the
    result.

    Markus


    Markus Popp Guest

  3. #3

    Default Re: Help for quite complex query.

    Markus Popp wrote:
    > Hi!
    >
    > If you use MySQL 4.1 upwards, you can do it with
    >
    select y from event where date = (select max(date) from event where
    group=somegroup)

    Is what you meant. And it will give a row per group.

    Bart
    Bart Friederichs Guest

  4. #4

    Default Re: Help for quite complex query.

    _andrea.l wrote:
    > Hallo,
    > I have a quite complex query:
    > I have a table
    >
    > create tabel event
    > [...]
    > y INT
    > date TIMESTAMP
    > group INT
    > [...]
    >
    > I'd like to know the value of y of the row which have the MAX(date) in
    > 'group'.
    >
    > something like that
    > SELECT y FROM event WHERE date is the max of (GROUP BY group)
    >
    > How can I DO?
    > THnakyou in advance,
    > Andrea.
    >
    >
    first you should rename your field group group1 ( because group is a
    reserved name)
    alter table event change 'group' 'group1' int(8);

    You could not obtain "y" witch matches max(date) in one query:
    select max(date),group1,y from event group by group1
    -> returns a random y among group1.

    the right way needs php.
    PHP code:
    <?
    include('yourconnection.php');

    //
    $qu1 = "select max(date),group1 from event group by group1";
    $res = mysql_query( $qu );

    while( $row = mysql_fetch_array( $res )){

    $qu2 = "select y,date,group1 from event where
    date=".$row['max(date)']." and group1 = ".$row['group1'];

    $res2 = mysql_query( $qu2 );

    $row2 = mysql_fetch_array( $res2 );
    //( or while $row2 = ... because $res2
    // may return more than 1 result )

    // print results:
    echo "<pre>";
    print_r( $row2 );
    echo "</pre><hr>";
    }
    ?>
    alfred Wallace Guest

Similar Threads

  1. Help! Complex query
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 7th, 12:47 AM
  2. Presenting complex query results through datagrid
    By Bijoy Naick in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: February 24th, 08:19 PM
  3. Adding/Updating records on form with complex query
    By Steve Marsden in forum Microsoft Access
    Replies: 0
    Last Post: July 28th, 10:07 AM
  4. Fairly Complex Query
    By Mike Davies in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 1st, 06:19 PM
  5. A complex query
    By Venkatesan M in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 02:27 PM

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