Select MAX row (latest event) for each user (two table join with conditions)

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I'm new to Postgres and having an issue with what seems like a simple task.. I've read through many examples and a lot of them occur with just a single table so I'm here for help!

I have a users table and an events table.

Users consists of user_id, name, user_type

Events consists of event_id, user_id, event_name, event_type, event_date

I want to get the latest events for each user where user_type = full and event_type = paid

I've tried the following but Postgres tells me "ERROR: Column "e.event_name" must appear in the GROUP BY clause or be used in an aggregate function"

select 

  u.user_id, 

  u.user_type, 

  max(e.event_id), 

  e.event_name 

from 

  users u 

  join events e on u.user_id = e.user_id 

where 

  u.user_type = 'full' 

  and e.event_type = 'paid' 

group by 

  u.user_id

Note: Some solutions mentioned DISTINCT ON but system I'm using doesn't think that's valid SQL.

edited Nov 24 '18 at 10:16

Mureinik

188k22142207

asked Nov 24 '18 at 10:03

Alex

6671919

2

If the system you are using does not support distinct on then you are not using Postgres

– a_horse_with_no_name
Nov 24 '18 at 10:11

Thanks it's an analytics system with it's own error checking, they're definitely on postgres but it's throwing an error :-|

– Alex
Nov 24 '18 at 10:18

This is a faq & a tag: "greatest n per group". Why are you trying to do this, it doesn't make sense. Group by partitions the table's rows into a group of rows per subtuple value then you can select group by columns and/or new values that are a function of the group. What do you think selecting a non-aggregated non-grouping column means? It has a possibly different value for every row in a partition.

– philipxy
Nov 24 '18 at 10:54

This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.

– philipxy
Nov 24 '18 at 10:55

add a comment |

I'm new to Postgres and having an issue with what seems like a simple task.. I've read through many examples and a lot of them occur with just a single table so I'm here for help!

I have a users table and an events table.

Users consists of user_id, name, user_type

Events consists of event_id, user_id, event_name, event_type, event_date

I want to get the latest events for each user where user_type = full and event_type = paid

I've tried the following but Postgres tells me "ERROR: Column "e.event_name" must appear in the GROUP BY clause or be used in an aggregate function"

select 

  u.user_id, 

  u.user_type, 

  max(e.event_id), 

  e.event_name 

from 

  users u 

  join events e on u.user_id = e.user_id 

where 

  u.user_type = 'full' 

  and e.event_type = 'paid' 

group by 

  u.user_id

Note: Some solutions mentioned DISTINCT ON but system I'm using doesn't think that's valid SQL.

edited Nov 24 '18 at 10:16

Mureinik

188k22142207

asked Nov 24 '18 at 10:03

Alex

6671919

2

If the system you are using does not support distinct on then you are not using Postgres

– a_horse_with_no_name
Nov 24 '18 at 10:11

Thanks it's an analytics system with it's own error checking, they're definitely on postgres but it's throwing an error :-|

– Alex
Nov 24 '18 at 10:18

This is a faq & a tag: "greatest n per group". Why are you trying to do this, it doesn't make sense. Group by partitions the table's rows into a group of rows per subtuple value then you can select group by columns and/or new values that are a function of the group. What do you think selecting a non-aggregated non-grouping column means? It has a possibly different value for every row in a partition.

– philipxy
Nov 24 '18 at 10:54

This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.

– philipxy
Nov 24 '18 at 10:55

add a comment |

I'm new to Postgres and having an issue with what seems like a simple task.. I've read through many examples and a lot of them occur with just a single table so I'm here for help!

I have a users table and an events table.

Users consists of user_id, name, user_type

Events consists of event_id, user_id, event_name, event_type, event_date

I want to get the latest events for each user where user_type = full and event_type = paid

I've tried the following but Postgres tells me "ERROR: Column "e.event_name" must appear in the GROUP BY clause or be used in an aggregate function"

select 

  u.user_id, 

  u.user_type, 

  max(e.event_id), 

  e.event_name 

from 

  users u 

  join events e on u.user_id = e.user_id 

where 

  u.user_type = 'full' 

  and e.event_type = 'paid' 

group by 

  u.user_id

Note: Some solutions mentioned DISTINCT ON but system I'm using doesn't think that's valid SQL.

