Including an attribute in SELECT without having to include it in GROUP BY

Question

I have a list of players who have hit Grand Slams this season, so far.

___________________________________________________
| Player Name | dateOfGrandSlam | distance | home |
---------------------------------------------------
|  Griffin    |    9-14-2013   |    413   | true |
|  Griffin    |    10-1-2013   |    371   | false|
|  Simpson    |    5-15-2013   |    413   | true |
|   Reid      |    7-1-2013    |    362   | true |
|   Reid      |    7-4-2013    |    363   | true |
|   Reid      |    9-28-2013   |    388   | true |
|  Peavis     |    8-14-2013   |    466   | false|

I want to get a list of players whose most recent grandslam was at home. If their most recent grandslam wasn't at home, I don't want them to show up on my list. This means, I need to select the player and group by the player and select the max date from that group. In that list, I also have to include home/away information so I can pick out the ones that were not at home.

However I'm having a problem. Because in order to select the attribute home, I also need to include home in the GROUP BY clause. For example:

SELECT playerName, MAX(date), distance, home 
FROM grandslams 
GROUP BY playerName, distance, home

The problem is that this returns a table with the most recent home and the most recent away grandslams.

| Player Name | dateOfGrandSlam | distance | home |
---------------------------------------------------
|  Griffin    |    9-14-2013   |    413   | true |
|  Griffin    |    10-1-2013   |    371   | false|
|  Simpson    |    5-15-2013   |    413   | true |
|   Reid      |    9-28-2013   |    388   | true |

This is not what I want - I want ONLY the most recent home grandslams IF there were no away grandslams more recently.

I want this result:

___________________________________________________
| Player Name | dateOfGrandSlam | distance | home |
---------------------------------------------------
|  Simpson    |    5-15-2013   |    413   | true |
|   Reid      |    9-28-2013   |    388   | true |

