Using Max not giving the data i expect

[Adamhumbug]

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

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 June 21, 2022 by Adamhumbug

[mac_gyver]

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.

 

[Barand]

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.

[gizmola]

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.  

[Barand]

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

 

[Adamhumbug]

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?

[Barand]

Sorry, but there was guesswork involved as I don't know all the table structures - you only gave os the quote table..

[mac_gyver]

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

[mac_gyver]

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)