SQL: Improve Select Query

Question

I have reasons to believe that this code can be done much better, and probably doing much more of the work with the query to begin with?

I'll try to explain this.

Each row on my database has either a 1, 2 or 3 value for exercise, and then also a number as rep that could be any number, but in this code I choose to only care for 1-12, so this code selects the row that has the highest value of kilograms (which is a column and has a value on each row) where exercise is 1 and rep is 1, and then 2 and 3 etc. etc. up to 12, then changes exercise to 2 and goes from 1-12 again selecting the top kilograms row.

Does this make sense?

for (var i = 1; i <= 3; i++) {
    for (var ii = 1; ii <= 12; ii++) {
        var getPR = "SELECT top 1 kg, rep, date FROM Test WHERE exerVariName = 'Comp' AND exercise = @0 AND rep = @1 order by kg desc";
        db.Execute(getPR, i, ii);
        foreach (var get in db.Query(getPR, i, ii)) {
            DateTime Date = get.Date;
            var finalDate = Date.ToString("MMM d, yyyy");
            var weight = get.kg + "kg";
            var reps = "x " + get.rep;
            <a>@weight @reps - @finalDate</a>
            <br>
        }
    }
}

I use SQL Server Compact, and it's not a MVC project.

Answer 1

You can select all rows you are interested in with only one query using Group By and MAX aggregated function.

SELECT t.kg, t.rep, t.date
FROM Test t
INNER JOIN 
    (SELECT MAX(kg) as kg, exercise, rep
    FROM Test
    WHERE exerVariName = 'Comp'
    GROUP BY exercise, rep) i
ON t.exercise = i.exercise AND t.rep = i.rep AND t.kg = i.kg
WHERE t.exerVariName = 'Comp'

Inner query is executed only once. It finds a group identifier (exercise, rep) tuple and a corresponding maximum kg group value. Then inner query is joined with Test table in order to get "content" of rows (in your case only one additional field date).

Overall performance is quit optimal.

You need only to iterate over results of this query.

See this topic.

---

Edit:

Exclude multiple (rep, exercise) records having same kg (almost same result as OP's looping)

SELECT kg, rep, exercise, MAX(date) 
FROM 
    (SELECT t.kg, t.rep, t.exercise, t.date
    FROM Test t
    INNER JOIN 
        (SELECT MAX(kg) as kg, exercise, rep
        FROM Test
        WHERE exerVariName = 'Comp'
        GROUP BY exercise, rep) i
    ON t.exercise = i.exercise AND t.rep = i.rep AND t.kg = i.kg
    WHERE t.exerVariName = 'Comp') t
GROUP BY t.kg, t.rep, t.exercise