Databases
StackOverflow2013
Postsdbo
POCompare first and last SQL row with same id
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCompare first and last SQL row with same id
    primarykey
    data
    text
    <p>Here's my SQL Fiddle: <a href="http://sqlfiddle.com/#!2/90d45/3" rel="nofollow">http://sqlfiddle.com/#!2/90d45/3</a></p> <p>Table scheme:</p> <pre><code>CREATE TABLE domain_rankings (domain_id int, rank int, create_date datetime); INSERT INTO domain_rankings VALUES (1, 0, "2012-01-01"); INSERT INTO domain_rankings VALUES (1, 2, "2012-01-02"); INSERT INTO domain_rankings VALUES (1, 1, "2012-01-03"); INSERT INTO domain_rankings VALUES (2, 0, "2012-01-01"); INSERT INTO domain_rankings VALUES (2, 1, "2012-01-02"); INSERT INTO domain_rankings VALUES (2, 2, "2012-01-03"); INSERT INTO domain_rankings VALUES (3, 1, "2012-01-01"); INSERT INTO domain_rankings VALUES (4, 3, "2012-01-01"); INSERT INTO domain_rankings VALUES (4, 2, "2012-01-02"); INSERT INTO domain_rankings VALUES (4, 1, "2012-01-03"); </code></pre> <p>I want to get the count of the domain_id's that go up in rank comparing the first entry to the last entry (by date).</p> <p>So in this case, for the count of all ranks going up should be 2 (domain_id: 1, 2).</p> <p>domain_id 3 should not be included in the count because it has only one entry. So, probably need a subquery of HAVING COUNT(*) > 1.</p> <p>domain_id 4 also shouldn't be included in the count unless I reverse the query and want the ranks that go down.</p> <p>How should I go about solving this problem? I'm aware that I'll need subqueries, but I have no idea where to begin with it. The JSFiddle should give you an idea of where I'm stuck.</p> <p>Attempts - this returns the rows that are valid, but not the COUNT of the rows correctly: </p> <pre><code>SELECT domain_id, COUNT(DR.domain_id) FROM domain_rankings DR WHERE (SELECT rank FROM domain_rankings WHERE domain_rankings.domain_id = DR.domain_id ORDER BY create_date ASC LIMIT 1 ) &gt; ( SELECT rank FROM domain_rankings WHERE domain_rankings.domain_id = DR.domain_id ORDER BY create_date DESC LIMIT 1 ) GROUP BY DR.domain_id HAVING count(*) &gt; 1 </code></pre> <p>Final answer (slower of the two options):</p> <pre><code>SELECT COUNT(a.domain_id) cnt FROM ( SELECT domain_id,MIN(create_date) mind, MAX(create_date) maxd FROM domain_rankings GROUP BY domain_id HAVING COUNT(*) &gt; 1 ) master JOIN domain_rankings a ON a.domain_id = master.domain_id AND a.create_date = master.mind JOIN domain_rankings b ON b.domain_id = master.domain_id AND b.create_date = master.maxd WHERE a.rank &lt; b.rank </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRijvi Rajib
    1. USRijvi Rajib
    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. POCompare first and last SQL row with same id
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you post your expected table result?
      singulars
      1. POCompare first and last SQL row with same id
      1. USLloyd Banks
    2. COI thought I did? Result should be: If I want all ranks with keywords going up, the result should be 2. If I want all ranks with keywords going down, the result should be 1. We ignore domain_id 3 because it has only one row.
      singulars
      1. POCompare first and last SQL row with same id
      1. USRijvi Rajib
 

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