Ranking

  • Thread starter Thread starter DMosher
  • Start date Start date
D

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
 
I see 7 potential field names with 3 columns of data. I don't see the value
you expect to see calculated.
 
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
 
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
 
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
 
Question..what is in the tblRank?

Clifford Bass said:
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 said:
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
Click to expand...
Click to expand...
 
Hi,

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

Clifford Bass
 
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


DMosher said:
Question..what is in the tblRank?

Clifford Bass said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

John Spencer said:
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
Click to expand...
Click to expand...
 
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

John Spencer said:
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
Click to expand...
Click to expand...
Click to expand...
 
Hi,

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

ORDER BY [Premium] DESC

Clifford Bass
 
Back
Top