edited Nov 24 '18 at 10:16

Mureinik

188k22142207

asked Nov 24 '18 at 10:03

Alex

6671919

I'm new to Postgres and having an issue with what seems like a simple task.. I've read through many examples and a lot of them occur with just a single table so I'm here for help!

I have a users table and an events table.

Users consists of user_id, name, user_type

Events consists of event_id, user_id, event_name, event_type, event_date

I want to get the latest events for each user where user_type = full and event_type = paid

I've tried the following but Postgres tells me "ERROR: Column "e.event_name" must appear in the GROUP BY clause or be used in an aggregate function"

select 

  u.user_id, 

  u.user_type, 

  max(e.event_id), 

  e.event_name 

from 

  users u 

  join events e on u.user_id = e.user_id 

where 

  u.user_type = 'full' 

  and e.event_type = 'paid' 

group by 

  u.user_id

Note: Some solutions mentioned DISTINCT ON but system I'm using doesn't think that's valid SQL.

sql postgresql join select greatest-n-per-group

edited Nov 24 '18 at 10:16

Mureinik

188k22142207

asked Nov 24 '18 at 10:03

Alex

6671919

edited Nov 24 '18 at 10:16

Mureinik

188k22142207

asked Nov 24 '18 at 10:03

Alex

6671919

edited Nov 24 '18 at 10:16

Mureinik

188k22142207

edited Nov 24 '18 at 10:16

Mureinik

188k22142207

edited Nov 24 '18 at 10:16

Mureinik

188k22142207

asked Nov 24 '18 at 10:03

Alex

6671919

asked Nov 24 '18 at 10:03

Alex

6671919

asked Nov 24 '18 at 10:03

Alex

6671919

2

If the system you are using does not support distinct on then you are not using Postgres

– a_horse_with_no_name
Nov 24 '18 at 10:11

Thanks it's an analytics system with it's own error checking, they're definitely on postgres but it's throwing an error :-|

– Alex
Nov 24 '18 at 10:18

This is a faq & a tag: "greatest n per group". Why are you trying to do this, it doesn't make sense. Group by partitions the table's rows into a group of rows per subtuple value then you can select group by columns and/or new values that are a function of the group. What do you think selecting a non-aggregated non-grouping column means? It has a possibly different value for every row in a partition.

– philipxy
Nov 24 '18 at 10:54

This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.

– philipxy
Nov 24 '18 at 10:55

add a comment |

2

If the system you are using does not support distinct on then you are not using Postgres

– a_horse_with_no_name
Nov 24 '18 at 10:11

Thanks it's an analytics system with it's own error checking, they're definitely on postgres but it's throwing an error :-|

– Alex
Nov 24 '18 at 10:18

This is a faq & a tag: "greatest n per group". Why are you trying to do this, it doesn't make sense. Group by partitions the table's rows into a group of rows per subtuple value then you can select group by columns and/or new values that are a function of the group. What do you think selecting a non-aggregated non-grouping column means? It has a possibly different value for every row in a partition.

– philipxy
Nov 24 '18 at 10:54

This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.

– philipxy
Nov 24 '18 at 10:55

If the system you are using does not support distinct on then you are not using Postgres

– a_horse_with_no_name
Nov 24 '18 at 10:11

Thanks it's an analytics system with it's own error checking, they're definitely on postgres but it's throwing an error :-|

– Alex
Nov 24 '18 at 10:18

This is a faq & a tag: "greatest n per group". Why are you trying to do this, it doesn't make sense. Group by partitions the table's rows into a group of rows per subtuple value then you can select group by columns and/or new values that are a function of the group. What do you think selecting a non-aggregated non-grouping column means? It has a possibly different value for every row in a partition.

– philipxy
Nov 24 '18 at 10:54

This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on Minimal, Complete, and Verifiable example.

– philipxy
Nov 24 '18 at 10:55

add a comment |

2 Answers
2

active

oldest

votes

As you've seen, you can't mix row and aggerate functions like that.
One common approach is to use the row_number window function to sort events by their date (per user), and then just take the first ones:

SELECT *

FROM   (SELECT *,

               ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY event_date DESC) AS rn

        FROM   users u

        JOIN   events e ON u.user_id = e.user_id

        WHERE  user_type = 'full' AND

               event_type = 'paid') t

