Databases
StackOverflow2013
Postsdbo
POIs there a query in MySQL that would allow variable group numbers and limits akin to this
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there a query in MySQL that would allow variable group numbers and limits akin to this
    primarykey
    data
    text
    <p>I've checked out a few of the stackoverflow questions and there are similar questions, but didn't quite put my fingers on this one.</p> <p>If you have a table like this:</p> <pre><code>uid cat_uid itm_uid 1 1 4 2 1 5 3 2 6 4 2 7 5 3 8 6 3 9 </code></pre> <blockquote> <p>where the uid column in auto_incremented and the cat_uid references a category of relevance to filter on and the itm_uid values are the one we're seeking</p> </blockquote> <p>I would like to get a result set that contains the following sample results:</p> <pre><code>array ( 0 =&gt; array (1 =&gt; array(4,5)), 1 =&gt; array (2 =&gt; array(6,7)), 2 =&gt; array (3 =&gt; array(8,9)) ) </code></pre> <p>An example issue is - select 2 records from each category (however many categories there may be) and make sure they are the last 2 entries by uid in those categories.</p> <p>I'm not sure how to structure the question to allow an answer, and any hints on a method for the solution would be welcome!</p> <p><strong>EDIT:</strong> This wasn't a very clear question, so let me extend the scenario to something more tangible.</p> <p>I have a set of records being entered into categories and I would like to select, with as few queries as possible, the latest 2 records entered per category, so that when I list out the contents of those categories, I will have at least 2 records per category (assuming that there are 2 or more already in the database). A similar query was in place that selected the last 100 records and filtered them into categories, but for small numbers of categories with some being updated faster than others can lead to having the top 100 not consisting of members from every category, so to try to resolve that, I was looking for a way to select 2 records from each category (or N-records assuming it's the same per-category) and for those 2 records to be the last entered. A date field is available to sort on, but the itm_uid itself could be used to indicate inserted order.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLightness Races in Orbit
    1. USMyStream
    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. POIs there a query in MySQL that would allow variable group numbers and limits akin to this
      1. This table or related slice is empty.
      1. VTClose
    1. COIt is not too clear what you actually want to do, but maybe have a look at the MySQL `GROUP_CONCAT()` function.
      singulars
      1. POIs there a query in MySQL that would allow variable group numbers and limits akin to this
      1. USChronial
    2. COI've extended my initial question with a scenario
      singulars
      1. POIs there a query in MySQL that would allow variable group numbers and limits akin to this
      1. USMyStream
 

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