带有order by和limit的sql组查询

My table strucutre is as follows

我的表结构如下

| id   | cmp      | empid | empname | ttm  
+------+----------+-------+---------+------
|    2 | xyz      | 12    | swap    |    2 
|    2 | xyz      | 12    | sag     |    3
|    2 | xyz      | 14    | azr     |    1 
|    3 | pqr      | 2     | ron     |    2 
|    3 | pqr      | 22    | rah     |    1 
|    3 | pqr      | 32    | pra     |    5

I have done query on that like as follows

我已经对此进行了如下查询

(select * from test.companies where id = '2' order by ttm desc limit 2)
union
(select * from test.companies where id = '3' order by ttm desc limit 2);

it will give output as follows

它将给出如下输出

| id   | cmp      | empid | empname | ttm  
+------+----------+-------+---------+------
|    2 | wissen   | 12    | sag     |    3
|    2 | wissen   | 12    | swap    |    2 
|    3 | prolinkd | 32    | pra     |    5
|    3 | prolinkd | 2     | ron     |    2

but i want output as follows

但我想要输出如下

| id   | cmp      | empid | empname | ttm  
+------+----------+-------+---------+------
|    3 | prolinkd | 32    | pra     |    5
|    3 | prolinkd | 2     | ron     |    2
|    2 | wissen   | 12    | sag     |    3 
|    2 | wissen   | 12    | swap    |    2

means which company has maximum ttm it will be shown first

意味着哪个公司有最大ttm它将首先显示

If some one knows plz reply

如果有人知道plz回复

Thanks

谢谢

3 个解决方案

#1

Rather ugly looking, and ugly sounding, but it should do what you want. It retrieves the "maxttm" for each company with each record, then uses that in sorting to allow you to assign priority to companies based on their highest ttm.

相当丑陋的外观和丑陋的声音，但它应该做你想要的。它为每个公司检索每个记录的“maxttm”，然后在排序中使用它来允许您根据公司的最高ttm为公司分配优先级。

SELECT * FROM (
    (SELECT *,
        (SELECT ttm FROM test.companies ic WHERE ic.id = oc.id ORDER BY ttm DESC LIMIT 1) AS maxttm
        FROM test.companies oc WHERE id = '2' ORDER BY ttm DESC LIMIT 2)
    UNION
    (SELECT *,
        (SELECT ttm FROM test.companies ic WHERE ic.id = oc.id ORDER BY ttm DESC LIMIT 1) AS maxttm
        FROM test.companies oc WHERE id = '3' ORDER BY ttm DESC LIMIT 2)
) AS myunion ORDER BY maxttm, id;

PS. thanks to inti whose query I shamelessly based this on.

PS。感谢inti的查询我无耻地基于此。

#2

I think this will work:

我认为这会奏效：

SELECT * FROM (

    (select * from test.companies where id = '2' order by ttm desc limit 2)
    union
    (select * from test.companies where id = '3' order by ttm desc limit 2)

) as myunion ORDER BY ttm;

gets what you need, and orders it by ttm.

得到你需要的，并通过ttm订购。

#3

select * 
from test.companies 
where id in ('2','3') 
and ttm > 1
order by ttm desc, id

#1

SELECT * FROM (
    (SELECT *,
        (SELECT ttm FROM test.companies ic WHERE ic.id = oc.id ORDER BY ttm DESC LIMIT 1) AS maxttm
        FROM test.companies oc WHERE id = '2' ORDER BY ttm DESC LIMIT 2)
    UNION
    (SELECT *,
        (SELECT ttm FROM test.companies ic WHERE ic.id = oc.id ORDER BY ttm DESC LIMIT 1) AS maxttm
        FROM test.companies oc WHERE id = '3' ORDER BY ttm DESC LIMIT 2)
) AS myunion ORDER BY maxttm, id;