0

I have a table structure in my mysql database that looks like this:

id | fname | lname | event | time | date

The table above is used for track meet results. All the results are put into the table for every meet every year.

Now what I did is get the "Top Ten List" for a specific event.

`SELECT * FROM Times WHERE event='1600m' ORDER BY time LIMIT 10

Everything works fine and dandy until I showed the results to my coach and he said "well... How is Joe Schmoe on there three times?"

So what I need to do is use DISTINCT (i'm guessing) so each name can only appear on the list once.

I can't seem to figure out how to do this with the two different columns. Any help?

krummens
  • 837
  • 2
  • 17
  • 38
  • 1
    No, you need to use `GROUP BY fname, lname`. `DISTINCT` works with the entire `SELECT` list. – Barmar Apr 03 '15 at 02:07
  • `DISTINCT` applies to all selected columns, if you want to distinct by `fname` and `lname` only you either have to select only those values instead of `*` or use `GROUP BY fname, lname`. – Havenard Apr 03 '15 at 02:09
  • And see http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group for how to retrieve the best result for each player when you're grouping. – Barmar Apr 03 '15 at 02:09
  • Joe Schmoe is just that good apparently – nomistic Apr 03 '15 at 02:10
  • 1
    haha. I'm actually the one on the list 3 times but i don't want to brag or anything @nomistic – krummens Apr 03 '15 at 02:11
  • So what would the entire `SELECT` statement look like @Barmar? – krummens Apr 03 '15 at 02:11
  • Sorry, the question is closed, it's not possible to answer. Read the related question and try to figure it out. If you can't, update your question with what you tried and request that it be reopened. – Barmar Apr 03 '15 at 02:12

0 Answers0