Prev: Value in union query
Next: select and count
From: Steven Cheng on 12 Apr 2010 19:52
I am trying to general a query where by their is ranking within groups like
the table below:

firm-id rank
1
1
1
2
2
3

want:

firm-id rank
1 1
1 2
1 3
2 1
2 2
3 1
DP file

are row number and rank not valid functions in the ADP file?
From: John W. Vinson on 12 Apr 2010 20:13
On Mon, 12 Apr 2010 16:52:01 -0700, Steven Cheng
<StevenCheng(a)discussions.microsoft.com> wrote:

>I am trying to general a query where by their is ranking within groups like
>the table below:
>
> firm-id rank
>1
>1
>1
>2
>2
>3
>
>want:
>
>firm-id rank
>1 1
>1 2
>1 3
>2 1
>2 2
>3 1
>DP file
>
>are row number and rank not valid functions in the ADP file?

No, they are not. Relational tables should be viewed as unordered "bags" of
records; there is no builtin "row number", and records will be retrieved in
any order that the program finds convenient, unless you have an ORDER BY
clause which determines that order. What other fields are available? Is there
anything within the record that would distinguish the third-rank entry for
firm 1 from the first-rank entry for that firm?
--

John W. Vinson [MVP]
From: Dorian on 12 Apr 2010 20:41
What is the purpose of the Rank column and how are the ranks to be assigned
to like ids?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Steven Cheng" wrote:

> I am trying to general a query where by their is ranking within groups like
> the table below:
>
> firm-id rank
> 1
> 1
> 1
> 2
> 2
> 3
>
> want:
>
> firm-id rank
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> DP file
>
> are row number and rank not valid functions in the ADP file?
From: vanderghast on 13 Apr 2010 07:21
If you can connect to an MS SQL Server 2005 or later, then you can quite
probably use RANK or Row_Number( ) functions introduced in 2005. The syntax
is:

--------------
SELECT *, ROW_NUMBER( ) OVER ( ORDER BY firmId ) AS rowNumber
FROM table
--------------

as example. That does not work with Jet, only with MS SQL Server, and only
if it is 2005 or later (2008).

You cannot access the field rowNumber in a where clause of the same query
where it is defined, though (since the SELECT clause is evaluated AFTER the
WHERE clause), but you can use a virtual table to reach it:

---------------
SELECT *

FROM (
SELECT *, ROW_NUMBER( ) OVER ( ORDER BY frmID) AS rowNumber
FROM table ) AS x

WHERE rowNumber BETWEEN 10 and 20
---------------



Vanderghast, Access MVP



"Steven Cheng" <StevenCheng(a)discussions.microsoft.com> wrote in message
news:609057D7-C7F4-41B0-A94C-527737F055FB(a)microsoft.com...
>I am trying to general a query where by their is ranking within groups like
> the table below:
>
> firm-id rank
> 1
> 1
> 1
> 2
> 2
> 3
>
> want:
>
> firm-id rank
> 1 1
> 1 2
> 1 3
> 2 1
> 2 2
> 3 1
> DP file
>
> are row number and rank not valid functions in the ADP file?

 | 
Pages: 1
Prev: Value in union query
Next: select and count