SQL question

Results 1 to 2 of 2

Thread: SQL question

  1. #1
    Join Date
    Dec 1969

    Default SQL question

    I have a table with name, race time and events for approx 20,000 races and 600 athletes. I want to pull out each person&#039;s fastest time per event and then rank them in an overall order from 1 to whatever. <BR><BR>I have tried using the group by function along with the min(time) which is working as expected. Also did min(Team) which works since there isn&#039;t a max or min team (this was suggested from an ASPmessageboard.com user - which worked for that field.)<BR><BR>However this only gives me the athlete&#039;s name, time and team. I want to include the age and date fields but can&#039;t with aggregate functions. <BR><BR>How can I create a query that will return the following: group the athletes by name (Group by), show their personal fastest time (MIN function), pull team (MIN Team) and also pull the age and date fields????<BR><BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Temp table or view

    Use your first query (as you described it) to create a VIEW (if available in the DB you are using, a temp table if not). Then simply use an ordinary JOIN with that view to get the full data.<BR><BR>SELECT * FROM table, theView<BR>WHERE table.athlete = theView.athlete<BR>AND table.timing = theView.timing<BR>AND table.team = theView.team<BR><BR>Not sure from your description whether you need to put the .team in the join, but I guess it couldn&#039;t hurt.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts