Assign Record Numbers in a query

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

Guest

I am attempting to rank values and assign a number to each. I have sorted
the values assending, but I can't figure out how to start the first record at
1 and add 1 to each record until I get the maximum number.
 
This will do ranking in a group --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Try this for single group ---
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.Points >= Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;
 
Hi Karl,
I appreciate your help. I guess I am a bit lost. I have a table "T1" with
a field "F1". There are 1000 + percentages in field "F1". I need to sort
them from highest to lowest and assign a Rank to each one so the highest is
"1", next is "2", until I get to the end. Could you help just a bit more?
Thank you,
Rick


KARL DEWEY said:
This will do ranking in a group --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Try this for single group ---
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.Points >= Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


SoCal Rick said:
I am attempting to rank values and assign a number to each. I have sorted
the values assending, but I can't figure out how to start the first record at
1 and add 1 to each record until I get the maximum number.
Click to expand...
Click to expand...
 
Try this ---
SELECT Q.F1, (SELECT COUNT(*) FROM [T1] Q1
WHERE Q1.F1 > Q.F1)+1 AS Rank
FROM [T1] AS Q
ORDER BY Q.F1 DESC;

--
KARL DEWEY
Build a little - Test a little


SoCal Rick said:
Hi Karl,
I appreciate your help. I guess I am a bit lost. I have a table "T1" with
a field "F1". There are 1000 + percentages in field "F1". I need to sort
them from highest to lowest and assign a Rank to each one so the highest is
"1", next is "2", until I get to the end. Could you help just a bit more?
Thank you,
Rick


KARL DEWEY said:
This will do ranking in a group --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Try this for single group ---
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.Points >= Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


SoCal Rick said:
I am attempting to rank values and assign a number to each. I have sorted
the values assending, but I can't figure out how to start the first record at
1 and add 1 to each record until I get the maximum number.
Click to expand...
Click to expand...
Click to expand...
 
Karl,
You are a genius!
Thank you,
Rick

KARL DEWEY said:
Try this ---
SELECT Q.F1, (SELECT COUNT(*) FROM [T1] Q1
WHERE Q1.F1 > Q.F1)+1 AS Rank
FROM [T1] AS Q
ORDER BY Q.F1 DESC;

--
KARL DEWEY
Build a little - Test a little


SoCal Rick said:
Hi Karl,
I appreciate your help. I guess I am a bit lost. I have a table "T1" with
a field "F1". There are 1000 + percentages in field "F1". I need to sort
them from highest to lowest and assign a Rank to each one so the highest is
"1", next is "2", until I get to the end. Could you help just a bit more?
Thank you,
Rick


KARL DEWEY said:
This will do ranking in a group --
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Try this for single group ---
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.Points >= Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

--
KARL DEWEY
Build a little - Test a little


:

I am attempting to rank values and assign a number to each. I have sorted
the values assending, but I can't figure out how to start the first record at
1 and add 1 to each record until I get the maximum number.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top