SQL statement for minimum time and minimum ID?
1
I'm really new to SQL, and I've encountered the problem as shown below:
Imagine this person has made payments at the respective timings, with the respective IDs.
I would like to find out the amount of the earliest payment. If there are multiple entries for the earliest date, I would want to choose the entry with the smallest payment ID.
In this image the answer is simple, but what if the earliest payment was done on 5 Apr?
After removing the blank spaces for the titles, and after searching high and low for the answers, I wrote this:
SELECT PaymentID, PaymentAmt
FROM
(
SELECT
MIN(s.PaymentID),
s.PaymentDate,
s.PaymentAmt,
ROW_NUMBER() OVER(PARTITION BY s.PaymentID ORDER BY s.PaymentDate) rn
FROM sheetName s
GROUP BY s.userid, s.PaymentDate, s.PaymentAmt
) t
WHERE rn = 1;
And it still doesn't work.
Could anyone help me with this?
EDIT: As written in my reply to Mark, what if I am looking into a group of people instead? So there could be another person with a different "userID" with their set of payment IDs and etc. If I want to find out the earliest payment amount for each person, what would be a good solution?
sql
sql-server ssms asked Nov 22 '18 at 23:42
TylerTyler
83
add a comment |
1
I'm really new to SQL, and I've encountered the problem as shown below:
Imagine this person has made payments at the respective timings, with the respective IDs.
I would like to find out the amount of the earliest payment. If there are multiple entries for the earliest date, I would want to choose the entry with the smallest payment ID.
In this image the answer is simple, but what if the earliest payment was done on 5 Apr?
After removing the blank spaces for the titles, and after searching high and low for the answers, I wrote this:
SELECT PaymentID, PaymentAmt
FROM
(
SELECT
MIN(s.PaymentID),
s.PaymentDate,
s.PaymentAmt,
ROW_NUMBER() OVER(PARTITION BY s.PaymentID ORDER BY s.PaymentDate) rn
FROM sheetName s
GROUP BY s.userid, s.PaymentDate, s.PaymentAmt
) t
WHERE rn = 1;
And it still doesn't work.
Could anyone help me with this?
EDIT: As written in my reply to Mark, what if I am looking into a group of people instead? So there could be another person with a different "userID" with their set of payment IDs and etc. If I want to find out the earliest payment amount for each person, what would be a good solution?
sql
sql-server ssms asked Nov 22 '18 at 23:42
TylerTyler
83
If you need to partition by something like "userID", use Squirrel's answer but addPARTITION BY userIDin the row number window function.
– ZLK
Nov 23 '18 at 0:41
That second is another question - especially since you will want new/different answers, post each table and what you have tried. Post all the details (tables) and what you have tried in that new question
– Mark Schultheiss
Nov 23 '18 at 14:24
add a comment |
1
1
1
I'm really new to SQL, and I've encountered the problem as shown below:
Imagine this person has made payments at the respective timings, with the respective IDs.
I would like to find out the amount of the earliest payment. If there are multiple entries for the earliest date, I would want to choose the entry with the smallest payment ID.
In this image the answer is simple, but what if the earliest payment was done on 5 Apr?
After removing the blank spaces for the titles, and after searching high and low for the answers, I wrote this:
SELECT PaymentID, PaymentAmt
FROM
(
SELECT
MIN(s.PaymentID),
s.PaymentDate,
s.PaymentAmt,
ROW_NUMBER() OVER(PARTITION BY s.PaymentID ORDER BY s.PaymentDate) rn
FROM sheetName s
GROUP BY s.userid, s.PaymentDate, s.PaymentAmt
) t
WHERE rn = 1;
And it still doesn't work.
Could anyone help me with this?
EDIT: As written in my reply to Mark, what if I am looking into a group of people instead? So there could be another person with a different "userID" with their set of payment IDs and etc. If I want to find out the earliest payment amount for each person, what would be a good solution?
sql
sql-server ssms asked Nov 22 '18 at 23:42
TylerTyler
83
I'm really new to SQL, and I've encountered the problem as shown below:
Imagine this person has made payments at the respective timings, with the respective IDs.
I would like to find out the amount of the earliest payment. If there are multiple entries for the earliest date, I would want to choose the entry with the smallest payment ID.
In this image the answer is simple, but what if the earliest payment was done on 5 Apr?
After removing the blank spaces for the titles, and after searching high and low for the answers, I wrote this:
SELECT PaymentID, PaymentAmt
FROM
(
SELECT
MIN(s.PaymentID),
s.PaymentDate,
s.PaymentAmt,
ROW_NUMBER() OVER(PARTITION BY s.PaymentID ORDER BY s.PaymentDate) rn
FROM sheetName s
GROUP BY s.userid, s.PaymentDate, s.PaymentAmt
) t
WHERE rn = 1;
And it still doesn't work.
Could anyone help me with this?
EDIT: As written in my reply to Mark, what if I am looking into a group of people instead? So there could be another person with a different "userID" with their set of payment IDs and etc. If I want to find out the earliest payment amount for each person, what would be a good solution?
sql
sql-server ssms sql
sql-server ssms asked Nov 22 '18 at 23:42
TylerTyler
83
asked Nov 22 '18 at 23:42
TylerTyler
83
edited Nov 23 '18 at 0:18
Tyler
asked Nov 22 '18 at 23:42
TylerTyler
83
asked Nov 22 '18 at 23:42
TylerTyler
83
asked Nov 22 '18 at 23:42
TylerTyler
83
83
If you need to partition by something like "userID", use Squirrel's answer but addPARTITION BY userIDin the row number window function.
– ZLK
Nov 23 '18 at 0:41
That second is another question - especially since you will want new/different answers, post each table and what you have tried. Post all the details (tables) and what you have tried in that new question
– Mark Schultheiss
Nov 23 '18 at 14:24
add a comment |
If you need to partition by something like "userID", use Squirrel's answer but addPARTITION BY userIDin the row number window function.
– ZLK
Nov 23 '18 at 0:41
That second is another question - especially since you will want new/different answers, post each table and what you have tried. Post all the details (tables) and what you have tried in that new question
– Mark Schultheiss
Nov 23 '18 at 14:24
If you need to partition by something like "userID", use Squirrel's answer but add
PARTITION BY userID in the row number window function.– ZLK
Nov 23 '18 at 0:41
If you need to partition by something like "userID", use Squirrel's answer but add
PARTITION BY userID in the row number window function.– ZLK
Nov 23 '18 at 0:41
That second is another question - especially since you will want new/different answers, post each table and what you have tried. Post all the details (tables) and what you have tried in that new question
– Mark Schultheiss
Nov 23 '18 at 14:24
That second is another question - especially since you will want new/different answers, post each table and what you have tried. Post all the details (tables) and what you have tried in that new question
– Mark Schultheiss
Nov 23 '18 at 14:24
add a comment |
3 Answers
3
active
oldest
votes
4
Why make it complicated?
SELECT TOP 1 PaymentID, PaymentAmt
FROM sheetName
ORDER BY s.PaymentDate ASC, s.PaymentID ASC
answered Nov 23 '18 at 0:05
Mark SchultheissMark Schultheiss
24.1k85181
NOTE: there is no way with the data provided to join with a person but for the data you provided this should work
– Mark Schultheiss
Nov 23 '18 at 0:06
IF you just need a date, add that WHERE clause.
– Mark Schultheiss
Nov 23 '18 at 0:07
Thanks Mark! Here's another question: What if I am actually dealing with multiple people? So let's say I add another column "user ID". And I want to find out the earliest payment amount for each person. In that case, SELECT TOP 1 doesn't seem to work anymore. Do you have a solution that takes this into account? It's an extension to the question, would be glad if you can help out on this one too!
– Tyler
Nov 23 '18 at 0:13
@MarkSchultheiss Btw, thanks a lot, I didn't know that you could "ORDER BY" more than one column.
– Tyler
Nov 23 '18 at 0:25
re: another column/table - that is another question regarding JOIN - please post another question for that as this one has several answers now. Please then give the layout (columns) of each table along with what you have tried.
– Mark Schultheiss
Nov 23 '18 at 14:17
add a comment |
0
just use row_number() with order by PaymentDate, PaymentID
select *
from
(
select *, rn = row_number() over (order by PaymentDate, PaymentID)
from sheetName
) d
where d.rn = 1
answered Nov 23 '18 at 0:04
SquirrelSquirrel
11.8k22127
add a comment |
0
For multiple users, perhaps something like this -
select s.userid, s.paymentid, s.paymentdate, s.paymentamt
from sheetname s inner join
(
select t.userid, min(t.paymentid) as minid
from sheetname t inner join
(
select u.userid, min(u.paymentdate) as mindate
from sheetname u
group by u.userid
) v on t.userid = v.userid and t.paymentdate = v.mindate
group by t.userid
) w on s.userid = w.userid and s.paymentid = w.minid
answered Nov 22 '18 at 23:58
Lee MacLee Mac
4,19431541
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');
});
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%2fstackoverflow.com%2fquestions%2f53439146%2fsql-statement-for-minimum-time-and-minimum-id%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
Why make it complicated?
SELECT TOP 1 PaymentID, PaymentAmt
FROM sheetName
ORDER BY s.PaymentDate ASC, s.PaymentID ASC
answered Nov 23 '18 at 0:05
Mark SchultheissMark Schultheiss
24.1k85181
NOTE: there is no way with the data provided to join with a person but for the data you provided this should work
– Mark Schultheiss
Nov 23 '18 at 0:06
IF you just need a date, add that WHERE clause.
– Mark Schultheiss
Nov 23 '18 at 0:07
Thanks Mark! Here's another question: What if I am actually dealing with multiple people? So let's say I add another column "user ID". And I want to find out the earliest payment amount for each person. In that case, SELECT TOP 1 doesn't seem to work anymore. Do you have a solution that takes this into account? It's an extension to the question, would be glad if you can help out on this one too!
– Tyler
Nov 23 '18 at 0:13
@MarkSchultheiss Btw, thanks a lot, I didn't know that you could "ORDER BY" more than one column.
– Tyler
Nov 23 '18 at 0:25
re: another column/table - that is another question regarding JOIN - please post another question for that as this one has several answers now. Please then give the layout (columns) of each table along with what you have tried.
– Mark Schultheiss
Nov 23 '18 at 14:17
add a comment |
4
Why make it complicated?
SELECT TOP 1 PaymentID, PaymentAmt
FROM sheetName
ORDER BY s.PaymentDate ASC, s.PaymentID ASC
answered Nov 23 '18 at 0:05
Mark SchultheissMark Schultheiss
24.1k85181
NOTE: there is no way with the data provided to join with a person but for the data you provided this should work
– Mark Schultheiss
Nov 23 '18 at 0:06
IF you just need a date, add that WHERE clause.
– Mark Schultheiss
Nov 23 '18 at 0:07
Thanks Mark! Here's another question: What if I am actually dealing with multiple people? So let's say I add another column "user ID". And I want to find out the earliest payment amount for each person. In that case, SELECT TOP 1 doesn't seem to work anymore. Do you have a solution that takes this into account? It's an extension to the question, would be glad if you can help out on this one too!
– Tyler
Nov 23 '18 at 0:13
@MarkSchultheiss Btw, thanks a lot, I didn't know that you could "ORDER BY" more than one column.
– Tyler
Nov 23 '18 at 0:25
re: another column/table - that is another question regarding JOIN - please post another question for that as this one has several answers now. Please then give the layout (columns) of each table along with what you have tried.
– Mark Schultheiss
Nov 23 '18 at 14:17
add a comment |
4
4
4
Why make it complicated?
SELECT TOP 1 PaymentID, PaymentAmt
FROM sheetName
ORDER BY s.PaymentDate ASC, s.PaymentID ASC
answered Nov 23 '18 at 0:05
Mark SchultheissMark Schultheiss
24.1k85181
Why make it complicated?
SELECT TOP 1 PaymentID, PaymentAmt
FROM sheetName
ORDER BY s.PaymentDate ASC, s.PaymentID ASC
answered Nov 23 '18 at 0:05
Mark SchultheissMark Schultheiss
24.1k85181
answered Nov 23 '18 at 0:05
Mark SchultheissMark Schultheiss
24.1k85181
answered Nov 23 '18 at 0:05
Mark SchultheissMark Schultheiss
24.1k85181
answered Nov 23 '18 at 0:05
Mark SchultheissMark Schultheiss
24.1k85181
24.1k85181
NOTE: there is no way with the data provided to join with a person but for the data you provided this should work
– Mark Schultheiss
Nov 23 '18 at 0:06
IF you just need a date, add that WHERE clause.
– Mark Schultheiss
Nov 23 '18 at 0:07
Thanks Mark! Here's another question: What if I am actually dealing with multiple people? So let's say I add another column "user ID". And I want to find out the earliest payment amount for each person. In that case, SELECT TOP 1 doesn't seem to work anymore. Do you have a solution that takes this into account? It's an extension to the question, would be glad if you can help out on this one too!
– Tyler
Nov 23 '18 at 0:13
@MarkSchultheiss Btw, thanks a lot, I didn't know that you could "ORDER BY" more than one column.
– Tyler
Nov 23 '18 at 0:25
re: another column/table - that is another question regarding JOIN - please post another question for that as this one has several answers now. Please then give the layout (columns) of each table along with what you have tried.
– Mark Schultheiss
Nov 23 '18 at 14:17
add a comment |
NOTE: there is no way with the data provided to join with a person but for the data you provided this should work
– Mark Schultheiss
Nov 23 '18 at 0:06
IF you just need a date, add that WHERE clause.
– Mark Schultheiss
Nov 23 '18 at 0:07
Thanks Mark! Here's another question: What if I am actually dealing with multiple people? So let's say I add another column "user ID". And I want to find out the earliest payment amount for each person. In that case, SELECT TOP 1 doesn't seem to work anymore. Do you have a solution that takes this into account? It's an extension to the question, would be glad if you can help out on this one too!
– Tyler
Nov 23 '18 at 0:13
@MarkSchultheiss Btw, thanks a lot, I didn't know that you could "ORDER BY" more than one column.
– Tyler
Nov 23 '18 at 0:25
re: another column/table - that is another question regarding JOIN - please post another question for that as this one has several answers now. Please then give the layout (columns) of each table along with what you have tried.
– Mark Schultheiss
Nov 23 '18 at 14:17
NOTE: there is no way with the data provided to join with a person but for the data you provided this should work
– Mark Schultheiss
Nov 23 '18 at 0:06
NOTE: there is no way with the data provided to join with a person but for the data you provided this should work
– Mark Schultheiss
Nov 23 '18 at 0:06
IF you just need a date, add that WHERE clause.
– Mark Schultheiss
Nov 23 '18 at 0:07
IF you just need a date, add that WHERE clause.
– Mark Schultheiss
Nov 23 '18 at 0:07
Thanks Mark! Here's another question: What if I am actually dealing with multiple people? So let's say I add another column "user ID". And I want to find out the earliest payment amount for each person. In that case, SELECT TOP 1 doesn't seem to work anymore. Do you have a solution that takes this into account? It's an extension to the question, would be glad if you can help out on this one too!
– Tyler
Nov 23 '18 at 0:13
Thanks Mark! Here's another question: What if I am actually dealing with multiple people? So let's say I add another column "user ID". And I want to find out the earliest payment amount for each person. In that case, SELECT TOP 1 doesn't seem to work anymore. Do you have a solution that takes this into account? It's an extension to the question, would be glad if you can help out on this one too!
– Tyler
Nov 23 '18 at 0:13
@MarkSchultheiss Btw, thanks a lot, I didn't know that you could "ORDER BY" more than one column.
– Tyler
Nov 23 '18 at 0:25
@MarkSchultheiss Btw, thanks a lot, I didn't know that you could "ORDER BY" more than one column.
– Tyler
Nov 23 '18 at 0:25
re: another column/table - that is another question regarding JOIN - please post another question for that as this one has several answers now. Please then give the layout (columns) of each table along with what you have tried.
– Mark Schultheiss
Nov 23 '18 at 14:17
re: another column/table - that is another question regarding JOIN - please post another question for that as this one has several answers now. Please then give the layout (columns) of each table along with what you have tried.
– Mark Schultheiss
Nov 23 '18 at 14:17
add a comment |
0
just use row_number() with order by PaymentDate, PaymentID
select *
from
(
select *, rn = row_number() over (order by PaymentDate, PaymentID)
from sheetName
) d
where d.rn = 1
answered Nov 23 '18 at 0:04
SquirrelSquirrel
11.8k22127
add a comment |
0
just use row_number() with order by PaymentDate, PaymentID
select *
from
(
select *, rn = row_number() over (order by PaymentDate, PaymentID)
from sheetName
) d
where d.rn = 1
answered Nov 23 '18 at 0:04
SquirrelSquirrel
11.8k22127
add a comment |
0
0
0
just use row_number() with order by PaymentDate, PaymentID
select *
from
(
select *, rn = row_number() over (order by PaymentDate, PaymentID)
from sheetName
) d
where d.rn = 1
answered Nov 23 '18 at 0:04
SquirrelSquirrel
11.8k22127
just use row_number() with order by PaymentDate, PaymentID
select *
from
(
select *, rn = row_number() over (order by PaymentDate, PaymentID)
from sheetName
) d
where d.rn = 1
answered Nov 23 '18 at 0:04
SquirrelSquirrel
11.8k22127
answered Nov 23 '18 at 0:04
SquirrelSquirrel
11.8k22127
answered Nov 23 '18 at 0:04
SquirrelSquirrel
11.8k22127
answered Nov 23 '18 at 0:04
SquirrelSquirrel
11.8k22127
11.8k22127
add a comment |
add a comment |
0
For multiple users, perhaps something like this -
select s.userid, s.paymentid, s.paymentdate, s.paymentamt
from sheetname s inner join
(
select t.userid, min(t.paymentid) as minid
from sheetname t inner join
(
select u.userid, min(u.paymentdate) as mindate
from sheetname u
group by u.userid
) v on t.userid = v.userid and t.paymentdate = v.mindate
group by t.userid
) w on s.userid = w.userid and s.paymentid = w.minid
answered Nov 22 '18 at 23:58
Lee MacLee Mac
4,19431541
add a comment |
0
For multiple users, perhaps something like this -
select s.userid, s.paymentid, s.paymentdate, s.paymentamt
from sheetname s inner join
(
select t.userid, min(t.paymentid) as minid
from sheetname t inner join
(
select u.userid, min(u.paymentdate) as mindate
from sheetname u
group by u.userid
) v on t.userid = v.userid and t.paymentdate = v.mindate
group by t.userid
) w on s.userid = w.userid and s.paymentid = w.minid
answered Nov 22 '18 at 23:58
Lee MacLee Mac
4,19431541
add a comment |
0
0
0
For multiple users, perhaps something like this -
select s.userid, s.paymentid, s.paymentdate, s.paymentamt
from sheetname s inner join
(
select t.userid, min(t.paymentid) as minid
from sheetname t inner join
(
select u.userid, min(u.paymentdate) as mindate
from sheetname u
group by u.userid
) v on t.userid = v.userid and t.paymentdate = v.mindate
group by t.userid
) w on s.userid = w.userid and s.paymentid = w.minid
answered Nov 22 '18 at 23:58
Lee MacLee Mac
4,19431541
For multiple users, perhaps something like this -
select s.userid, s.paymentid, s.paymentdate, s.paymentamt
from sheetname s inner join
(
select t.userid, min(t.paymentid) as minid
from sheetname t inner join
(
select u.userid, min(u.paymentdate) as mindate
from sheetname u
group by u.userid
) v on t.userid = v.userid and t.paymentdate = v.mindate
group by t.userid
) w on s.userid = w.userid and s.paymentid = w.minid
answered Nov 22 '18 at 23:58
Lee MacLee Mac
4,19431541
edited Nov 23 '18 at 0:26
answered Nov 22 '18 at 23:58
Lee MacLee Mac
4,19431541
answered Nov 22 '18 at 23:58
Lee MacLee Mac
4,19431541
answered Nov 22 '18 at 23:58
Lee MacLee Mac
4,19431541
4,19431541
add a comment |
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');
});
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%2fstackoverflow.com%2fquestions%2f53439146%2fsql-statement-for-minimum-time-and-minimum-id%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
If you need to partition by something like "userID", use Squirrel's answer but add
PARTITION BY userIDin the row number window function.– ZLK
Nov 23 '18 at 0:41
That second is another question - especially since you will want new/different answers, post each table and what you have tried. Post all the details (tables) and what you have tried in that new question
– Mark Schultheiss
Nov 23 '18 at 14:24