ranking query

I have this very simple query here
SELECT distinct
xdate,
store
FROM deldates;

Open in new window


that gives me

XDATE	STORE
2	10411
5	10411
5	10412
2	10412
7	10413
4	10413
6	10414
4	10414
2	10414
6	10415
3	10415
6	10416
3	10416
6	10418
2	10418
4	10427
7	10427
7	10428
4	10428
3	10429

Open in new window


I am looking for a new query that will give these results

XDATE	STORE	RANKING
2	10411	1
5	10411	2
5	10412	2
2	10412	1
7	10413	2
4	10413	1	
6	10414	3
4	10414	2
2	10414	1
6	10415	2
3	10415	1
6	10416	2
3	10416	1
6	10418	2
2	10418	1
4	10427	2
7	10427	1
7	10428	2
4	10428	1
3	10429	1

Open in new window

FutureDBA-Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Ty this (untested, just typed in):
SELECT distinct
xdate,
store,
rank() over(partition by store order by xdate) myrank
FROM deldates;
0
 
FutureDBA-Author Commented:
that's on the right track i think, but the results are wrong.

XDATE	STORE	MYRANK
2	1000	1
5	1000	19
3	10395	1
7	10395	12
2	10396	1
5	10396	17
2	10397	1
4	10397	11
6	10397	21
3	10400	1
5	10400	12
7	10400	23
4	10401	1
7	10401	11
2	10402	1
5	10402	23
2	10403	1
5	10403	10
4	10404	1
7	10404	5
3	10405	1
6	10405	12

Open in new window



i added an order by clause to main query to make it easier to work with
SELECT distinct
xdate,
store,
rank() over(partition by store order by xdate) myrank
FROM deldates
order by store, to_number(xdate);
0
 
FutureDBA-Author Commented:
dense_rank worked
0
 
sventhanCommented:
try this ..

SELECT distinct
xdate,
store,
rank() over(partition by store,xdate order by store,xdate) myrank
FROM deldates
0
 
FutureDBA-Author Commented:
set me on right path,

used desne_rank instead of rank.

thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.