Essentially I need a way to perform the query that just gets me the most recent grandslam per player, tacks on the home attribute (without having to group by it so I don't get his most recent home and his most recent away) and then can be easily filtered in an outer query.

In other words, I need to get the result from

SELECT playerName, MAX(date), distance
FROM grandSlams
GROUP BY playerName, distance

and attach the home attribute to it.

score 3 · Answer 1 · answered Dec 16 '13 at 15:38

3

You can find the most recent grand slam for each player by using the row_number() function to enumerate the grand slams for each player, ordered by the date in descending order. To choose the most recent, choose the one where the value is 1. Then add the condition on the grand slam being from home:

select gs.*
from (select gs.*,
             row_number() over (partition by PlayerName
                                order by dateOfGrandSlam desc) as seqnum
      from GrandSlams gs
     ) gs
where seqnum = 1 and home = true;

answered Dec 16 '13 at 15:38

Gordon Linoff

1,242,037
58
646
786

Is there a more conventional way? – CodyBugstein Dec 16 '13 at 15:54
1

@Imray: that *is* the "conventional" way (it's standard ANSI SQL) – Dec 16 '13 at 16:04
@a_horse_with_no_name: Standard SQL is a "nice to have". Simplicity and performance are more important, IMO. Especially, since every RDBMS violates the standard anyway and also because the standard is rather silly in quite a few places (not here, mind you). – Erwin Brandstetter Dec 16 '13 at 19:16

mrras · Answer 2 · 2013-12-16T16:04:50.383

You can first find all of the last home runs, then join them to the original table.

SELECT g.* from (SELECT playerName, MAX(date) date
FROM grandslams 
GROUP BY playerName) a
NATURAL JOIN grandslams g
WHERE g.home = true;

If you are not a fan of natural join (for ansi's sake) you can always use

JOIN grandslams g on g.playerName = a.playerName and g.date = a.date

instead of the natural join. this would make the query ansi.

Anon · Answer 3 · 2013-12-16T16:51:37.403

0

Break the problem down.

1) Build a query that gets you the players whose last slam was at home. I.e., their most recent home slam was their most recent slam.

SELECT
  playerName,
  MAX(dateOfGrandSlam) mostRecentGrandSlam
FROM grandSlams
GROUP BY playerName
HAVING MAX(CASE home WHEN 'true' THEN dateOfGrandSlam END) = MAX(dateOfGrandSlam)

2) Use that query to filter the original table to just the rows you want:

SELECT a.playerName, a.dateOfGrandSlam, a.distance, a.home
FROM grandSlams a
INNER JOIN (
  SELECT
    playerName,
    MAX(dateOfGrandSlam) mostRecentGrandSlam
  FROM grandSlams
  GROUP BY playerName
  HAVING MAX(CASE home WHEN 'true' THEN dateOfGrandSlam END) = MAX(dateOfGrandSlam)
) b ON (a.playerName = b.playerName AND a.dateOfGrandSlam = b.mostRecentGrandSlam)

edited Dec 16 '13 at 16:51

answered Dec 16 '13 at 16:12

Anon

10,660
1
29
31

The OPs code cleanly inserts into [sqlfiddle.com](http://sqlfiddle.com/#!15/a381e/1), please check your answer before posting it. – mabi Dec 16 '13 at 16:25
It does, once you added the `group by`. And yeah, never known you could use `CASE` in that context. Really wondrous. – mabi Dec 16 '13 at 17:02
@mabi: If you like the CASE statement, consider this untangled form: `CASE WHEN home THEN dateOfGrandSlam END`. Postgres (unlike some other RDBMS) supports a proper `boolean` type, that can be used accordingly. – Erwin Brandstetter Dec 16 '13 at 19:04

score 0 · Answer 4 · edited May 23 '17 at 11:56

The basic query would be simplest and fastest with DISTINCT ON, which is a Postgres specific extension of the SQL standard DISTINCT:

SELECT DISTINCT ON (playername)
       playername, dateofgrandslam, distance, home 
FROM   grandslams 
GROUP  BY playername, dateofgrandslamDESC;

Compare performance with EXPLAIN ANALYZE. Detailed explanation in this related answer:
Select first row in each GROUP BY group?

Use a subquery to eliminate cases with the most recent grandslam not at home:

SELECT * FROM (
    SELECT DISTINCT ON (playername)
           playername, dateofgrandslam, distance, home 
    FROM   grandslams 
    GROUP  BY playername, dateofgrandslam DESC
   ) sub
WHERE  home;

Another hot contender for the performance crown (depending on what you need exactly), and purely standard SQL: a NOT EXISTS anti-semi-join. Also simpler than it sounds:

SELECT playername, dateofgrandslam, distance, home 
FROM   grandslams g
WHERE  home
AND NOT EXISTS (
   SELECT 1
   FROM   grandslams g1
   WHERE  g1.playername = g.playername
   AND    g1.dateofgrandslam > g.dateofgrandslam
   );

Aside: Using the lower case for dateofgrandslam, since unquoted identifiers are cast to lower case anyway in Postgres.

mw90 · Answer 5 · 2013-12-16T16:01:25.987

-1

Try this:

SELECT playerName, MAX(date), distance, home
FROM grandslams
GROUP BY playerName, distance, home
WHERE home='true'

you may need to use " or ' depending on what type of SQL you are using.

However, I am a little confused by your question due to this:

"I want to get a list of players whose most recent grandslam was at home. If their most recent grandslam wasn't at home, I don't want them to show up on my list." and then "This is not what I want - I want ONLY the most recent home grandslams IF there were no away grandslams more recently."

Are you looking for the most recent grandslam in general? or just the most recent home grandslam?

edited Dec 16 '13 at 16:01

answered Dec 16 '13 at 15:54

mw90

132
3
12

I'm looking for the most recent home grandslam but only if there were no even more recent away grandslams. – CodyBugstein Dec 16 '13 at 16:01
I noticed I had missed the "home" in the group by, but other then that no I did not try it as I'm not going to attempt to create those tables for the purpose of testing a query – mw90 Dec 16 '13 at 16:03

Including an attribute in SELECT without having to include it in GROUP BY

5 Answers5