CODEWITHSUNDEEP
sqlms-accessaggregate-functions
ALisboa
ALisboa

Reputation: 484

SQL group by a certain criterion, then order inside the group by different criteria

What is a way to write a SQL query for Access that will group by a certain criterion, then order inside the group by different criteria, that were not used to group by? For example, suppose I have a table called tblObjects, with columns:

autoID name color shape weight height

I want to end up with a list of objects that does not repeat color, and that selects the tallest object in each color group. If there are objects of same color, same height, then select the heaviest.

I figured I can group by color, order inside the group by height and then weight and then truncate at 1. That should eliminate all color repetition and pick the one "representative" of each color according to my criteria.

After researching here I was able to group by with the code:

    SELECT autoID,  name,  color,  shape,  weight,  height
    FROM tblObjects INNER JOIN( SELECT
         color, COUNT(*) AS NumberSameColor
         FROM tblObjects
         GROUP BY color) AS tblGroups ON tblObjects.color = tblGroups.color;

but when I try to ORDER BY height, weight I get an error saying that the columns I am trying to use to order by is not part of the aggregation. If I put "order by" statement at the end of the query, it undoes the groups in favor of the ordering. How can I get around this limitation to get the results that I need?

I see that I don't actually need the count, so do I even need to aggregate? Am I missing a significantly simpler way to approach this?

Upvotes: 0

Views: 237

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

This should work:

SELECT o.autoID,  o.name,  o.color,  o.shape,  o.weight, o.height,
       c.NumberSameColor -- added so the `JOIN` makes sense
FROM tblObjects as o INNER JOIN
     (SELECT color, COUNT(*) AS NumberSameColor
      FROM tblObjects
      GROUP BY color
     ) AS c
     ON o.color = c.color
ORDER BY o.weight, o.height;

I just added the table aliases to remove ambiguity about where the columns come from.

EDIT:

Hmmm, I think you want this:

select o.*
from tblObjects as o
where o.height = (select max(o2.height)
                  from tblObjects as o2
                  where o2.color = o.color
                 );

There is no group by at all. This says to choose all objects whose height is the largest value for their color. Of course, there could be more than one for a given color. For that:

select o.*
from tblObjects as o
where o.autoid = (select top 1 o2.autoid
                  from tblObjects as o2
                  where o2.color = o.color
                  order by o2.height desc, o2.weight desc, o2.autoid
                 );

Upvotes: 2

Related Questions