Ufdjrw

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

0

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

share|improve this question

asked Mar 7 at 22:35

VictorVictor

33

add a comment | 

0

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

share|improve this question

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

share|improve this question

asked Mar 7 at 22:35

VictorVictor

33

select id,ts_insert,serial_no,rank() over (partition by serial_no order by serial_no desc) from schema.table_name

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

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

share|improve this question

asked Mar 7 at 22:35

VictorVictor

33

share|improve this question

asked Mar 7 at 22:35

VictorVictor

33

asked Mar 7 at 22:35

VictorVictor

33

asked Mar 7 at 22:35

VictorVictor

33

1 Answer
1

active

oldest

votes

0

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.

share|improve this answer

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 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
  
  

  
  
  
  

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

Name

Email

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

Name

Email

Required, but never shown

0

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.

share|improve this answer

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 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
  
  

  
  
  
  

add a comment | 

0

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.

share|improve this answer

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 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
  
  

  
  
  
  

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.

share|improve this answer

answered Mar 7 at 22:36

Gordon LinoffGordon Linoff

793k36318421

select id, ts_insert, serial_no,
 rank() over (partition by id order by serial_no desc)
from schema.table_name;

share|improve this answer

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