Databases
StackOverflow2013
Postsdbo
POAllow a maximum number of entries when certain conditions apply
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAllow a maximum number of entries when certain conditions apply
    primarykey
    data
    text
    <p>I have a dataset with a lot of entries. Each of these entries belongs to a certain ID (belongID), the entries are unique (with uniqID), but multiple entries can come from the same source (sourceID). It is also possible that multiple entries from the same source have a the same belongID. For the purposes of the research I need to do on the dataset I have to get rid of the entries of a single sourceID that occur more than 5 times for 1 belongID. The maximum of 5 entries that need to be kept are the ones with the highest 'Time' value.</p> <p>To illustrate this I have the following example dataset:</p> <pre><code> belongID sourceID uniqID Time 1 1001 101 5 1 1002 102 5 1 1001 103 4 1 1001 104 3 1 1001 105 3 1 1005 106 2 1 1001 107 2 1 1001 108 2 2 1005 109 5 2 1006 110 5 2 1005 111 5 2 1006 112 5 2 1005 113 5 2 1006 114 4 2 1005 115 4 2 1006 116 3 2 1005 117 3 2 1006 118 3 2 1005 119 2 2 1006 120 2 2 1005 121 1 2 1007 122 1 3 1010 123 5 3 1480 124 2 </code></pre> <p>The example in the end should look like this:</p> <pre><code> belongID sourceID uniqID Time 1 1001 101 5 1 1002 102 5 1 1001 103 4 1 1001 104 3 1 1001 105 3 1 1005 106 2 1 1001 107 2 2 1005 109 5 2 1006 110 5 2 1005 111 5 2 1006 112 5 2 1005 113 5 2 1006 114 4 2 1005 115 4 2 1006 116 3 2 1005 117 3 2 1006 118 3 2 1007 122 1 3 1010 123 5 3 1480 124 2 </code></pre> <p>There are a lot more columns with data entries in the file, but the selection has to be purely based on time. As shown in the example it can also occur that the 5th and 6th entry of a sourceID with the same belongID have the same time. In this case only 1 has to be chosen, because max=5.</p> <p>The dataset here is nicely ordered on belongID and time for illustrative purposes, but in the real dataset this is not the case. Any idea how to tackle this problem? I have not come across something similar yet.. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMatt Dowle
    1. USMax van der Heijden
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAllow a maximum number of entries when certain conditions apply
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAllow a maximum number of entries when certain conditions apply
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for showing sample data and expected results.
      singulars
      1. POAllow a maximum number of entries when certain conditions apply
      1. USAndrie
 

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