postgresql sequence number depending on rows?

Question

Taking a course in databases and i am unsure of how to create this view. I have this table(postgresql):

CREATE TABLE InQueue (
  id INT REFERENCES Student(id),
  course VARCHAR(10) REFERENCES RestrictedCourse(course_code),
  since TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id,course),
  UNIQUE (course,since)
  );

I am supposed to create a view that lists course, id, number, where number is calculated with since. Basically the lowest since gives the queuenumber 1, the 2nd lowest gives 2 and so forth. (course,number) is unique, but not number by itself, since there are many different courses.

What i think needs to be done is to first order the table by (course,since) and then just add sequence numbers, but eventually the course will change and then the sequence numbers need to start over, beginning with 1 again.

Could someone point me in the right direction? :)

You should look into window functions and then in particular on partitioning the data into groups and how to apply a window function over them. — Patrick
– Patrick, Commented Feb 24, 2016 at 17:05

Paul A Jungwirth · Accepted Answer · 2016-02-24 17:22:32Z

2

Use:

Select row_number() over (partition by course order by since asc) as yournumber, id,
course, since from InQueue

You can read about analytic functions here: http://www.postgresql.org/docs/9.4/static/tutorial-window.html

edited Feb 24, 2016 at 17:22

Paul A Jungwirth

24.9k16 gold badges79 silver badges96 bronze badges

answered Feb 24, 2016 at 17:01

Haytem BrB

1,5093 gold badges16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rewbert Over a year ago

Perfect, this produces the desired result. I'll read up on what all those keywords do and add this to my work. Super! edit -- thanks for the link, reading right now.

Haytem BrB Over a year ago

I added a link to explain analytic functions (like row_number here)

Patrick Over a year ago

@Rewbert You should not read the referenced documentation. Version 8.4 is prehistoric and upsupported since mid 2014. Read the current documentation instead.

Rewbert Over a year ago

Oh okay, thanks for the tip. Will make sure to find updated documentation :)

Haytem BrB Over a year ago

@Patrick the content I posted in the link remains unchanged though.

Collectives™ on Stack Overflow

postgresql sequence number depending on rows?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related