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.

Razor files are made for applying view model data into HTML markup. DB requests should live in controller and pass a view model with the data. (Assuming MVC) — trailmax, Feb 10 '17 at 17:32
Duh.. I've read the last line. Sorry, my bad. Should not skim questions. — trailmax, Feb 10 '17 at 17:34
Ye, im not doing a MVC project, that was all too complicated, never found a good tutorial for it, but I'll try to learn about it later, it's somewhat hard to find help for my problems I run into since most questions on here are in "MVC format" when it comes to razor @trailmax — Pontus Svedberg, Feb 10 '17 at 17:34
There is a free video course: https://app.pluralsight.com/player?author=scott-allen&name=mvc4-building-m1-intro&mode=live&clip=0&course=mvc4-building It is for MVC4, but basic principles are unchanged going into MVC5. Also this official tutorial: https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/introduction/index if you prefer to read rather than video. — trailmax, Feb 10 '17 at 18:32

score 3 · Accepted Answer · edited May 23 '17 at 12:33

3

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

edited May 23 '17 at 12:33

Community

1
1

answered Feb 10 '17 at 15:36

Ivan Gritsenko

4,166
2
20
34

I'm testing these out right now, I see that I need to learn more about joins because I dont understand the code haha, however, these two different queries result in different things, the later one showing like half the amount of rows, but I'm not sure which one is the correct one yet, I'd like to loop out `exercise` as well so I can see what is what to determine whether its correct or not, if you'd like to help out with that! – Pontus Svedberg Feb 10 '17 at 18:00
Okey, so I managed to display the exercise number and compare it to the results of my old query/for loop code and when `exercise` is 2 or 3 it got all the correct results, however when it was 1, it only showed 4 out of 6 results that my query showed, can't figure out why :/ – Pontus Svedberg Feb 10 '17 at 18:20
What *'my query'* are you talking about? As I can see you `getPR` query may return at most 1 result. You say your query returned 6. – Ivan Gritsenko Feb 10 '17 at 18:24
I'll update my post and include a picture to compare – Pontus Svedberg Feb 10 '17 at 18:26
@Pontus Svedberg, you may have different results for queries in case of some rows with `rep` or `exercise` columns are `NULL`. – Ivan Gritsenko Feb 10 '17 at 18:34
There are no `NULL` values in my database :/ cant really see whats the problem since I'm pretty sure this would solve my problem! – Pontus Svedberg Feb 10 '17 at 18:41
@Pontus Svedberg, let's go to [chat](https://niltalk.com/r/m4PxE) to discuss. Password - **stackoverflow** – Ivan Gritsenko Feb 10 '17 at 18:46
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/135416/discussion-between-ivan-gritsenko-and-pontus-svedberg). – Ivan Gritsenko Feb 10 '17 at 18:47

score 0 · Answer 2 · answered Feb 10 '17 at 15:32

0

SELECT kg, rep, date, exercise, rep FROM Test test1 WHERE rep = (SELECT TOP 1 test2.rep FROM Test test2 WHERE test2.exercise = test1.exercise AND test2.rep = test1.rep ORDER BY kg DESC) GROUP BY exercise, rep

Loop over those results and display them.

answered Feb 10 '17 at 15:32

Timothy Stepanski

1,186
7
21

resulted in this `There was an error parsing the query. [ Token line number = 1,Token line offset = 66,Token in error = SELECT ]` – Pontus Svedberg Feb 10 '17 at 17:53

SQL: Improve Select Query

2 Answers2