Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33

    Unanswered: sql select statement help :)

    I need to do the following:

    select (max(extract(hour from log_datetime)), max(extract(minute from log_datetime)),max(extract(second from log_datetime)),max(extract(day from log_datetime)),max(extract(month from log_datetime)),max(extract(year from log_datetime)) - interval 300 second) FROM table;

    What it is is extract the various components for the max datetime and do a calculation on it ??

    MANY thansk for the help

  2. #2
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Well, if I'm not mistaken, any max function has in mind "GROUP BY" on something, doesn't it?..

    Or I simply didn't understand the question?
    Yours faithfully,
    Yaroslav Zaremba

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, it's quite alright to have a max function without a GROUP BY, but in that case, all the columns in the SELECT list have to be aggregate functions, and the entire result set is considered to be one group

    tag, that query will not give you what you want

    for example, if these are datetimes in your table...

    2003-01-16 12:05:57
    2003-01-16 18:00:03
    2003-01-17 09:37:00
    2003-01-17 10:59:23

    then when you pull out the max of each of the parts, you get

    2003, 01, 17, 18, 59, 57

    what you want is to extract the parts from the max datetime, and in mysql, you have to do this in two queries

    see 3.5.2 The Row Holding the Maximum of a Certain Column

    rudy
    http://rudy.ca/

  4. #4
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Won't this work instead of using two SELECTs?

    Code:
     SELECT MAX(log_datetime), DATE_FORMAT(log_datetime, '%Y') AS Year, DATE_FORMAT(log_datetime, '%m') AS Month, DATE_FORMAT(log_datetime, '%d') AS Date, DATE_FORMAT(log_datetime, '%H') AS Hour, DATE_FORMAT(log_datetime, '%i') AS Minute, DATE_FORMAT(log_datetime, '%s') AS Second FROM table;
    Yours faithfully,
    Yaroslav Zaremba

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope

    mysql might let you say it (it's notoriously sloppy about its grouping), but if so, it will not give the right answer

    all other databases will flag that as a syntax error, because you cannot use an aggregate function like max() at the same time as selecting row-level values, which the rest of those columns are


    rudy

  6. #6
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    Ok, a bit change to the query and it works smoothly - just tested.
    No need to use two SELECTs, one is more than enough.

    Code:
    SELECT MAX(log_datetime), DATE_FORMAT(MAX(log_datetime), '%Y') AS Year, ...., DATE_FORMAT(MAX(log_datetime), '%s') AS Seconds FROM table;
    P.S.: sorry for adding that damned horizontal scrollbar to your browsers because of my code examples.
    Yours faithfully,
    Yaroslav Zaremba

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    cool

    thanks

  8. #8
    Join Date
    Nov 2001
    Location
    South Africa
    Posts
    33

    ok but ....

    ok but - how then do you do the - interval 300 second part ???

    THANKS ))))

  9. #9
    Join Date
    Sep 2002
    Location
    Kyiv, Ukraine
    Posts
    77
    If you need simply to subtract 300 seconds then changing everywhere in the SQL query variable log_datetime for (log_datetime - 300) should do it, shouldn't it?

    I'm not quite sure if I understood properly what you meant under 'interval 300 second' .....
    Yours faithfully,
    Yaroslav Zaremba

Posting Permissions

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