CODEWITHSUNDEEP
sql
afarrell
afarrell

Reputation: 35

SQL index relative to another field

I have, as part of a query, a bunch of distinct pairs of values:

a       d
a       e
a       f
b       g
b       h
c       i

I'd like to be able to calculate an counter relative to the first field:

a       d       1
a       e       2
a       f       3
b       g       1
b       h       2
c       i       1

I can't use the position in the temporary table - apart from anything else it goes too high, whereas the value I need can't go over 2 digits (and there isn't going to be more than 50 entries with the same first field. Are there any methods or techniques to help?

Thanks for any help!

Upvotes: 0

Views: 143

Answers (2)

user330315
user330315

Reputation:

You didn't specify your DBMS, so this is ANSI SQL:

select a,
       b,
       row_number() over (partition by a order by b) as idx
from the_table;

SQLFiddle: http://sqlfiddle.com/#!15/4cf96/1

row_number() is a window function which will generate a unique number based on the "grouping" and ordering defined with the partition by clause. The Postgres manual has a nice introduction to window functions: http://www.postgresql.org/docs/current/static/tutorial-window.html

This is going to be much faster than a self join

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32392

select t1.c1 , t1.c2 , count(t2.c1) cnt
from mytable t1
join mytable t2 on t1.c1 = t2.c1 and t1.c2 >= t2.c2
group by t1.c1, t1.c2
order by t1.c1, cnt

Demo

Explanation

This query assumes that the pair (c1,c2) is unique.

To rank each row (c1,c2) the query counts the number of rows within the group c1 where c2 is less than or equal to c2. For example, for (a,e), there are 2 rows within the group a that are less than or equal to e (namely d and e).

Upvotes: 1

Related Questions