Results 1 to 13 of 13

Thread: Query Help!

  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Unanswered: Query Help!

    I have a database for keeping track of inventory and would like a saved Query that will pull the current inventory from the database.

    Right now I have a SQL statement that may make sense (improper syntax):

    Select * from inventory where date = max(date) group by item;

    The query will need to pull ONLY the most recent entries for the 45 different items in the database.

    I can make it work by imputting the time manually, but that's not what I am looking for since it will be using the saved query:

    select * from inventory where date =>'2002123114100' group by item;


    Please help!

    TIA!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you were close

    you need a correlated subquery

    Select * from inventory X
    where date =
    ( select max(date)
    from inventory
    where item = X.item )

    i suggest you not use "date" or any other reserved word as a column name

    rudy
    http://rudy.ca/

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aw, shoot, my apologies, i didn't notice that this was the mysql forum until i had posted that

    it's the correct answer for every database except mysql

    for mysql, see Rows having maximum group value in MySQL

    rudy

  4. #4
    Join Date
    Jan 2003
    Posts
    4
    awesome, thanks!!

    I'll give the temp table method a try!

    Which would you recommend the temp table or the MAX-CONCAT trick?

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

    it can be understand by anyone who comes after (including yourself, later)

    rudy

  6. #6
    Join Date
    Jan 2003
    Posts
    4
    great! Thanks for your help! I'll let you know how it works out.

  7. #7
    Join Date
    Oct 2003
    Location
    Taiwan
    Posts
    16
    Originally posted by r937
    you were close

    you need a correlated subquery

    Select * from inventory X
    where date =
    ( select max(date)
    from inventory
    where item = X.item )

    i suggest you not use "date" or any other reserved word as a column name

    rudy
    http://rudy.ca/
    Hi, r937, I saw some information about subquery in this post:

    http://www.dbforums.com/showthread.p...ight=sub+query

    Can your mysql server execute subquerys? Mine can't. And they can't, either. Someone said that mysql don't support subquery. Would you please tell me what version of mysql did you run? I'm in subquery trouble , now.
    My English is poor, so... please forgive me if I make mistakes.

  8. #8
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    Originally posted by AkiraJ

    Can your mysql server execute subquerys? Mine can't. And they can't, either. Someone said that mysql don't support subquery. Would you please tell me what version of mysql did you run? I'm in subquery trouble , now.
    Only the very latest alpha testing version of MySQL supports subqueries (v4.1.0). All the versions up to 4.0.x do not support subqueries.

    Hope this helps,

    Matt.

  9. #9
    Join Date
    Jan 2003
    Posts
    4

    Thumbs up

    wow, I remember this thread I used the temp table it worked like a charm!!

  10. #10
    Join Date
    Oct 2003
    Location
    Taiwan
    Posts
    16

    Talking

    Originally posted by Mincer
    Only the very latest alpha testing version of MySQL supports subqueries (v4.1.0). All the versions up to 4.0.x do not support subqueries.

    Hope this helps,

    Matt.
    Yes! Thank you I download mysql 4.1 alpha and tried it yesterday. But I got a problem on remote access control grant. The grant control setting of the mysql 4.1 alpha seems not the same as before. I delete all records in the table "user", but left 2 records. One is the field "Host" data= localhost and the fiend "User" data=root. The other one is the field "Host"=% and the field "User"=root. I also grant all rights and restart it. But I can't connect mysql server on anyother client PC besides from the localhost. I use the same setting on mysql 4.0 and it's worked. Do you know why?
    My English is poor, so... please forgive me if I make mistakes.

  11. #11
    Join Date
    Dec 2003
    Posts
    6

    And now for something completely different

    One way to get around lack of correlated subqueries is to do what the query optimizer tries to do. "Query flattening" is a process of converting a nested loop query, such as a correlated subquery that gets a row at a time from the outer query and processes it against the inner one, into a scan query. E.g.,

    Select * from inventory X
    where date =
    ( select max(date)
    from inventory
    where item = X.item )


    Can be rewritten as:


    Select X.col1, X.col2, X.date from inventory X, inventory maxX
    where X.date = max(maxX.date)
    and X.item = maxX.item
    group by X.col1, X.col2, X.date

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

    Re: And now for something completely different

    your WHERE condition must be given in the HAVING clause!

    select X.col1, X.col2, X.date
    from inventory X, inventory maxX
    where X.item = maxX.item
    group by X.col1, X.col2, X.date
    having X.date = max(maxX.date)

    rudy
    http://r937.com/

  13. #13
    Join Date
    Dec 2003
    Posts
    6
    thanks - you're right.


    select X.col1, X.col2, X.date
    from inventory X, inventory maxX
    group by X.col1, X.col2, X.date
    having X.date = max(maxX.date)
    Last edited by aaron_dba; 12-19-03 at 13:28.

Posting Permissions

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