WHERE  rn = 1

answered Nov 24 '18 at 10:13

Mureinik

188k22142207

add a comment |

The most efficient method in Postgres is often to use distinct on:

select distinct on (u.user_id) u.user_id, u.user_type, 

       e.event_id, e.event_name 

from users u join

     events e

     on u.user_id = e.user_id 

where u.user_type = 'full' and

      e.event_type = 'paid' 

order by u.user_id, e.event_date desc;

answered Nov 24 '18 at 12:18

Gordon Linoff

799k37320426

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53457069%2fselect-max-row-latest-event-for-each-user-two-table-join-with-conditions%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

SELECT *

FROM   (SELECT *,

               ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY event_date DESC) AS rn

        FROM   users u

        JOIN   events e ON u.user_id = e.user_id

        WHERE  user_type = 'full' AND

               event_type = 'paid') t

WHERE  rn = 1

answered Nov 24 '18 at 10:13

Mureinik

188k22142207

add a comment |

SELECT *

FROM   (SELECT *,

               ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY event_date DESC) AS rn

        FROM   users u

        JOIN   events e ON u.user_id = e.user_id

        WHERE  user_type = 'full' AND

               event_type = 'paid') t

WHERE  rn = 1

answered Nov 24 '18 at 10:13

Mureinik

188k22142207

add a comment |

SELECT *

FROM   (SELECT *,

               ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY event_date DESC) AS rn

        FROM   users u

        JOIN   events e ON u.user_id = e.user_id

        WHERE  user_type = 'full' AND

               event_type = 'paid') t

WHERE  rn = 1

answered Nov 24 '18 at 10:13

Mureinik

188k22142207

SELECT *

FROM   (SELECT *,

               ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY event_date DESC) AS rn

        FROM   users u

        JOIN   events e ON u.user_id = e.user_id

        WHERE  user_type = 'full' AND

               event_type = 'paid') t

WHERE  rn = 1

answered Nov 24 '18 at 10:13

Mureinik

188k22142207

answered Nov 24 '18 at 10:13

Mureinik

188k22142207

answered Nov 24 '18 at 10:13

Mureinik

188k22142207

answered Nov 24 '18 at 10:13

Mureinik

188k22142207

add a comment |

The most efficient method in Postgres is often to use distinct on:

select distinct on (u.user_id) u.user_id, u.user_type, 

       e.event_id, e.event_name 

from users u join

     events e

     on u.user_id = e.user_id 

where u.user_type = 'full' and

      e.event_type = 'paid' 

order by u.user_id, e.event_date desc;

answered Nov 24 '18 at 12:18

Gordon Linoff

799k37320426

add a comment |

The most efficient method in Postgres is often to use distinct on:

select distinct on (u.user_id) u.user_id, u.user_type, 

       e.event_id, e.event_name 

from users u join

     events e

     on u.user_id = e.user_id 

where u.user_type = 'full' and

      e.event_type = 'paid' 

order by u.user_id, e.event_date desc;

answered Nov 24 '18 at 12:18

Gordon Linoff

799k37320426

add a comment |

The most efficient method in Postgres is often to use distinct on:

select distinct on (u.user_id) u.user_id, u.user_type, 

       e.event_id, e.event_name 

from users u join

     events e

     on u.user_id = e.user_id 

where u.user_type = 'full' and

      e.event_type = 'paid' 

order by u.user_id, e.event_date desc;

answered Nov 24 '18 at 12:18

Gordon Linoff

799k37320426

The most efficient method in Postgres is often to use distinct on:

select distinct on (u.user_id) u.user_id, u.user_type, 

       e.event_id, e.event_name 

from users u join

     events e

     on u.user_id = e.user_id 

where u.user_type = 'full' and

      e.event_type = 'paid' 

order by u.user_id, e.event_date desc;

answered Nov 24 '18 at 12:18

Gordon Linoff

799k37320426

answered Nov 24 '18 at 12:18

Gordon Linoff

799k37320426

answered Nov 24 '18 at 12:18

Gordon Linoff

799k37320426

answered Nov 24 '18 at 12:18

Gordon Linoff

799k37320426

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Wsrtjtyk