Ranking results

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

Guest

I have a tbale consisting of premium (currency), segment (text) and office
(text). I want to run a query to rank the segments within each office. Any
suggestions? I'm not an sql wiz, unforutnately.
 
One more question, I modified your sql and it worked nicely, now I want to
throw one more variable into the mix, REGION. I can't get this one right,
please help. Here's the current sql, just need to add REGION so that each
office is now sorted by top premium by segment and region.

SELECT top25.segment, top25.premium, top25.office, CStr((SELECT COUNT(*) FROM
top25 AS A WHERE A.segment = top25.segment AND
A.premium > top25.premium)+(SELECT COUNT(*)
FROM top25 AS A WHERE A.segment < top25.segment AND
A.premium = top25.premium)+1) & IIf((SELECT
COUNT(*) FROM top25 AS A WHERE A.office <> top25.office AND
A.segment = top25.segment AND A.premium =
top25.premium)=0,'',' (tie)') AS RankWithinPortfolio INTO tblSeamless
FROM top25
GROUP BY top25.segment, top25.premium, top25.office
ORDER BY top25.segment, top25.premium DESC;

EXAMPLE RESULTS FROM CURRENT QUERY WITHOUT REGION:
Segment premium office Rank
AB $47,322 969097 1
AC $22,879 919650 2
AD $19,453 969578 3
AF $17,981 986885 4
AG $30,030 956603 1
AI $20,719 927246 2
AK $18,241 927286 3
AL $17,306 970159 4
AM $8,784 987191 1
AV $8,617 913327 2
AW $7,533 946277 3
AZ $6,503 936498 4
 
James,

Do you know of a way to force rank so there is no ties. Even first record
to be chosen, I'm not picky.
 
DAN said:
James,

Do you know of a way to force rank so there is no ties. Even first record
to be chosen, I'm not picky.
Click to expand...


Long answer:

SELECT InvestmentAmount, PID, CID, CStr((SELECT COUNT(*) FROM
tblInvestments AS A WHERE A.PID = tblInvestments.PID AND
A.InvestmentAmount > tblInvestments.InvestmentAmount)+1) +
(SELECT COUNT(*) FROM tblInvestments AS A WHERE A.PID =
tblInvestments.PID AND A.CID < tblInvestments.CID AND
A.InvestmentAmount = tblInvestments.InvestmentAmount)
AS RankWithinPortfolio INTO tblSeamless2
FROM tblInvestments
GROUP BY InvestmentAmount, PID, CID, IID
ORDER BY PID, CID;

The last subquery adds on how many ties preceded it within the same PID.
I also removed a subquery from the previous answer that caused the
answers to be incorrect in certain cases when ties are not allowed. I
didn't go back to do more testing on the original SQL.

This SQL ran correctly on a small sample I tried. Also, the CStr()
enclosure can be removed since the '(tie)' text isn't being added
anymore. Ties will go to the smaller CID's.

James A. Fortune
(e-mail address removed)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate rank based upon multiple fields 1
Ranking Results of a Query 9
How to finding student Rank and How to upload bulk marks of each student at a time. 1
Rank - Rankings - A Ranked List 3
Consistency Check 1
Query Rank Starting at 1 not 0 2
Ranking question 3
Modify Ranking Code 8

Back
Top