Postgres window functions - rank() partition by bigintWhat are the options for storing hierarchical data in a relational database?postgres: upgrade a user to be a superuser?How to use RANK() SQl Function?Syntax error near rank() functionUsing postgres rank function to limit to n top resultsHow to compare dates in datetime fields in Postgresql?PostgreSQL Window Function orderingPartition By over Two Columns in Row_Number functionSum over partition not workingHow to cast bigint to timestamp with time zone in postgres in an update
How can saying a song's name be a copyright violation?
ssTTsSTtRrriinInnnnNNNIiinngg
Is it inappropriate for a student to attend their mentor's dissertation defense?
Why is it a bad idea to hire a hitman to eliminate most corrupt politicians?
Detention in 1997
Why do bosons tend to occupy the same state?
How seriously should I take size and weight limits of hand luggage?
What about the virus in 12 Monkeys?
How to show a landlord what we have in savings?
Is it acceptable for a professor to tell male students to not think that they are smarter than female students?
What killed these X2 caps?
Is it logically or scientifically possible to artificially send energy to the body?
One verb to replace 'be a member of' a club
What does the expression "A Mann!" means
Can the Meissner effect explain very large floating structures?
Unlock My Phone! February 2018
Can my sorcerer use a spellbook only to collect spells and scribe scrolls, not cast?
What is the most common color to indicate the input-field is disabled?
Is "remove commented out code" correct English?
Which is the best way to check return result?
Why no variance term in Bayesian logistic regression?
How do I gain back my faith in my PhD degree?
Could the museum Saturn V's be refitted for one more flight?
Mathematica command that allows it to read my intentions
Postgres window functions - rank() partition by bigint
What are the options for storing hierarchical data in a relational database?postgres: upgrade a user to be a superuser?How to use RANK() SQl Function?Syntax error near rank() functionUsing postgres rank function to limit to n top resultsHow to compare dates in datetime fields in Postgresql?PostgreSQL Window Function orderingPartition By over Two Columns in Row_Number functionSum over partition not workingHow to cast bigint to timestamp with time zone in postgres in an update
I'm trying to rank data over partition by a column thats type is bigint but it isn't working when I change the column in the partition by to a column of type timestamp with time zone it does work.
My query looks like this:
select id,ts_insert,serial_no,rank() over (partition by serial_no order by serial_no desc) from schema.table_name
The result looks like this:
1,'2019-02-19 18:27:50.258846+11',19516,1
1,'2019-02-19 18:27:50.258846+11',19515,1
1,'2019-02-19 18:27:50.258846+11',19514,1
1,'2019-02-06 19:45:38.896062+11',15069,1
1,'2019-02-05 21:46:28.633325+11',14827,1
Expected result is:
1,'2019-02-19 18:27:50.258846+11',19516,1
1,'2019-02-19 18:27:50.258846+11',19515,2
1,'2019-02-19 18:27:50.258846+11',19514,3
1,'2019-02-06 19:45:38.896062+11',15069,4
1,'2019-02-05 21:46:28.633325+11',14827,5
Can someone help me please?
Thanks in advance...
sql postgresql window-functions
asked Mar 7 at 22:35
VictorVictor
33
add a comment |
I'm trying to rank data over partition by a column thats type is bigint but it isn't working when I change the column in the partition by to a column of type timestamp with time zone it does work.
My query looks like this:
select id,ts_insert,serial_no,rank() over (partition by serial_no order by serial_no desc) from schema.table_name
The result looks like this:
1,'2019-02-19 18:27:50.258846+11',19516,1
1,'2019-02-19 18:27:50.258846+11',19515,1
1,'2019-02-19 18:27:50.258846+11',19514,1
1,'2019-02-06 19:45:38.896062+11',15069,1
1,'2019-02-05 21:46:28.633325+11',14827,1
Expected result is:
1,'2019-02-19 18:27:50.258846+11',19516,1
1,'2019-02-19 18:27:50.258846+11',19515,2
1,'2019-02-19 18:27:50.258846+11',19514,3
1,'2019-02-06 19:45:38.896062+11',15069,4
1,'2019-02-05 21:46:28.633325+11',14827,5
Can someone help me please?
Thanks in advance...
sql postgresql window-functions
asked Mar 7 at 22:35
VictorVictor
33
add a comment |
I'm trying to rank data over partition by a column thats type is bigint but it isn't working when I change the column in the partition by to a column of type timestamp with time zone it does work.
My query looks like this:
select id,ts_insert,serial_no,rank() over (partition by serial_no order by serial_no desc) from schema.table_name
The result looks like this:
1,'2019-02-19 18:27:50.258846+11',19516,1
1,'2019-02-19 18:27:50.258846+11',19515,1
1,'2019-02-19 18:27:50.258846+11',19514,1
1,'2019-02-06 19:45:38.896062+11',15069,1
1,'2019-02-05 21:46:28.633325+11',14827,1
Expected result is:
1,'2019-02-19 18:27:50.258846+11',19516,1
1,'2019-02-19 18:27:50.258846+11',19515,2
1,'2019-02-19 18:27:50.258846+11',19514,3
1,'2019-02-06 19:45:38.896062+11',15069,4
1,'2019-02-05 21:46:28.633325+11',14827,5
Can someone help me please?
Thanks in advance...
sql postgresql window-functions
asked Mar 7 at 22:35
VictorVictor
33
I'm trying to rank data over partition by a column thats type is bigint but it isn't working when I change the column in the partition by to a column of type timestamp with time zone it does work.
My query looks like this:
select id,ts_insert,serial_no,rank() over (partition by serial_no order by serial_no desc) from schema.table_name
The result looks like this:
1,'2019-02-19 18:27:50.258846+11',19516,1
1,'2019-02-19 18:27:50.258846+11',19515,1
1,'2019-02-19 18:27:50.258846+11',19514,1
1,'2019-02-06 19:45:38.896062+11',15069,1
1,'2019-02-05 21:46:28.633325+11',14827,1
Expected result is:
1,'2019-02-19 18:27:50.258846+11',19516,1
1,'2019-02-19 18:27:50.258846+11',19515,2
1,'2019-02-19 18:27:50.258846+11',19514,3
1,'2019-02-06 19:45:38.896062+11',15069,4
1,'2019-02-05 21:46:28.633325+11',14827,5
Can someone help me please?
Thanks in advance...
sql postgresql window-functions
sql postgresql window-functions
asked Mar 7 at 22:35
VictorVictor
33
asked Mar 7 at 22:35
VictorVictor
33
asked Mar 7 at 22:35
VictorVictor
33
asked Mar 7 at 22:35
VictorVictor
33
asked Mar 7 at 22:35
VictorVictor
33
33
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You seem to want:
select id, ts_insert, serial_no,
rank() over (partition by id order by serial_no desc)
from schema.table_name;
serial_no is unique (at least in the data you've provided). If you partition by a unique column, then the rank() will always be 1.
answered Mar 7 at 22:36
Gordon LinoffGordon Linoff
793k36318421
That's correct serial_no is unique, thanks for your response. What can I use to select the highest value in the serial_no column?
– Victor
Mar 7 at 22:39
@Victor . . . That is what theorder bydoes. If you want to filter the results, use a subquery and addwhere rnk = 1.
– Gordon Linoff
Mar 7 at 22:41
Okay thanks a lot
– Victor
Mar 7 at 22:45
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
);
);
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%2f55053916%2fpostgres-window-functions-rank-partition-by-bigint%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You seem to want:
select id, ts_insert, serial_no,
rank() over (partition by id order by serial_no desc)
from schema.table_name;
serial_no is unique (at least in the data you've provided). If you partition by a unique column, then the rank() will always be 1.
answered Mar 7 at 22:36
Gordon LinoffGordon Linoff
793k36318421
That's correct serial_no is unique, thanks for your response. What can I use to select the highest value in the serial_no column?
– Victor
Mar 7 at 22:39
@Victor . . . That is what theorder bydoes. If you want to filter the results, use a subquery and addwhere rnk = 1.
– Gordon Linoff
Mar 7 at 22:41
Okay thanks a lot
– Victor
Mar 7 at 22:45
add a comment |
You seem to want:
select id, ts_insert, serial_no,
rank() over (partition by id order by serial_no desc)
from schema.table_name;
serial_no is unique (at least in the data you've provided). If you partition by a unique column, then the rank() will always be 1.
answered Mar 7 at 22:36
Gordon LinoffGordon Linoff
793k36318421
That's correct serial_no is unique, thanks for your response. What can I use to select the highest value in the serial_no column?
– Victor
Mar 7 at 22:39
@Victor . . . That is what theorder bydoes. If you want to filter the results, use a subquery and addwhere rnk = 1.
– Gordon Linoff
Mar 7 at 22:41
Okay thanks a lot
– Victor
Mar 7 at 22:45
add a comment |
You seem to want:
select id, ts_insert, serial_no,
rank() over (partition by id order by serial_no desc)
from schema.table_name;
serial_no is unique (at least in the data you've provided). If you partition by a unique column, then the rank() will always be 1.
answered Mar 7 at 22:36
Gordon LinoffGordon Linoff
793k36318421
You seem to want:
select id, ts_insert, serial_no,
rank() over (partition by id order by serial_no desc)
from schema.table_name;
serial_no is unique (at least in the data you've provided). If you partition by a unique column, then the rank() will always be 1.
answered Mar 7 at 22:36
Gordon LinoffGordon Linoff
793k36318421
answered Mar 7 at 22:36
Gordon LinoffGordon Linoff
793k36318421
answered Mar 7 at 22:36
Gordon LinoffGordon Linoff
793k36318421
answered Mar 7 at 22:36
Gordon LinoffGordon Linoff
793k36318421
793k36318421
That's correct serial_no is unique, thanks for your response. What can I use to select the highest value in the serial_no column?
– Victor
Mar 7 at 22:39
@Victor . . . That is what theorder bydoes. If you want to filter the results, use a subquery and addwhere rnk = 1.
– Gordon Linoff
Mar 7 at 22:41
Okay thanks a lot
– Victor
Mar 7 at 22:45
add a comment |
That's correct serial_no is unique, thanks for your response. What can I use to select the highest value in the serial_no column?
– Victor
Mar 7 at 22:39
@Victor . . . That is what theorder bydoes. If you want to filter the results, use a subquery and addwhere rnk = 1.
– Gordon Linoff
Mar 7 at 22:41
Okay thanks a lot
– Victor
Mar 7 at 22:45
That's correct serial_no is unique, thanks for your response. What can I use to select the highest value in the serial_no column?
– Victor
Mar 7 at 22:39
That's correct serial_no is unique, thanks for your response. What can I use to select the highest value in the serial_no column?
– Victor
Mar 7 at 22:39
@Victor . . . That is what the
order by does. If you want to filter the results, use a subquery and add where rnk = 1.– Gordon Linoff
Mar 7 at 22:41
@Victor . . . That is what the
order by does. If you want to filter the results, use a subquery and add where rnk = 1.– Gordon Linoff
Mar 7 at 22:41
Okay thanks a lot
– Victor
Mar 7 at 22:45
Okay thanks a lot
– Victor
Mar 7 at 22:45
add a comment |
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.
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%2f55053916%2fpostgres-window-functions-rank-partition-by-bigint%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
