Can MySQL do this?

[jddc]

I have a Table as follows:

 

Name, EventNo, Event, Score with example data as follows:

 

John Williams, 1, 100m Hurdles, 13.75

George Smith, 1, 100m Hurdles, 13.88

Mike Stone, 1, 100m Hurdles, 14.09

George Smith, 1, 100m Hurdles, 13.99

Mark Greene, 1, 100m Hurdles, 14.17

John Williams, 2, 100m, 10.87

Will Downs, 2, 100m, 10.96

Joe Canton, 1, 100m Hurdles, 13.85

John Williams, 2, 100m, 10.75

George Smith, 2, 100m, 10.79

John Williams, 2, 100m, 11.25

 

I would like to return a results as follows (Interested in Top4 only if there is 4):

 

John Williams, 1, 100m Hurdles, 13.75

George Smith, 1, 100m Hurdles, 13.88

Joe Canton, 1, 100m Hurdles, 13.85

Mike Stone, 1, 100m Hurdles, 14.09

John Williams, 2, 100m, 10.75

George Smith, 2, 100m, 10.79

Will Downs, 2, 100m, 10.96

 

Is This Possible???

 

Thanks

[pikemsu28]

try something like this:

 

$sql = "SELECT DISTINCT Name, EventNo, Event, Score FROM {table name} ORDER BY Score DESC LIMIT 4";

 

[jddc]

$sql = "SELECT DISTINCT Name, EventNo, Event, Score FROM {table name} ORDER BY Score DESC LIMIT 4";

 

Not quite.

 

This returns a total of 4 records... 

 

I need 4 from each group!  (100m Hurdles & 100m)

 

 

[fenway]

Not trivially... you can easily get the list of the events, but without knowing how many there are in advance, you won't know how many tables to join/union.

[jddc]

Not trivially... you can easily get the list of the events, but without knowing how many there are in advance, you won't know how many tables to join/union.

 

I am so new to MySQl that I have done about 20 SELECTS and I have done very few join/unions.  To help me understand/learn...

 

If I know the events,hw would I accomplish it?

 

Also, if I do not know the events, would I just have to do SELECTS over and over through the 'comlete' event list and combine the results?

 

I am afraid I may have to just query the entire table and then through PHP eliminate the extra data and the 'report it'  Is that correct?

 

Thanks!

 

[teng84]

this is one of the way of doing that

$query = 'select distinct(category) form tablename';
$result =mysql_query($query);
while ($row = $result){
$select = "select fields form tablename where field = $row[field] limit 4";
$result =mysql_query($select);
while ($row2 = $result){
//do anything here
}
}

[Barand]

this gives top 4 for each event based on score. If you want them based by table order, use id instead of score.

SELECT r.*
FROM results r
WHERE (SELECT COUNT(*) FROM results b WHERE b.score < r.score AND b.eventno = r.eventno) < 4
ORDER BY event, score

[jddc]

this gives top 4 for each event based on score. If you want them based by table order, use id instead of score.

SELECT r.*
FROM results r
WHERE (SELECT COUNT(*) FROM results b WHERE b.score < r.score AND b.eventno = r.eventno) < 4
ORDER BY event, score

 

Wow!  Very close!  The only difficulty is that certain names are being repeated that can not be...

 

John Williams, 1, 100m Hurdles, 13.75

George Smith, 1, 100m Hurdles, 13.88

Joe Canton, 1, 100m Hurdles, 13.85

George Smith, 1, 100m Hurdles, 13.99 (Repeat: should be Mike Stone 14.09)

John Williams, 2, 100m, 10.75

George Smith, 2, 100m, 10.79

John Williams, 2, 100m, 10.87 (Repeat: name should be not listed)

Will Downs, 2, 100m, 10.96

 

Thanks!

[Barand]

A bit more complex

SELECT r.*
FROM results r
INNER JOIN (SELECT name, eventno, MIN(score) as mscore FROM results GROUP BY name, eventno) as X
  ON r.name = X.name AND r.eventno = X.eventno AND r.score = X.mscore
WHERE (SELECT COUNT(*) FROM (SELECT name, eventno, MIN(score) as mscore FROM results GROUP BY name, eventno) b
WHERE b.mscore < r.score AND b.eventno = r.eventno) < 4
ORDER BY event, score

 

-->[pre]

9, 'John Williams', 2, '100m', '10.75'

10, 'George Smith', 2, '100m', '10.79'

7, 'Will Downs', 2, '100m', '10.96'

 

1, 'John Williams', 1, '100m Hurdles', '13.75'

8, 'Joe Canton', 1, '100m Hurdles', '13.85'

2, 'George Smith', 1, '100m Hurdles', '13.88'

3, 'Mike Stone', 1, '100m Hurdles', '14.09'

[fenway]

Complex indeed... well done.

[Barand]

If you take the lid off it's just like creating a temporary table from

SELECT name, eventno, eventName, MIN(score) as mscore FROM results GROUP BY name, eventno

 

which eliminates duplicate names, just having the best time for each one.

 

Then run my original, simpler query on that.

[jddc]

A bit more complex

SELECT r.*
FROM results r
INNER JOIN (SELECT name, eventno, MIN(score) as mscore FROM results GROUP BY name, eventno) as X
  ON r.name = X.name AND r.eventno = X.eventno AND r.score = X.mscore
WHERE (SELECT COUNT(*) FROM (SELECT name, eventno, MIN(score) as mscore FROM results GROUP BY name, eventno) b
WHERE b.mscore < r.score AND b.eventno = r.eventno) < 4
ORDER BY event, score

 

AWESOME!  Thank you sooooo much!  Since I am so new to SQL, could you explain the statement?  I really want to understand it!

 

 

If you take the lid off it's just like creating a temporary table from

SELECT name, eventno, eventName, MIN(score) as mscore FROM results GROUP BY name, eventno

 

which eliminates duplicate names, just having the best time for each one.

 

Then run my original, simpler query on that.

 

Do you mean combining it with:

SELECT r.*
FROM results r
WHERE (SELECT COUNT(*) FROM results b WHERE b.score < r.score AND b.eventno = r.eventno) < 4
ORDER BY event, score

 

 

[Barand]

Creating the temp table would give a single row for each person/event with their best time

[pre]

'George Smith', 1, '100m Hurdles', '13.88'

'George Smith', 2, '100m', '10.79'

'Joe Canton', 1, '100m Hurdles', '13.85'

'John Williams', 1, '100m Hurdles', '13.75'

'John Williams', 2, '100m', '10.75'

'Mark Greene', 1, '100m Hurdles', '14.17'

'Mike Stone', 1, '100m Hurdles', '14.09'

'Will Downs', 2, '100m', '10.96'

[/pre]

 

If you now use this table (instead of results table) in my first query you don't have the duplicate name problem.

 

The WHERE clause finds those records which have 3 or less records with a lower time so you get the top 4 only for each event.

 

The second query just substitutes subqueries for the temp table