Thread: SQL Query Help

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0

    SQL Query Help


    I have a table similar to this:


    REC_ID PROD_ID ISSUE_NO
    ===== ======= ========
    101 120100 1
    102 120100 2
    103 120100 3
    104 120200 1
    105 120200 2
    106 120300 1

    I want to return a query that shows the latest issue_no for each product

    e.g.

    REC_ID PROD_ID ISSUE_NO
    ===== ======= ========
    103 120100 3
    105 120200 2
    103 120300 1

    I have tried select REC_ID, PROD_ID, max(ISSUE_NO) from TABLE GROUP BY PROD_ID but this gives the REC_ID of the first matching product_ID in the table rather than the one for the latest issue_NO

    e.g.
    REC_ID PROD_ID ISSUE_NO
    ===== ======= ========
    101 120100 3
    104 120200 2
    103 120300 1


    Any ideas ?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    how about something like this

    Code:
    SELECT
        REC_ID,
        PROD_ID,
        MAX(ISSUE_NO)
    FROM
        SOME_TABLE
    GROUP BY
        PROD_ID
    just tried it on my test database. works fine.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by deljr
    just tried it on my test database. works fine.
    Did you even read his post? He had the exact same query, and it didn't "work fine" for him.

    So what's wrong? The problem is that the query makes no sense. What is the PROD_ID for, say, ISSUE_NO = 1? 120100? 120200? 120300? As you can see, there is no definite value. A strict database system like PostgreSQL would throw an error and tell you exactly that.

    Unfortunately, MySQL accepts the wrong query and will basically choose an arbitrary value. Which leads leads people to believe this is actually correct SQL. It isn't. It's an invention by MySQL to -- I guess -- not frustrate newbies.

    The correct way of doing this is to first get the highest ISSUE_NO for every PROD_ID and then look up the corresponding REC_ID for this pair of (PROD_ID, ISSUE_NO). Note that there needs to be a UNIQUE constraint on (PROD_ID, ISSUE_NO) for this task to even make sense.

    Code:
    SELECT
    	REC_ID
    	, PROD_ID
    	, ISSUE_NO
    FROM
    	dev
    WHERE
    	(PROD_ID, ISSUE_NO) IN (
    		SELECT
    			PROD_ID
    			, MAX(ISSUE_NO)
    		FROM
    			dev
    		GROUP BY
    			PROD_ID
    	)
    ;
    You could also use a JOIN with a subselect. Which version is better I haven't checked.

    Comments on this post

    • shammat agrees
    Last edited by Jacques1; January 16th, 2013 at 04:01 PM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Wisconsin
    Posts
    78
    Rep Power
    3
    Originally Posted by Jacques1
    Did you even read his post? He had the exact same query, and it didn't "work fine" for him.
    I did read the post and when it worked for me I assumed that there may have been a typo in the first attempt.



    Originally Posted by Jacques1
    The correct way of doing this is to first get the highest ISSUE_NO for every PROD_ID and then look up the corresponding REC_ID for this pair of (PROD_ID, ISSUE_NO). Note that there needs to be a UNIQUE constraint on (PROD_ID, ISSUE_NO) for this task to even make sense.

    Code:
    SELECT
    	REC_ID
    	, PROD_ID
    	, ISSUE_NO
    FROM
    	dev
    WHERE
    	(PROD_ID, ISSUE_NO) IN (
    		SELECT
    			PROD_ID
    			, MAX(ISSUE_NO)
    		FROM
    			dev
    		GROUP BY
    			PROD_ID
    	)
    ;
    You could also use a JOIN with a subselect. Which version is better I haven't checked.
    and this is not PostgreSQL... MySQL will not run a query that references a table in both the main and sub queries. But your a expert and im a noob so you should know that.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Originally Posted by deljr
    MySQL will not run a query that references a table in both the main and sub queries.
    It will run it.

    Only DELETE or UPDATE statements have this stupid limitation (and then it seems to be a parser problem because technically it actually works)
    Last edited by shammat; January 17th, 2013 at 02:13 AM.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0

    Thanks


    Thanks Jacques1

    That solved my problem and your explanation is one of the clearest I have seen.



    Originally Posted by Jacques1
    Did you even read his post? He had the exact same query, and it didn't "work fine" for him.

    So what's wrong? The problem is that the query makes no sense. What is the PROD_ID for, say, ISSUE_NO = 1? 120100? 120200? 120300? As you can see, there is no definite value. A strict database system like PostgreSQL would throw an error and tell you exactly that.

    Unfortunately, MySQL accepts the wrong query and will basically choose an arbitrary value. Which leads leads people to believe this is actually correct SQL. It isn't. It's an invention by MySQL to -- I guess -- not frustrate newbies.

    The correct way of doing this is to first get the highest ISSUE_NO for every PROD_ID and then look up the corresponding REC_ID for this pair of (PROD_ID, ISSUE_NO). Note that there needs to be a UNIQUE constraint on (PROD_ID, ISSUE_NO) for this task to even make sense.

    Code:
    SELECT
    	REC_ID
    	, PROD_ID
    	, ISSUE_NO
    FROM
    	dev
    WHERE
    	(PROD_ID, ISSUE_NO) IN (
    		SELECT
    			PROD_ID
    			, MAX(ISSUE_NO)
    		FROM
    			dev
    		GROUP BY
    			PROD_ID
    	)
    ;
    You could also use a JOIN with a subselect. Which version is better I haven't checked.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0

    Better Solution


    I have found the query runs much quicker using a join.

    This code works best for me:

    Code:
    select dev.REC_ID, _d.PROD_ID, _d.ISSUE_NO
    from (SELECT PROD_ID, max(ISSUE_NO) ISSUE_NO
          from dev
          group by PROD_ID
          )
          _d
          join dev
          using (PROD_ID, ISSUE_NO)
    order by _d.PROD_ID, _d.ISSUE_NO
    Originally Posted by DHaslam
    Thanks Jacques1

    That solved my problem and your explanation is one of the clearest I have seen.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Jacques1
    Unfortunately, MySQL accepts the wrong query and will basically choose an arbitrary value. Which leads leads people to believe this is actually correct SQL. It isn't. It's an invention by MySQL to -- I guess -- not frustrate newbies.
    i totally agree that this mysql extension has pwned a disproportionate number of novice developers

    here's a good explanation of how it works and why it was designed -- Debunking GROUP BY myths

    it's a long article but very worthwhile to read it all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo