0

Recently posted this question: SQL query comparing an attribute in multiple tuples based on values of another attribute within the relation

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 |   55   |
    +--------+--------+--------+--------+
    |  Ryan  |01-20-15| 180000 |   99   |
    +--------+--------+--------+--------+

Notice that Ryan's score decreases but then increases later. The response that I previously received still selects Ryan in this case despite his scores not always increasing. I understand I might not have been clear in my original post, but is there any query I could do to fix this?

Thanks

EDIT: Sorry, I posted this very quickly.

Essentially I need a query to select the names of all of the people who got lower scores every consecutive test they attempted.

ie, not accept Ryan, but accept James

Improve this question
13
  • can you then combine the two questions, go back to that one. And re-jigger your Accepted Answer accordingly. Instead of dupes. We now have 3 variations on the theme. 3 questions Commented Nov 4, 2015 at 15:08
  • also, make it clear that it is definitely mysql and not Sql Server (or whatever the case is). A guy wrote up a Sql Server answer with partition because some find sql to mean Sql Server Commented Nov 4, 2015 at 15:13
  • Although it's unfortunate that the OP has had trouble figuring out how to pose the question he really wanted answered, it would be poor form for him to go back and fundamentally change his others after accepting answers to them. Commented Nov 4, 2015 at 15:15
  • I do not notice that Ryan score decreases but then increases later!!! also be clear in which cases Ryan must be in or out the output Commented Nov 4, 2015 at 15:16
  • I agree @JohnBollinger Commented Nov 4, 2015 at 15:16

2 Answers 2

Reset to default
1

I think John answer is great but I want add some information.

Using this base query SqlFiddleDemo here you can include all the conditionals in the left join

select t1.Name, t1.Date, t1.Score, t2.Date, t2.Score
from
  student t1
  left join student t2 
         on t1.Name = t2.Name
        and t1.Date < t2.Date
        and t1.Score <= t2.Score

|   Name |                       Date | Score |                      Date |  Score |
|--------|----------------------------|-------|---------------------------|--------|
|   Ryan |  October, 11 2013 00:00:00 |    62 | January, 20 2015 00:00:00 |     99 |
|   Ryan | December, 19 2013 00:00:00 |    55 | January, 20 2015 00:00:00 |     99 |
| Steven |    March, 05 2012 00:00:00 |    78 |                    (null) | (null) |
|  James |    April, 09 2011 00:00:00 |    67 |                    (null) | (null) |
|  James |     June, 22 2011 00:00:00 |    58 |                    (null) | (null) |
|   Ryan |  January, 20 2015 00:00:00 |    99 |                    (null) | (null) |

Then you can use a conditional SUM to find how many times each name increase his score. In this case Ryan will be 2

select t1.Name, SUM(IF(t2.Date IS NULL, 0, 1)) as increase_score
from
  student t1
  left join student t2 
         on t1.Name = t2.Name
        and t1.Date < t2.Date
        and t1.Score <= t2.Score
GROUP BY t1.Name
HAVING 
    increase_score = 0                    -- not increase score in any test
and count(*) > 1                          -- present more than one test
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

All I know is this: yours returns James, and John's Answer returns Steven. And I had to read both questions (this one and prior) from Op to come up with this: Students that only have continuously decreasing grades. And I would move mountains before i use a NOT IN. I think the answer is James. And the OP needs to know how to write a question without cookie crumbs scattered as someone said
@Drew Thanks have to fix it after read again the comment to remove the ones didnt present more than one test. The good part is easy to change if want those include it agan.
well the results you are getting are the ones I expect. :)
1

UPDATE: This is a complete replacement for my original, wrong, answer.

The problem is complicated by the fact that you want to simultaneously select for two very different criteria:

  1. The person has multiple rows in table Test, and
  2. Every row for a given person and date has a lesser score than is recorded for the same person on every earlier date

The fact that you are comparing different rows of the same table suggests approaching the problem via a self-join:

FROM
  Test t1
  join Test t2
    on t1.Name = t2.Name

If we filter out the results arising from joining each row to itself, then that leaves only rows pertaining to people who are referenced by multiple rows. Moreover, for rows R1 and R2 both with the same Name, we only need to consider one of the pairs (R1, R2) and (R2, R1). We can address both of those considerations with a single filter:

WHERE t1.Date < t2.Date

We want to perform an analysis of the joined result on a Name-by-Name basis; that suggests an aggregate query (if suitable aggregate functions are available):

GROUP BY t1.Name

We want only those aggregates that satisfy our criteria, and those criteria are that every row that passed the WHERE filter, and therefore has t1.Date < t2.Date, also has t1.Score > t2.Score. We can rely here on the fact that relational operators evaluate to a number: 1 for true, and 0 for false. If we add those values across each group, we can determine whether every row satisfied the criterion:

HAVING SUM(t1.Score > t2.Score) = COUNT(*)

Given that we want to select only the names (which are conveniently already distinct, courtesy of the grouping), that all comes together as

SELECT t1.Name
FROM
  Test t1
  join Test t2
    on t1.Name = t2.Name
WHERE t1.Date < t2.Date
GROUP BY t1.Name
HAVING SUM(t1.Score > t2.Score) = COUNT(*)

Here's a fiddle, with sample data drawn from the question: http://sqlfiddle.com/#!9/8dcba/16/0

Improve this answer

1 Comment

Updated with a completely revised answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.