special rank

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!

Does anyone know how to make a query for this result:

cust_id due_date special rank
180912 9.6.2004 1
180912 9.6.2004 1
180912 13.6.2004 2
180912 27.7.2004 3
232459 12.8.2004 1
232459 12.8.2004 1
232459 20.8.2004 2
232459 20.8.2004 2
232459 13.9.2004 3
406562 9.9.2004 1
406562 13.9.2004 2
406562 15.9.2004 3

Thanks.
 
If these fields are in a table use the New Query wizard to select your table
and fields and in the design view of the query you can select the cust-id
field to sort in Ascending order
HTH
Tony Williams
 
I think we misunderstood. The problem is not in sorting cust-id, but in
ranking inside the same cust-id groups by due date in a way that is shown in
an example below.

Miha

"Tony Williams" je napisal:
 
Miha said:
Does anyone know how to make a query for this result:

cust_id due_date special rank
180912 9.6.2004 1
180912 9.6.2004 1
180912 13.6.2004 2
180912 27.7.2004 3
232459 12.8.2004 1
232459 12.8.2004 1
232459 20.8.2004 2
232459 20.8.2004 2
232459 13.9.2004 3
406562 9.9.2004 1
406562 13.9.2004 2
406562 15.9.2004 3
Click to expand...


That's not a standard ranking. Normally it would be:
232459 12.8.2004 1
232459 12.8.2004 1
232459 20.8.2004 3
232459 20.8.2004 3
232459 13.9.2004 5

which you can get this way:

SELECT cust_id, due_date,
(SELECT Count(*) + 1
FROM table AS X
WHERE X.cust_id = table.cust_id
AND X.due_date < table.due_date
) AS Rank
FROM table

But, you can get what you asked for:

SELECT cust_id, due_date,
(SELECT Count(*) + 1
FROM [SELECT DISTINCT Y.cust_id, Y.due_date
FROM table AS Y]. As X
WHERE X.cust_id = table.cust_id
AND X.due_date < table.due_date
) AS Rank
FROM table

which is pretty messy with two subqueries. The inner
subquery will not allow you to use [ ] around any names, so
be careful that you don't let any be inserted there.
 
Back
Top