|
|
Posted by Mladen Gogala on 10/08/07 15:09
On Sat, 06 Oct 2007 18:16:01 +0000, Colleen wrote:
> Hello!
>
> I've only been dabbling in PHP and MySQL for a few months.
>
> I'm trying to find out if there's a query/function similar to the RANK
> function in Excel.
>
> What I want to be able to do is take a list of dates from a particular
> table field, and be able to rank each record in the order within which
> in falls in those range of dates, because records are not always entered
> in chronological order.
>
> Here's a partial sampling of my table, showing the relevant fields for
> this query.
>
> casualtyID deathdate
> 1 2003-12-01
> 2 2007-06-20
> 3 2002-08-30
> 4 2007-01-01
> 5 2005-04-11
>
> So, from this "ranking" query (that would generate the result $rank), I
> want to be able to say:
> echo "$casualtyID is the number $rank casualty to die in the current
> wars in Iraq and Afghanistan\n";
>
>
> Thank you!
>
> Colleen Robledo
> http://www.colleenrobledo.info
I'm not sure about MySQL but Oracle can do the following:
select id,
dense_rank() over (order by deathdate) as rnk
from table;
You can do that on the classic EMP table and it goes like this:
1 select ename,hiredate,
2 dense_rank() over (order by hiredate) as rnk
3* from emp
SQL> /
ENAME HIREDATE RNK
---------- --------- ----------
SMITH 17-DEC-80 1
ALLEN 20-FEB-81 2
WARD 22-FEB-81 3
JONES 02-APR-81 4
BLAKE 01-MAY-81 5
CLARK 09-JUN-81 6
TURNER 08-SEP-81 7
MARTIN 28-SEP-81 8
KING 17-NOV-81 9
JAMES 03-DEC-81 10
FORD 03-DEC-81 10
MILLER 23-JAN-82 11
SCOTT 19-APR-87 12
ADAMS 23-MAY-87 13
14 rows selected.
SQL>
You can do that with Oracle Express, too. It's called "analytic function".
--
http://www.mladen-gogala.com
Navigation:
[Reply to this message]
|