MySql:获取最新记录然后按字母顺序分组

Question

我有以下查询:

SELECT * FROM form_advertisers fa
INNER JOIN form_settings fs USING(form_id)
WHERE fa.advertiser_id = $adv_id
GROUP BY fs.ukip_title_id
ORDER BY fs.form_title ASC, fs.form_id DESC

简化:

SELECT form_id, form_title FROM form_advertisers
WHERE advertiser_id = 135
GROUP BY ukip_title_id
ORDER BY form_title ASC, form_id DESC

但这只是按 form_title 对结果集进行排序然后form_id ，而我需要它来找到最新的 form_id对于每个 $adv_id 然后按字母顺序排列它们。

我读到了关键字 HAVING ，还有(看起来像)SELECT里面 SELECT ，但这确实很令人困惑。任何人都可以让我的查询正常工作，甚至可能抽出时间来解释它在做什么吗？

结果(按字母顺序排列，但不是最新的 form_id):

form_id   form_title
4         Automotive Testing
756       Electric & Hybrid Marine
22        Electric & Hybrid Vehicle
11        Engine
21        European Automotive
721       Magnetics
24        Professional MotorSport
220       Transmission

建议结果( form_id DESC 在 form_title ASC 之前)(仍然不正确，因为仅对输出进行排序):

form_id   form_title
756       Electric & Hybrid Marine
721       Magnetics
220       Transmission
24        Professional MotorSport
22        Electric & Hybrid Vehicle
21        European Automotive
11        Engine
4         Automotive Testing

预期结果(最新 form_id ):

form_id   form_title
610       Automotive Testing
756       Electric & Hybrid Marine
940       Electric & Hybrid Vehicle
912       Engine
132       European Automotive
720       Magnetics
780       Professional MotorSport
332       Transmission

完整数据:

4   Automotive Testing International
11  Engine Technology International
22  Electric & Hybrid Vehicle Technology International
21  European Automotive Components
24  Professional Motorsport World
25  Automotive Testing International
43  Engine Technology International
57  Engine Technology International
62  European Automotive Components
78  European Automotive Components
82  Engine Technology International
92  Electric & Hybrid Vehicle Technology International...
106 Engine Technology International
109 Professional Motorsport World
118 Engine Technology International
132 European Automotive Components
144 Electric & Hybrid Vehicle Technology International...
218 Electric & Hybrid Vehicle Technology International
220 Transmission Technology International
226 Engine Technology International
252 Engine Technology International
278 Engine Technology International
284 Electric & Hybrid Vehicle Technology International
330 Engine Technology International
332 Transmission Technology International
358 Electric & Hybrid Vehicle Technology International
392 Engine Technology International
428 Engine Technology International
434 Professional Motorsport World
458 Engine Technology International
470 Electric & Hybrid Vehicle Technology International
570 Electric & Hybrid Vehicle Technology International
584 Engine Technology International
610 Automotive Testing International
618 Engine Technology International
638 Electric & Hybrid Vehicle Technology International
720 Magnetics Technology International
752 Electric & Hybrid Vehicle Technology International
764 Engine Technology International
780 Professional Motorsport World
756 Electric & Hybrid Marine Technology International
800 Engine Technology International
820 Electric & Hybrid Vehicle Technology International
852 Engine Technology International
912 Engine Technology International
940 Electric & Hybrid Vehicle Technology International

Answer 1

试试这个......让我知道它是否有效:

SELECT * FROM( 
    SELECT form_id as form_id,
        mag_logo_url, 
        cover_date, 
        fs.ukip_title_id as title_id, 
        fs.form_title as form_title 
    FROM form_advertisers fa 
    INNER JOIN form_settings fs 
    USING ( form_id ) 
    WHERE fa.advertiser_id =135 
    ORDER BY fs.form_id DESC 
)as temp 
GROUP BY title_id 
ORDER BY form_title ASC , form_id DESC