CODEWITHSUNDEEP
mysqlsql
digestivee
digestivee

Reputation: 740

SQL : number different dates within users

I know this question must have been asked before but I can't find it when I am looking around. Basically I have a table with column 1 and 2, and I want to know how I can construct column 3 of this table:

user   date         number
---------------------------
 1     2017-08-22     1
 1     2017-08-22     1
 1     2017-09-12     2
 1     2017-09-12     2
 1     2017-10-15     3
 2     2017-05-23     1
 2     2017-07-24     2

Anyone got an idea?

Upvotes: 2

Views: 49

Answers (1)

Magisch
Magisch

Reputation: 7352

Other then Rownumber shenanigans a subquery might do the trick:

SELECT
et.user,
et.date,
(SELECT COUNT(DISTINCT date) FROM exampleTable WHERE user = et.user AND date <= et.date) AS number
FROM exampleTable AS et 
ORDER BY user ASC;

This works by making number the amount of occurences of distinct dates lower then or equal to the current date.

Detailed breakdown:

  • You're trying to "rank" the dates for any given user.
  • Therefore multiple occurences of one date aren't counted (COUNT(DISTINCT))
  • The rank of each row is the amount of unique dates lower then or equal to it that came before

You can try it out online here

Upvotes: 1

Related Questions