CODEWITHSUNDEEP
sqlpostgresql
Patthebug
Patthebug

Reputation: 4797

How to use Row_Number() correctly

I'm running the following query on Postgres:

SELECT a.testscoreid,
       COUNT(a.*) AS Questions,
       a.created,
       ROW_NUMBER() OVER (PARTITION BY Questions) AS Number,
       b.irt_tlevel
FROM asmt.testscores a
  JOIN asmt.questions b ON a.questionid = b.questionid
WHERE a.answered = TRUE
AND   a.testscoreid = '53bd6ed5da2cfe7baf0d97ed'
AND   irt_tlevel IS NOT NULL
GROUP BY a.testscoreid,
         a.created,
         b.irt_tlevel
ORDER BY Questions DESC,
         a.created ASC

which gives me the following result:

Testscoreid                 Questions Created               Number irt_tlevel
53bd6ed5da2cfe7baf0d97ed    272       2014-07-09 09:33:25.0 1      easy
53bd6ed5da2cfe7baf0d97ed    38        2014-07-09 09:33:25.0 1      hard
53bd6ed5da2cfe7baf0d97ed    34        2014-07-09 09:33:25.0 1      medium

I would like to have the Number column increment each time, for all questionid. I think I'm missing something in row_number().

I want the column Number to increment for all the questionid. For example - I would like to have 272 rows (with incrementing Number) for easy questions in the example above, 38 rows for hard and 34 rows for medium. In total, I would like to have (272+38+34) rows, with Number incrementing as per Created.

Any suggestions would be appreciated.

Upvotes: 1

Views: 140

Answers (2)

Chinthaka Senaratne
Chinthaka Senaratne

Reputation: 110

Do you want to show (272 + 38 + 34) rows? What you expect is not clear. If you want such rows your main error is grouping with irt_tlevel level.

But if you do so to get the total count for a particular level you may have to have a sub query or a function call to get the total.

e.g.- (not tested for syntax errors)

SELECT a.testscoreid,       
       a.created,
       ROW_NUMBER() OVER (ORDER BY created ) AS Number,
       b.irt_tlevel irt_level
FROM asmt.testscores a, asmt.questions b
WHERE a.answered = TRUE
AND b.questionid = a.questionid
AND   a.testscoreid = '53bd6ed5da2cfe7baf0d97ed'
ORDER BY a.created ASC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use ORDER BY instead of PARTITION BY:

SELECT a.testscoreid,
       COUNT(*) AS Questions,
       a.created,
       ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) AS Number,
       b.irt_tlevel

Upvotes: 1

Related Questions