SQL query comparing an attribute in multiple tuples based on values of another attribute within the relation

Question

I just made this post a little while ago: SQL query concerning the sum of occurrences for multiple rows

The table below is the same but with a slight modification:

                    Test

    +--------+--------+--------+--------+
    |  Name  |  Date  |Location| Score  |
    +--------+--------+--------+--------+
    | Steven |03-05-12| 120000 |   78   |
    +--------+--------+--------+--------+
    | James  |04-09-11| 110000 |   67   |
    +--------+--------+--------+--------+
    | James  |06-22-11| 110000 |   58   |
    +--------+--------+--------+--------+
    |  Ryan  |10-11-13| 250000 |   62   |
    +--------+--------+--------+--------+
    |  Ryan  |12-19-13| 180000 |   95   |
    +--------+--------+--------+--------+

Essentially, I need a query to select the names of people who have taken multiple tests and gotten a lower score the next time.

For example, it would select James but not Ryan because he got a higher score the second time.

Thanks

Juan Carlos Oropeza · Accepted Answer · 2015-11-04 16:52:32Z

2

SELECT DISTINCT a.name
FROM test AS a 
JOIN test AS b 
  ON a.name = b.name 
 AND a.date > b.date 
 AND a.score < b.score

SQL Fiddle

edited Nov 4, 2015 at 16:52

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

answered Nov 4, 2015 at 5:18

Ken Geis

9226 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query comparing an attribute in multiple tuples based on values of another attribute within the relation

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related