Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

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

0
FutureDBA-
Asked:
FutureDBA-
  • 3
1 Solution
 
slightwv (䄆 Netminder) 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now