Select first and last row based on each user id mysql
0
I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead.
Input:
id timestamp event
10096 1545136190 songs
10096 1545136202 tv
10096 1545136239 book
10096 1545136244 eat
10096 1545136252 travel
10096 1545136271 tv
10096 1545136275 laptop
10096 1545313396 sleep
10105 1545464444 book
10105 1545464455 eat
10105 1545464457 travel
10105 1545464460 tv
10105 1545464605 book
10105 1546740388 sleep
10213 1545134176 tv
10213 1545134180 computer
10213 1545134221 play
10213 1545134238 book
10213 1545134267 eat
10213 1545146190 travel
10213 1545146212 play
10213 1545623861 sleep
1053 1545131279 sleep
Expected Output:
id timestamp event
10096 1545136190 songs
10096 1545313396 sleep
10105 1545464444 book
10105 1546740388 sleep
10213 1545134176 tv
10213 1545623861 sleep
1053 1545131279 sleep
tried doing this but not getting exactly what i want:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as RowNum,
id,
timestamp,
event
FROM
`table`
) MyData
WHERE RowNum < 3
mysql google-bigquery
asked 4 mins ago
NrjNrj
52
add a comment |
0
I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead.
Input:
id timestamp event
10096 1545136190 songs
10096 1545136202 tv
10096 1545136239 book
10096 1545136244 eat
10096 1545136252 travel
10096 1545136271 tv
10096 1545136275 laptop
10096 1545313396 sleep
10105 1545464444 book
10105 1545464455 eat
10105 1545464457 travel
10105 1545464460 tv
10105 1545464605 book
10105 1546740388 sleep
10213 1545134176 tv
10213 1545134180 computer
10213 1545134221 play
10213 1545134238 book
10213 1545134267 eat
10213 1545146190 travel
10213 1545146212 play
10213 1545623861 sleep
1053 1545131279 sleep
Expected Output:
id timestamp event
10096 1545136190 songs
10096 1545313396 sleep
10105 1545464444 book
10105 1546740388 sleep
10213 1545134176 tv
10213 1545623861 sleep
1053 1545131279 sleep
tried doing this but not getting exactly what i want:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as RowNum,
id,
timestamp,
event
FROM
`table`
) MyData
WHERE RowNum < 3
mysql google-bigquery
asked 4 mins ago
NrjNrj
52
add a comment |
0
0
0
I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead.
Input:
id timestamp event
10096 1545136190 songs
10096 1545136202 tv
10096 1545136239 book
10096 1545136244 eat
10096 1545136252 travel
10096 1545136271 tv
10096 1545136275 laptop
10096 1545313396 sleep
10105 1545464444 book
10105 1545464455 eat
10105 1545464457 travel
10105 1545464460 tv
10105 1545464605 book
10105 1546740388 sleep
10213 1545134176 tv
10213 1545134180 computer
10213 1545134221 play
10213 1545134238 book
10213 1545134267 eat
10213 1545146190 travel
10213 1545146212 play
10213 1545623861 sleep
1053 1545131279 sleep
Expected Output:
id timestamp event
10096 1545136190 songs
10096 1545313396 sleep
10105 1545464444 book
10105 1546740388 sleep
10213 1545134176 tv
10213 1545623861 sleep
1053 1545131279 sleep
tried doing this but not getting exactly what i want:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as RowNum,
id,
timestamp,
event
FROM
`table`
) MyData
WHERE RowNum < 3
mysql google-bigquery
asked 4 mins ago
NrjNrj
52
I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead.
Input:
id timestamp event
10096 1545136190 songs
10096 1545136202 tv
10096 1545136239 book
10096 1545136244 eat
10096 1545136252 travel
10096 1545136271 tv
10096 1545136275 laptop
10096 1545313396 sleep
10105 1545464444 book
10105 1545464455 eat
10105 1545464457 travel
10105 1545464460 tv
10105 1545464605 book
10105 1546740388 sleep
10213 1545134176 tv
10213 1545134180 computer
10213 1545134221 play
10213 1545134238 book
10213 1545134267 eat
10213 1545146190 travel
10213 1545146212 play
10213 1545623861 sleep
1053 1545131279 sleep
Expected Output:
id timestamp event
10096 1545136190 songs
10096 1545313396 sleep
10105 1545464444 book
10105 1546740388 sleep
10213 1545134176 tv
10213 1545623861 sleep
1053 1545131279 sleep
tried doing this but not getting exactly what i want:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) as RowNum,
id,
timestamp,
event
FROM
`table`
) MyData
WHERE RowNum < 3
mysql google-bigquery
mysql google-bigquery
asked 4 mins ago
NrjNrj
52
asked 4 mins ago
NrjNrj
52
asked 4 mins ago
NrjNrj
52
asked 4 mins ago
NrjNrj
52
asked 4 mins ago
NrjNrj
52
52
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227736%2fselect-first-and-last-row-based-on-each-user-id-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f227736%2fselect-first-and-last-row-based-on-each-user-id-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
