Databases
StackOverflow2013
Postsdbo
POMySQL: Select all twos of a kind with highest ids
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL: Select all twos of a kind with highest ids
    primarykey
    data
    text
    <p>I have table that consists multiple rows of a kind that have different ids. (Kinds are many. Ids are unique. Both columns are indexed.) Now I need to select the two with highest ids of each kind. Here is what I do.</p> <pre><code>select max(c.id), max(d.id) from theTable c left join theTable d on c.id &gt; d.id and c.kind=d.kind where c.id &gt; constant group by c.kind; </code></pre> <p>However the query above doesnt perform very well and it is not a big surprise. Ive figured out a faster version of it... </p> <pre><code>select c.id, max(d.id) from (select max(id) id, kind from theTable where id &gt; constatnt group by kind) c left join theTable d on c.id &gt; d.id and c.kind=d.kind group by c.kind; </code></pre> <p>.... but still it is not fast enough</p> <p>Is there a more efficient way to achieve the same result? Thanks! <br> <br> <strong>Edit:</strong> theTbale is a history table so my task is to get the current values and the previous ones for each kind and compare them as part of an expression (logical operations, coalesces, ifs and etc) and determine if expression results are different</p> <p>here is an example resultset:</p> <pre> +-----------+-----------+ | max(c.id) | max(d.id) | +-----------+-----------+ | 1747 | NULL | | 1701 | 1432 | | 1703 | 1434 | | 1706 | 1437 | | 1707 | 1438 | | 1751 | NULL | | 1713 | 1444 | | 1750 | NULL | | 1709 | 1440 | | 1742 | 1741 | | 1711 | 1442 | | 1746 | 1745 | | 1708 | 1439 | | 1719 | 1450 | | 1725 | 1456 | | 1723 | 1454 | | 1740 | 1733 | | 1705 | 1436 | | 1702 | 1433 | | 1749 | 1748 | | 1712 | 1443 | | 1718 | 1449 | | 1722 | 1453 | | 1728 | 1459 | | 1721 | 1452 | | 1739 | 1731 | | 1714 | 1445 | | 1717 | 1448 | | 1716 | 1447 | | 1724 | 1455 | | 1710 | 1441 | | 1727 | 1458 | | 1720 | 1451 | | 1738 | NULL | | 1715 | 1446 | | 1704 | 1435 | | 1726 | 1457 | | 1758 | 1757 | +-----------+-----------+ </pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBoris D. Teoharov
    1. USBoris D. Teoharov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL: Select all twos of a kind with highest ids
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI see no reason for an outer join.
      singulars
      1. POMySQL: Select all twos of a kind with highest ids
      1. USDan Bracuk
    2. COWould it be faster?
      singulars
      1. POMySQL: Select all twos of a kind with highest ids
      1. USBoris D. Teoharov
    3. COCould you please post sample data and the expected result? Your task is not very clear. Performance (especially for large data sets) often depends on table indexing. What indexes do you have?
      singulars
      1. POMySQL: Select all twos of a kind with highest ids
      1. USPM 77-1
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload