Ranking based on a group and column

[achick]

I have a table where I would like to rank based on a group and column. I want to create a rank column for each type based on cdate column as below.
ID Type CDate Rank
1 Pens 5/20/2000 1
2 Pens 4/10/2004 2
3 Pencils 1/2/2000 2
4 Pencils 5/6/1998 1
5 Pencils 6/6/2006 3
6 Crayons 2/1/2000 1
7 Crayons 8/9/2003 2
8 Crayons 8/9/2003 3

The lowest date gets first ranking.
If cdate is same, lowest ID gets the lowest/minimum rank.

Is there an easy way to rank?

 

[PHV]

you may try this:

SELECT A.ID, A.Type, A.CDate, Count(*) AS Rank
FROM yourTable AS A INNER JOIN yourTable AS B ON A.Type = B.Type AND A.CDate >= B.CDate
GROUP BY A.ID, A.Type, A.CDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[achick]

Thank you so much for the query.

I changed it a little bit to use ID too when creating rank.

Here is my query but what's happening is if they are the same date in a type, the highest ID gets the rank 1 or first rank. I thought the earliest date and lowest ID should get rank1.

SELECT A.ID, A.Type, A.CDate, Count(*) AS Rank
FROM yourTable AS A INNER JOIN yourTable AS B ON A.Type = B.Type AND A.CDate & A.ID <= B.CDate &B.ID
GROUP BY A.ID, A.Type, A.CDate

 

[PHV]

What about simply this ?

SELECT A.ID, A.Type, A.CDate, Count(*) AS Rank
FROM yourTable AS A INNER JOIN yourTable AS B ON A.Type = B.Type AND A.ID >= B.ID
GROUP BY A.ID, A.Type, A.CDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[achick]

Actually my ids are not in ascending oprder. So I tried this and seesm to be working so far.

SELECT A.ID, A.Type, A.CDate, Count(*) AS Rank
FROM yourTable AS A INNER JOIN yourTable AS B ON A.Type = B.Type AND format(A.CDate,'yyyymmdd') & A.ID >=format(B.CDate,'yyyymmdd') &B.ID
GROUP BY A.ID, A.Type, A.CDate

 

[lespaul]

you shouldn't have to do all the formatting and converting of your dates, this should return the same results:

SELECT A.ID, A.Type, A.CDate, Count(*) AS Rank
FROM yourTable AS A INNER JOIN yourTable AS B ON A.Type = B.Type AND A.CDate & A.ID  >=B.CDate &B.ID
GROUP BY A.ID, A.Type, A.CDate

Leslie

Come join me at New Mexico Linux Fest!

 

[achick]

I thought it didn't work when I didn't use the format (may be becasue in access they are in date time field). I will check again

Thankyou