complicated query help

bpsintl · #1 (**permalink**) 10-27-08, 08:48

Hi, I need help with a complicated query.

I have 2 tables in question, members and swim_times.

The tables are joined together on members.member_id = swim_times.memberid but here's where it gets complicated:

There are the following fields in the swim_times table

eventid (int)
pool_size (int)
location (varchar)
stroke (int)
swim_time (varchar)

Now, this table is for storing swimmers swim times. The eventid is from another table and holds the swim distances like 25m, 100m, 200m etc

The pool_size is either 50 or 25

The location is a free text field that the user can input when adding the swim time

The stroke field is either 1, 2, 3, 4 or 5

The swim_time is a varchar field storing the swim times like 00:47:71 (mm:ss:ms)

I want to be able to select the swimmers best time (one result) for each of the variables like pool_size, stroke, eventid, location etc

Any ideas?

Here's the query that joins the tables

select swim_times.*, members.*
from members inner join swim_times on members.member_id = swim_times.memberid

r937 · #2 (**permalink**) 10-27-08, 09:09

Code:

SELECT pool_size, MIN(swim_time) 
  FROM swim_times 
 WHERE member_id = 42
GROUP BY pool_size

Code:

SELECT stroke, MIN(swim_time) 
  FROM swim_times 
 WHERE member_id = 42
GROUP BY stroke

Code:

SELECT location, MIN(swim_time) 
  FROM swim_times 
 WHERE member_id = 42
GROUP BY location

eck setterah

gvee · #3 (**permalink**) 10-27-08, 09:18

Quote:

The swim_time is a varchar field storing the swim times like 00:47:71 (mm:ss:ms)

Ouch.
Does your solution work through this correctly Rudy (gut reaction is "yes, it bodges its way through")?

bpsintl · #4 (**permalink**) 10-27-08, 09:22

georgev, are you asking me that question?

bpsintl · #5 (**permalink**) 10-27-08, 09:25

@r937 I was looking for a single query, not 3 separate ones. The way it works is that it goes through the members table listing the members best swim times, but only their best per stroke, event, location etc.

If a swim time has either the same stroke, location, event etc as another one, only the best (lowest) time is displayed.

Does that make sense?

r937 · #6 (**permalink**) 10-27-08, 09:27

Quote:

Originally Posted by bpsintl

If a swim time has either the same stroke, location, event etc as another one, only the best (lowest) time is displayed.

Code:

SELECT MIN(swim_time)
  FROM swim_times
 WHERE member_id = 42

Internet Services

Web Development

Digital Marketing

Consumer Tech