Ranking

[DMosher]

I am looking for SQL to rank the following data based on premium. Highest
premium = rank 1 and so on. Any ideas?

Policy Key Company Key Total Vehicle Premium
0 5 1801
0 4 1442
0 0 1441
0 2 1376
0 1 1364
0 3 1331

DMosher

 

[Duane Hookom]

I see 7 potential field names with 3 columns of data. I don't see the value
you expect to see calculated.

 

[John Spencer]

One method:
SELECT *
, 1 + (SELECT Count(*)
FROM [SomeTable] as TEMP
WHERE Temp.[Premium] < [SomeTable].[Premium])
FROM [SomeTable]
ORDER BY [Premium]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Clifford Bass]

Hi,

Try the following, adjusting your table name as needed:

SELECT A.[Policy Key], A.[Company Key], A.[Total Vehicle Premium],
DCount("*","tblRank","[Policy Key] = " & [A].[Policy Key] & " and [Total
Vehicle Premium] > " & [A].[Total Vehicle Premium])+1 AS Ranking
FROM tblRank AS A
ORDER BY DCount("*","tblRank","[Policy Key] = " & [A].[Policy Key] & " and
[Total Vehicle Premium] > " & [A].[Total Vehicle Premium])+1;

Note that it will rank those with an equal value as the same rank and
skip some ranks. So if you add another row with the values of 0, 6, 1376,
you wil get:

Policy Key Company Key Total Vehicle Premium Ranking
0 5 1801 1
0 4 1442 2
0 0 1441 3
0 6 1376 4
0 2 1376 4
0 1 1364 6
0 3 1331 7

Hope that helps,

Clifford Bass

 

[DMosher]

The fiield names are
Policy Key
Company Key
Total Vehicle Premium
Rank

I need to rank the policy key (in this example a 0) by Total Vehicle
Premium. So in this case. I would llike to see

Policy Key Company Key Total Vehicle Premium Rank
0 5 1801 1
0 4 1442 2
0 0 1441 3
0 2 1376 4
0 1 1364 5
0 3 1331 6

Don

 

[DMosher]

Question..what is in the tblRank?

Hi,

Try the following, adjusting your table name as needed:

SELECT A.[Policy Key], A.[Company Key], A.[Total Vehicle Premium],
DCount("*","tblRank","[Policy Key] = " & [A].[Policy Key] & " and [Total
Vehicle Premium] > " & [A].[Total Vehicle Premium])+1 AS Ranking
FROM tblRank AS A
ORDER BY DCount("*","tblRank","[Policy Key] = " & [A].[Policy Key] & " and
[Total Vehicle Premium] > " & [A].[Total Vehicle Premium])+1;

Note that it will rank those with an equal value as the same rank and
skip some ranks. So if you add another row with the values of 0, 6, 1376,
you wil get:

Policy Key Company Key Total Vehicle Premium Ranking
0 5 1801 1
0 4 1442 2
0 0 1441 3
0 6 1376 4
0 2 1376 4
0 1 1364 6
0 3 1331 7

Hope that helps,

Clifford Bass

I am looking for SQL to rank the following data based on premium. Highest
premium = rank 1 and so on. Any ideas?

Policy Key Company Key Total Vehicle Premium
0 5 1801
0 4 1442
0 0 1441
0 2 1376
0 1 1364
0 3 1331

DMosher

 

[Clifford Bass]

Hi,

It is merely the name of the table that contains you data, as named by
me for testing purposes.

Clifford Bass

 

[Duane Hookom]

If you ask a question in the future, you might want to be more explicit with
your table and field names. If you use spaces in your field names, you should
take the time to enclose them in []s like [Policy key], [Company Key], etc.

Do the crime (spaces in object names)
Do the time (typing in []s)

--
Duane Hookom
Microsoft Access MVP

Question..what is in the tblRank?

Hi,

Try the following, adjusting your table name as needed:

SELECT A.[Policy Key], A.[Company Key], A.[Total Vehicle Premium],
DCount("*","tblRank","[Policy Key] = " & [A].[Policy Key] & " and [Total
Vehicle Premium] > " & [A].[Total Vehicle Premium])+1 AS Ranking
FROM tblRank AS A
ORDER BY DCount("*","tblRank","[Policy Key] = " & [A].[Policy Key] & " and
[Total Vehicle Premium] > " & [A].[Total Vehicle Premium])+1;

Note that it will rank those with an equal value as the same rank and
skip some ranks. So if you add another row with the values of 0, 6, 1376,
you wil get:

Policy Key Company Key Total Vehicle Premium Ranking
0 5 1801 1
0 4 1442 2
0 0 1441 3
0 6 1376 4
0 2 1376 4
0 1 1364 6
0 3 1331 7

Hope that helps,

Clifford Bass

I am looking for SQL to rank the following data based on premium. Highest
premium = rank 1 and so on. Any ideas?

Policy Key Company Key Total Vehicle Premium
0 5 1801
0 4 1442
0 0 1441
0 2 1376
0 1 1364
0 3 1331

DMosher

 

[DMosher]

How would I modify the SQL to have the highest premium come in with a Rank of
1 and then in descending order, rank2, rank3 and so on.

DM

One method:
SELECT *
, 1 + (SELECT Count(*)
FROM [SomeTable] as TEMP
WHERE Temp.[Premium] < [SomeTable].[Premium])
FROM [SomeTable]
ORDER BY [Premium]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am looking for SQL to rank the following data based on premium. Highest
premium = rank 1 and so on. Any ideas?

Policy Key Company Key Total Vehicle Premium
0 5 1801
0 4 1442
0 0 1441
0 2 1376
0 1 1364
0 3 1331

DMosher

 

[John Spencer]

Reverse the < to >

SELECT *
, 1 + (SELECT Count(*)
FROM [SomeTable] as TEMP
WHERE Temp.[Premium] > [SomeTable].[Premium])
FROM [SomeTable]
ORDER BY [Premium]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

How would I modify the SQL to have the highest premium come in with a Rank of
1 and then in descending order, rank2, rank3 and so on.

DM

One method:
SELECT *
, 1 + (SELECT Count(*)
FROM [SomeTable] as TEMP
WHERE Temp.[Premium] < [SomeTable].[Premium])
FROM [SomeTable]
ORDER BY [Premium]


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am looking for SQL to rank the following data based on premium. Highest
premium = rank 1 and so on. Any ideas?

Policy Key Company Key Total Vehicle Premium
0 5 1801
0 4 1442
0 0 1441
0 2 1376
0 1 1364
0 3 1331

DMosher

 

[Clifford Bass]

Hi,

And to get it in proper order change the order by clause to:

ORDER BY [Premium] DESC

Clifford Bass