CODEWITHSUNDEEP
sqlselectjoinleft-join
Advice Me
Advice Me

Reputation: 79

SQL multi select with join

I can find a solution for one of my sql statement and I want to ask for your help ;)

Lets imagine that we have table of Athletes and they have to run 100 meters of what ever distance. after each run the table innserts Run, Name and Time for each run:

Run Name  Time[s]
1   Joe     10
2   Joe     11
1   Jim     14
2   Jim     12

What I want to do, is to select Run, Name And fastest time, so it this case it would be min(Time). But when I use Min aggregate function I have to group by all other parameters, so it will return all records...

I have tried this. But it doesn't work:

SELECT MIN(Time) AS MinTime, Name AS TimeName
        From Athletes
        group by TimeName
        LEFT JOIN  
        (Select Run, Name, Time From Athletes a)
         On 
         a.Name = TimeName, a.Time = MinTime;

Yes, it has syntax error but it's not the main problem, as I udrestand it still won't work.

thanks in advance, SQL newbie

Upvotes: 2

Views: 3927

Answers (5)

the_pete
the_pete

Reputation: 822

Your SQL query is all over the place You have an uncessary join and your group by is in the wrong place and your left join syntax is also incorrect. I scrapped it and rewrote it to use min() properly.

SELECT run, MIN(Time) AS MinTime,
Name AS Timename
From Athletes
group by TimeName

There is no need to run a join on the same table and if you knew which columns you needed to group by, there is also no need for a where clause.

Tested and working on sqlfiddle. http://sqlfiddle.com/#!2/13b1a/2

I suggest you read up on what group by does and how to join tables properly.

As your table grows, if you aren't using any kind of id or key, you can use a more advanced query that does actually involve a subquery:

select a.run,
a1.name,
a1.Time
from athletes as a
    ,(select name,
    min(time) as Time
    from athletes
    group by name) as a1
where a.time = a1.Time
and a.name = a1.name

There still isn't a reason to use a join and the subquery route is the appropriate route to take with your current data set and schema. http://sqlfiddle.com/#!2/23cb4/23

Upvotes: 4

Dave Mason
Dave Mason

Reputation: 4936

I see left-join is one of the tags for you question, but it can be accomplished without it.

SELECT *
FROM Athletes a
WHERE a.Time = ( SELECT MIN(a2.Time) FROM Athletes a2 )

Note that you may get multiple results from the query if there are two or more runners tied for the fastest time. Hope this helps.

Here's the query to get the same info, grouped by athlete name:

SELECT a.*
FROM #Athletes a
JOIN ( SELECT a2.Name, MIN(a2.Time) Time FROM #Athletes a2  GROUP BY a2.Name) BestTimes
ON a.Name = BestTimes.Name
AND a.Time = BestTimes.Time

Upvotes: -1

Jiang
Jiang

Reputation: 588

Try this query:

SELECT r.Run, r.Name, r.Time 
FROM (SELECT * FROM Athletes ORDER BY Time) r 
GROUP BY r.Name

Upvotes: 0

DLeh
DLeh

Reputation: 24395

SELECT * 
FROM Athletes a
WHERE a.Time = (select min(time) from Athletes a2 where a2.Name = a.Name)

Here's an example usage on SqlFiddle

Upvotes: 0

MikkaRin
MikkaRin

Reputation: 3084

select Run , Name, Time from Athletes a
where Time=(select min(time) 
      from Athletes s 
      where s.Name=a.Name)

Upvotes: 0

Related Questions