1

I've looked into Dynamic SQL and the inc() function, but neither are really what I'm after.

Say I have a database like this:

grade     name      age
9         Bob       9
10        Sue       11
11        Larry     15
9         Joe       8
10        Carrot    10

I want to create a table that first selects all the rows with the lowest grade (9) then displays the oldest. It then goes through and searches for the next highest grade (10) and displays the oldest. Then goes to the next highest grade (11) and displays the oldest.

I'd like for them all to be in the same table and not have to write out a separate SQL call and different PHP variables for each grade.

This is the SQL call I have right now:

$query = "SELECT * FROM horses WHERE grade='1' ORDER BY points DESC LIMIT 1" or die(mysql_error());

Is there a way I can make the grade column increment until it reaches the highest number in the database?

Thanks for any suggestions.

Chelsea
  • 335
  • 2
  • 13
  • Please let us know which RDBMS you're using. It seems to be MySQL, but I'm not 100% sure. – Serge Belov Dec 06 '12 at 00:34
  • By the way, storing `age` is usually a terrible thing to do: you'd be constantly updating the data. It would be much better to _derive_ the information at need, based off of birthdays (I'm aware racehorses have some special rules here, but it shouldn't be too bad). – Clockwork-Muse Dec 06 '12 at 00:42
  • @SergeBelov, I don't know what RDBMS means, but I do use a MySQL Database...I didn't know there was a difference. – Chelsea Dec 06 '12 at 00:57
  • @Clockwork-Muse, Age isn't kept in the actual database statically; it's calculated in the PHP some where. I justed used it as an example. – Chelsea Dec 06 '12 at 00:57

1 Answers1

2

You don't need a loop for this if I understand your request. Instead, you need a MAX() aggregate grouped by grade. The following method should work independently of your RDBMS. It relies on a JOIN against a subquery which returns the greatest age per group to get the age/group pair and join that back against the main table to retrieve the name (and other columns as needed).

SELECT
  horses.grade, 
  horses.name, 
  horses.age
FROM
  horses
  JOIN (
    SELECT grade, MAX(age) as maxage
    FROM horses 
    GROUP BY grade
  ) ma ON horses.grade = ma.grade AND horses.age = ma.maxage
ORDER BY grade ASC

Here is an example on SQLFiddle.com

Returns:

GRADE NAME  AGE
9     Bob    9
10    Sue    11
11    Larry  15

It is generally far faster an less resource-intensive to do one query instead of multiple queries in a loop, so this should be the approach whenever possible.

Community
  • 1
  • 1
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • This is what I want (looked at your example). I'm just having some issues getting it to work for me. I'm fiddling around with it now. – Chelsea Dec 06 '12 at 00:59
  • Awesome, I'm getting the info I want now. Any tips on ordering it by grade? I've tried adding `ORDER BY 1 DESC` as suggested by this thread: http://stackoverflow.com/questions/27983/sql-group-by-with-an-order-by But that doesn't seem to be working. – Chelsea Dec 06 '12 at 01:25
  • 1
    You've got it sorted out? `ORDER BY grade ASC` to sort grade in ascending order. – Michael Berkowski Dec 06 '12 at 01:50
  • Yep, I refreshed and saw you added that. (: – Chelsea Dec 06 '12 at 01:52