I have two table a and b. In the table there are: unique id for the article, sref1: for the sub-reference of the article and shortname of the article. In table b, it's she who manages the completeness of the article with a history. There is a unique identifier, the completenessProgression and completenessObject_id which refers to the identifier of table a.

Last row of table b in my progress field. And in the same query it would take a sort without duplicate of the concat ("shortname", "-", "sub-reference"). So have in return for my request the for each article with its sub-reference the last progression to save.

Currently I know how to retrieve the last value of the progress for a specific article :

SELECT oo_id, completenessProgression, completenessObject__id 
FROM object_20 WHERE object_20.completenessObject__id=171423 
ORDER BY object_20.oo_id DESC LIMIT 1;

I also know how to retrieve articles by name and sub-reference1 with a GROUP BY :

SELECT object_20.oo_id AS Id, object_20.completenessObject__id AS id2,
object_20.completenessProgression AS Progress,
object_1.CSubRef1 AS Color, object_1.shortname AS Name
FROM object_20 INNER JOIN object_1
ON object_1.oo_id = object_20.completenessObject__id
WHERE object_20.completenessProgression >= 80
GROUP BY CONCAT(object_1.CSubRef1, ' ',object_1.shortname);

But when I add in my where clause a completenessProgression> 80. It does not take the last values ​​of my completeness for each article by name and under reference.

I have tested with various sub-request but I cannot get something correct. I can only use queries, no script, no trigger.

http://sqlfiddle.com/#!9/9a51d/1

 ------------------------------------ 
| Results Completeness > 80          |
 ----- ---------- -------- ---------- 
| Id  | Name     | Color  | Progress |
 ----- ---------- -------- ---------- 
| 2   | Armand   | Jaune  | 98       |
 ----- ---------- -------- ---------- 
| 4   | Herbert  | Bleu   | 100      |
 ----- ---------- -------- ---------- 

CodePudding user response：

Iy the numbers for progrssion are unique, what i doubt, you could use a sbquery

But a better solution is the second, with a window function, because if you use the second and you have more than one, you need a similar solution

SELECT o20.*,o1.CSubRef1 ,  o1.shortname 
FROM object_20 o20 INNER JOIN object_1 o1 ON o1.oo_id = o20.completenessObject__id
INNER JOIN (SELECT MAX(completenessProgression) maxreg, shortname
FROM object_20 o20 INNER JOIN object_1 o1 ON o1.oo_id = o20.completenessObject__id
GROUP BY shortname) t1 On t1.shortname = o1.shortname  AND  t1.maxreg = o20.completenessProgression
WHERE o20.completenessProgression > 80

oo_id | completenessProgression | completenessObject__id | CSubRef1 | shortname
----: | ----------------------: | ---------------------: | :------- | :--------
    6 |                      98 |                      2 | Jaune    | Armand   
    7 |                     100 |                      4 | Bleu     | Hebert   

SELECT 
    oo_id,CSubRef1,  shortname,completenessProgression
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY shortname ORDER BY  completenessProgression DESC) rn
,o20.oo_id,CSubRef1,  shortname,completenessProgression
FROM object_20 o20 INNER JOIN object_1 o1 ON o1.oo_id = o20.completenessObject__id) t2
WHERE rn = 1

oo_id | CSubRef1 | shortname | completenessProgression
----: | :------- | :-------- | ----------------------:
    6 | Jaune    | Armand    |                      98
    7 | Bleu     | Hebert    |                     100

db<>fiddle here

CodePudding user response：

The problem is that in my case I have a sub-reference 2 which is the size and I don't want this value because the progression is similar from one sub-reference 2 to another.

And the problem is there, I have as many lines as there are sizes while I only want one line per CSubRef1 and not one line per CSubRef2

If I'm not clear, I can make a screen of my current output. that's why in my case I made a:

GROUP BY CONCAT(object_1.CSubRef1, ' ',object_1.shortname);

but it doesn't work the way i would like