Jump to content

Using Max not giving the data i expect


Adamhumbug

Recommended Posts

I have the below query:

SELECT 
  max(quote.id) as quoteId, 
  job_id as jobId, 
  job.name as jobName, 
  job.client_id as clientId, 
  client.name as clientName, 
  currency, 
	version 
from quote
inner join job on job.id = quote.job_id
inner join client on client.id = job.client_id
where quote.closed != '1'
group by job_id

i have attached an image of the data structure

2105224186_Screenshot2022-06-21at16_44_10.png.7d3a1ab42afe9f1c3bc712a860732385.png

when i run the qry i am getting

quoteId	jobId	jobName	clientId	clientName	currency	version 	
13 	21 	Test Job 	12 	Test Client 	GBP 	1
14 	22 	JOB JOB 	3 	Testing LTD 	USD 	1
19 	24 	Adams Job 	13 	Adams Co 	GBP 	1

but i would be expecting the version numbers of the quote ids selected to be 8, 1 and 4.

Could anyone point me at what i am doing wrong with my sql?

 

Edited by Adamhumbug
Link to comment
Share on other sites

this is a common activity, i.e. getting the row of data in each group matching a specific condition. see - https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html 

not listed in that information is adding a term to the WHERE clause with id IN(a sub-query that gets the max(id) per group) (essentially what your query would be if it was just getting the max quote id per job_id group.) i cannot vouch for the efficiency of this method, but it is fairly easy to understand when you write/read it.

 

Link to comment
Share on other sites

You are grouping by jobId so you will get one row for each job. The max(quoteId) should be OK (as that is a aggregation) and also jobName and clientId from the job record, but the remaining values (like version and currency) could be arbitrarily selected from from any quote record associated with jobId.

  • Great Answer 1
Link to comment
Share on other sites

9 minutes ago, Barand said:

You are grouping by jobId so you will get one row for each job. The max(quoteId) should be OK (as that is a aggregation) and also jobName and clientId from the job record, but the remaining values (like version and currency) could be arbitrarily selected from from any quote record associated with jobId.

In addition to Barand's comment, what you can do is inner join the result back to quote by quote.id in order to pick up the version, as well as any other data specific to the max quote row of the group.  

Link to comment
Share on other sites

or something like this to find the max quoteIds to be matched

SELECT 
  mx.quoteId, 
  job_id as jobId, 
  job.name as jobName, 
  job.client_id as clientId, 
  client.name as clientName, 
  quote.currency, 
  quote.version 
from job
     inner join client on client.id = job.client_id
     inner join (
                    select job_id
                         , max(id) as quoteId
                    from quote
                    where quote.closed != '1'
                    group by job_id
                ) mx using (job_id)
     inner join quote on mx.quoteId = quote.id
group by job_id

 

Link to comment
Share on other sites

On 6/21/2022 at 6:43 PM, Barand said:

or something like this to find the max quoteIds to be matched

SELECT 
  mx.quoteId, 
  job_id as jobId, 
  job.name as jobName, 
  job.client_id as clientId, 
  client.name as clientName, 
  quote.currency, 
  quote.version 
from job
     inner join client on client.id = job.client_id
     inner join (
                    select job_id
                         , max(id) as quoteId
                    from quote
                    where quote.closed != '1'
                    group by job_id
                ) mx using (job_id)
     inner join quote on mx.quoteId = quote.id
group by job_id

 

ok this is interesting.

On first try i am getting "Unknown column 'job_id' in 'from clause'" but there isnt a job_id in the from clause?

Link to comment
Share on other sites

using the IN(sub-query) method. untested, should work -

SELECT
	q.id as quoteId,
	q.job_id as jobId,
	j.name as jobName,
	j.client_id as clientId,
	c.name as clientName,
	q.currency,
	q.version
 FROM quote q
 JOIN job j ON j.id = q.job_id
 JOIN client c ON c.id = j.client_id
 WHERE q.closed != 1 AND q.id IN(SELECT MAX(id) FROM quote GROUP BY job_id)

in writing the above, i realize that your quote table contains redundant data in that it has the client_id in it. the reason you are joining the client table to the job table is because the client is related to the job, not to the quote.

if you post your table definitions, just post an .sql dump of your database so that someone could help by actually testing the query

Link to comment
Share on other sites

here's a correction to the above, move the closed != 1 codition to the sub-query -

SELECT
	q.id as quoteId,
	q.job_id as jobId,
	j.name as jobName,
	j.client_id as clientId,
	c.name as clientName,
	q.currency,
	q.version
 FROM quote q
 JOIN job j ON j.id = q.job_id
 JOIN client c ON c.id = j.client_id
 WHERE q.id IN(SELECT MAX(id) FROM quote WHERE closed != 1 GROUP BY job_id)

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.