CODEWITHSUNDEEP
sqlsql-serverdatelabelrank
Bob
Bob

Reputation: 63

SQL How to order each entry by date

I have a list of customer_ids, the date on which some information was changed, and the corresponding changes. I would like to number each change, by order of date, on each customer. So for example; I have something that looks like the following

    Cust_id         Date                information
   -----------------------------------------------------
    12345           2015-04-03          blue hat 
    12345           2015-04-05          red scarf 
    54321           2015-04-12          yellow submarine

and I would like an output which looks something like this;

    cust_id      change_number        Date             information 
   ---------------------------------------------------------------
    12345          1                  2015-04-03        blue hat   
    12345          2                  2015-04-0         red scarf    
    54321          1                  2015-04-12        yellow submarine

This will be quite a big table, so it will need to be somewhat efficient. There will be at most 1 entry per customer per day. Any help you can give is appreciated.

Upvotes: 4

Views: 114

Answers (3)

john igneel
john igneel

Reputation: 407

Simply use the ORDER BY clause:

SELECT *
FROM (
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY Cust_id ORDER BY [Date]) As Change_Number
    FROM yourTable) t
ORDER BY
    Cust_id, Change_Number;

Upvotes: 0

shA.t
shA.t

Reputation: 16968

If you want to order over a change number like that you need to use an inner select like this:

SELECT *
FROM (
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY Cust_id ORDER BY [Date]) As Change_Number
    FROM yourTable) t
ORDER BY
    Cust_id, Change_Number;

Upvotes: 3

YLG
YLG

Reputation: 885

As Indian said, Try this : 

 select cust_id,
        Row_number() over(partition by cust_id order by date) change_number,
        Date,
        information 
   from tablename;

Upvotes: 2

Related Questions