Professional Web Applications Themes

Complex SQL? - MySQL

Hi, I have a table which contains subscriptions from students, bu they are tagged with a date (so I have a kind of history). Both 'email' and 'datum' are primary keys. How can I get a full list of every subscription, but only the last of each student? My SQL knowledge is too small to solve this, I'm affraid. Any help welcome. Regards, Toni....

  1. #1

    Default Complex SQL?

    Hi,

    I have a table which contains subscriptions from students, bu they are
    tagged with a date (so I have a kind of history).
    Both 'email' and 'datum' are primary keys.

    How can I get a full list of every subscription, but only the last of each
    student?

    My SQL knowledge is too small to solve this, I'm affraid. Any help welcome.

    Regards,
    Toni.
    Toni Van Remortel Guest

  2. #2

    Default Re: Complex SQL?

    Toni Van Remortel wrote:
    > Hi,
    >
    > I have a table which contains subscriptions from students, bu they are
    > tagged with a date (so I have a kind of history).
    > Both 'email' and 'datum' are primary keys.
    >
    > How can I get a full list of every subscription, but only the last of each
    > student?
    >
    > My SQL knowledge is too small to solve this, I'm affraid. Any help welcome.
    >
    > Regards,
    > Toni.
    assuming that datum is a date using a DATE or DATETIME or TIMESTAMP type,
    then you can use the MAX function:

    select email, max(datum) from subscriptions group by email;


    If you are not using any of the above column types, you may not be able to
    do what you want.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|><
    _|
    [url]http://gmax.oltrelinux.com[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Complex SQL?

    Giuseppe Maxia wrote:
    > Toni Van Remortel wrote:
    >> Hi,
    >>
    >> I have a table which contains subscriptions from students, bu they are
    >> tagged with a date (so I have a kind of history).
    >> Both 'email' and 'datum' are primary keys.
    >>
    >> How can I get a full list of every subscription, but only the last of
    >> each student?
    >>
    >> My SQL knowledge is too small to solve this, I'm affraid. Any help
    >> welcome.
    >>
    >> Regards,
    >> Toni.
    >
    > assuming that datum is a date using a DATE or DATETIME or TIMESTAMP type,
    > then you can use the MAX function:
    >
    > select email, max(datum) from subscriptions group by email;
    Wonderfull. I was seeking for JOIN and subquery's etc.
    Seems it to be a simple GROUP BY command.

    Thank you!
    Toni Van Remortel Guest

  4. #4

    Default Re: Complex SQL?

    Giuseppe Maxia wrote:
    > Toni Van Remortel wrote:
    >> Hi,
    >>
    >> I have a table which contains subscriptions from students, bu they are
    >> tagged with a date (so I have a kind of history).
    >> Both 'email' and 'datum' are primary keys.
    >>
    >> How can I get a full list of every subscription, but only the last of
    >> each student?
    >>
    >> My SQL knowledge is too small to solve this, I'm affraid. Any help
    >> welcome.
    >>
    >> Regards,
    >> Toni.
    >
    > assuming that datum is a date using a DATE or DATETIME or TIMESTAMP type,
    > then you can use the MAX function:
    >
    > select email, max(datum) from subscriptions group by email;
    >
    >
    > If you are not using any of the above column types, you may not be able to
    > do what you want.
    Still a problem:

    SELECT email, naam, def_id, workshop_id_1, workshop_id_2, workshop_id_3,
    max( datum ) as datum FROM 2006_subscriptions GROUP BY email ORDER BY datum

    selects the max(datum) as it should, but not the values of workshop_id_X
    that belong to the max(datum) row.

    Idea?
    Toni Van Remortel Guest

  5. #5

    Default Re: Complex SQL?

    Toni Van Remortel wrote:
    [SNIP]
    >
    >
    > Still a problem:
    >
    > SELECT email, naam, def_id, workshop_id_1, workshop_id_2, workshop_id_3,
    > max( datum ) as datum FROM 2006_subscriptions GROUP BY email ORDER BY datum
    >
    > selects the max(datum) as it should, but not the values of workshop_id_X
    > that belong to the max(datum) row.
    >
    > Idea?
    Only the columns that you use in the GROUP BY clause are meaningful in
    such a query.
    If you need more columns, then you must use a subquery.

    SELECT
    email, naam, def_id,
    workshop_id_1, workshop_id_2,
    workshop_id_3, datum
    FROM
    2006_subscriptions
    WHERE
    (email, datum) IN (select email, max(datum) from 2006_subscriptions GROUP BY email)
    ORDER BY
    datum

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|><
    _|
    [url]http://gmax.oltrelinux.com[/url]
    Giuseppe Maxia Guest

  6. #6

    Default Re: Complex SQL?

    Toni Van Remortel wrote:
    > SELECT email, naam, def_id, workshop_id_1, workshop_id_2, workshop_id_3,
    > max( datum ) as datum FROM 2006_subscriptions GROUP BY email ORDER BY datum
    There's a rule about GROUP BY that you should understand. It applies to
    any database product, not just MySQL.

    Every field in your select list that is not part of an aggregate
    function (e.g. MAX, MIN, COUNT, SUM, AVG), _must_ be in the group by
    list, or else you get an ambiguous result.

    Consider the following example:

    |__A__|__B__|__C__|
    | a | 1 | x |
    | a | 1 | y |
    | a | 2 | z |
    | a | 2 | w |


    SELECT COUNT(A), B FROM MyTABLE GROUP BY B;

    Prints:

    |_COUNT_|__B__|
    | 2 | 1 |
    | 2 | 2 |

    SELECT COUNT(A), B, C FROM MyTABLE GROUP BY B;

    |_COUNT_|__B__|__C__|
    | 2 | 1 | x |
    | 2 | 2 | z |

    What happened to 'y' and 'w'? The answer is that if you list a field
    such as `C` in the select list, without also listing it in the GROUP BY
    clause, then it's ambiguous to the SQL engine which row to take the
    value of `C` from, when presenting the final result. When grouping
    solely by `B`, there are two rows in each group, but only one row in the
    result. The field returning the value of `C` can holds only one value
    per row, and your query hasn't done anything to specify which row from
    which to take that value. So the SQL engine decides for you, somewhat
    arbitrarily.

    In some RDBMS products, a query such as this that creates an ambiguous
    result set actually results in an error, and won't execute the query or
    return any result. You _must_ change the query to make it unambiguous.

    But in MySQL, the query is permitted, and it is assumed that you know
    what you are doing and you accept the ambiguous result, even though it
    has lost some information from the underlying data.

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Help! Complex query
    By RuBot in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 7th, 12:47 AM
  2. Dynamic WHERE (more complex)
    By raulriera in forum Coldfusion Database Access
    Replies: 7
    Last Post: January 10th, 11:33 PM
  3. Can Anyone help with this complex page?
    By BucksSteve in forum Macromedia Dynamic HTML
    Replies: 2
    Last Post: August 6th, 08:44 AM
  4. Complex Types in VB 6
    By Gary Dunne in forum ASP.NET Web Services
    Replies: 0
    Last Post: July 28th, 02:34 PM
  5. Complex situation
    By Mike in forum ASP Database
    Replies: 1
    Last Post: December 30th, 04:26 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