2

So every morning at work we have a stand-up meeting. We throw the nearest object to hand around the room as a method of deciding who speaks in what order. Being slightly odd I decided it could be fun to get some data on these throws. So, every morning I memorise the order of throws (as well as other relevant things like who dropped the ball/strange sponge object that was probably once a ball too and who threw to someone who'd already been or just gave an atrocious throw), and record this data in a table:

+---------+-----+------------+----------+---------+----------+--------+--------------+
| throwid | day |    date    | thrownum | thrower | receiver | caught | correctthrow |
+---------+-----+------------+----------+---------+----------+--------+--------------+
|       1 |   1 | 10/01/2012 |        1 | dan     | steve    |      1 |            1 |
|       2 |   1 | 10/01/2012 |        2 | steve   | alice    |      1 |            1 |
|       3 |   1 | 10/01/2012 |        3 | alice   | matt     |      1 |            1 |
|       4 |   1 | 10/01/2012 |        4 | matt    | justin   |      1 |            1 |
|       5 |   1 | 10/01/2012 |        5 | justin  | arif     |      1 |            1 |
|       6 |   1 | 10/01/2012 |        6 | arif    | pete     |      1 |            1 |
|       7 |   1 | 10/01/2012 |        7 | pete    | greg     |      0 |            1 |
|       8 |   1 | 10/01/2012 |        8 | greg    | alan     |      1 |            1 |
|       9 |   1 | 10/01/2012 |        9 | alan    | david    |      1 |            1 |
|      10 |   1 | 10/01/2012 |       10 | david   | dan      |      1 |            1 |
|      11 |   2 | 11/01/2012 |        1 | dan     | david    |      1 |            1 |
|      12 |   2 | 11/01/2012 |        2 | david   | alice    |      1 |            1 |
|      13 |   2 | 11/01/2012 |        3 | alice   | steve    |      1 |            1 |
|      14 |   2 | 11/01/2012 |        4 | steve   | arif     |      1 |            1 |
|      15 |   2 | 11/01/2012 |        5 | arif    | pete     |      0 |            1 |
|      16 |   2 | 11/01/2012 |        6 | pete    | justin   |      1 |            1 |
|      17 |   2 | 11/01/2012 |        7 | justin  | alan     |      1 |            1 |
|      18 |   2 | 11/01/2012 |        8 | alan    | dan      |      1 |            1 |
|      19 |   2 | 11/01/2012 |        9 | dan     | greg     |      1 |            1 |
+---------+-----+------------+----------+---------+----------+--------+--------------+

I've now got quite a few days worth of data for this, and I'm starting to run some queries on it for my own purposes (I've not told the rest of the team yet...wouldn't like to influence the results). I've done a few with no issues, but I'm stuck trying to get a certain result out.

What I'm looking for is the number of times each person has been the last team member to receive the ball. Now, as you can see on the table, due to absences etc the number of throws per day is not always constant, so I can't simply select the receiver by thrownum.

In the case for the data above, it would return:

+--------+-------------------+
| person | LastReceiverTotal |
+--------+-------------------+
| dan    |                 1 |
| greg   |                 1 |
+--------+-------------------+

I've got this far:

SELECT MAX(thrownum) AS LastThrowNum, day FROM Throws GROUP BY day

Now, this returns some useful data. I get the highest thrownum for each and every day. It would seem like all I need to do is get the receiver for this value, and then get a count grouped by receiver to get my answer. This doesn't work, though, because the resultset isn't what it seems due to the above query using aggregate functions.

I suspect there's a much better way of designing tables to store the data to be honest, but equally I'm also sure there's a way to get this information with the tables as they are - some kind of inner query? I can't figure out how it would work. Can anyone shed some light on how this would be done?

Improve this question
7
  • 3
    Seriously: Go and do some work! ;-) Commented Feb 8, 2012 at 10:47
  • 1
    @JonEgerton This is far more important than any petty jobs the company might have on at the moment... minimises browser window Commented Feb 8, 2012 at 10:49
  • @Hecksa this is a very funny idea :P Commented Feb 8, 2012 at 10:58
  • @MarkBannister It's actually in Access, since I'd feel a bit bad using the dev db server for this...but at any rate, I shouldn't have a problem modifying solutions in any common SQL version to...whatever version access uses. I assume MSSQL. Commented Feb 8, 2012 at 11:04
  • 1
    @aF. Funny, and surprisingly interesting! There's already some interesting patterns developing with the throw orders, I suspect in a few months I'll be able to predict the throws with fairly decent accuracy (you'll probably see another question pop up in a while about that query, I haven'teven started to consider how I'd do it). Also there's some people hitting way higher catch percentages than others... :P Commented Feb 8, 2012 at 11:06

1 Answer 1

Reset to default
2

The query that you have gives you the biggest thrownum for each day.

With that, you just do a inner join with your table and get the receiver and the number of times he happears.

select t.receiver as person, count(t.day) as LastReceiverTotal from Throws t
inner join (SELECT MAX(thrownum) AS LastThrowNum, day FROM Throws GROUP BY day) a on a.LastThrowNum = t.thrownum and a.day = t.day
group by t.receiver
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Close...very close. I imagine it'd work fine in any sensible database, but since I'm using access, I get asked for a parameter value for a.thrownum when I run the query. It then seems to run correctly, but only for days which have the last thrownum equal to the parameter I entered manually. As it happens, I could cope with that with a little bit of effort, but I'm a developer, so any manual effort is unacceptable :P I'll poke around to see if I can find some information on SQL access at lunch, in the meantime is there any reason you can see for this happening?
Yes, it should work now. It's a.LastThrowNum and not a.thrownum :)
ugh, disappointed I didn't pick that one up myself. Works perfectly, thanks a lot.

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.