SQL Query Help

Dev Shed Forums Sponsor:

January 16th, 2013, 09:20 AM

DHaslam

Registered User

Join Date: Jan 2013

Posts: 3

Time spent in forums: 27 m 43 sec
Reputation 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 ?

January 16th, 2013, 02:52 PM

deljr

Contributing User

Join Date: Jun 2012

Location: Wisconsin

Posts: 78

Time spent in forums: 1 Day 11 h 51 m 6 sec
Reputation Power: 1

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.

January 16th, 2013, 03:58 PM

Jacques1

pollyanna

Join Date: Jul 2012

Location: Germany

Posts: 1,849

Time spent in forums: 1 Month 2 Weeks 1 Day 11 h 7 m 32 sec
Reputation Power: 813

Quote:

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.

January 16th, 2013, 04:07 PM

deljr

Contributing User

Join Date: Jun 2012

Location: Wisconsin

Posts: 78

Time spent in forums: 1 Day 11 h 51 m 6 sec
Reputation Power: 1

Quote:

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.

Quote:

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.

January 16th, 2013, 04:15 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,684

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 37 m 18 sec
Reputation Power: 284

Quote:

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)

__________________
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

Last edited by shammat : January 17th, 2013 at 02:13 AM.

January 17th, 2013, 02:59 AM

DHaslam

Registered User

Join Date: Jan 2013

Posts: 3

Time spent in forums: 27 m 43 sec
Reputation Power: 0

Thanks

Thanks Jacques1

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

Quote:

Originally Posted by Jacques1

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.

January 17th, 2013, 03:35 AM

DHaslam

Registered User

Join Date: Jan 2013

Posts: 3

Time spent in forums: 27 m 43 sec
Reputation 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

Quote:

Originally Posted by DHaslam

Thanks Jacques1

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

January 17th, 2013, 04:54 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,362

Time spent in forums: 3 Months 1 Week 2 Days 5 h 42 m 2 sec
Reputation Power: 4140

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Developer Shed Advertisers and Affiliates