Rank Order

Results 1 to 3 of 3

Thread: Rank Order

  1. #1
    Join Date
    Dec 1969

    Default Rank Order

    This may be a little long, so please bear with me:<BR><BR>I want to rank records in order, and then submit a final rank score, although this seems easy I can&#039;t seem to get it to work because of the structure of my database:<BR><BR>Here&#039;s how it works, I have 4 tables:<BR><BR>user, company, products, selectedProducts<BR><BR>with the following structure<BR><BR>user(user_id, name...)<BR>company(company_id, user_id, name, balance)<BR>selectedProducts(selectedProd_id, user_id, product_id)<BR>products(product_id, name, price)<BR><BR>Now what I want to do is give a rank to the user on thir overall income against other companies (i.e. rank on thier balance and total Price of thier selectedProducts combined)<BR><BR>for example:<BR><BR>If a company balance is 10000<BR><BR>and they have seleted 4 products, when the prices are looked up in the products table they all cost 5000. This will then give the user a totalWorth of 30000 (10000 + (5000*4)), and so I want to rank this total against the other company totals.<BR><BR>Is there a simple solution to this, I tried setting up a few nested loops using 4 recordsets, but it causes an exception<BR><BR>Thanks for you help, and willingness to put up with my somewhat dubious explination<BR><BR>Regards<BR>Craig

  2. #2
    Join Date
    Dec 1969
    Los Angeles, CA

    Default Dont think i totally understand

    but how about <BR><BR><BR>well one way is to do this in the front end and throw the results in an array and then order the array<BR><BR><BR>from the back end....<BR><BR>well i guess you pass 4 to the sp but then were do you get the 5000 is it the total costs of the 4 products or do they all cost the same??<BR><BR>where did you get the 30000??<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default Not too hard...

    ...if I understand the question correctly.<BR><BR>Let&#039;s do it in pieces.<BR><BR>Ranking on *only* the company balances, we have this:<BR><BR>SELECT U.name, C.balance<BR>FROM [user] AS U, company AS C<BR>WHERE C.user_id = U.UserID<BR>ORDER BY C.balance<BR><BR>And then ranking by just the sum of the product prices:<BR><BR>SELECT U.name, SUM(P.price) AS TotalPrice<BR>FROM [User] AS U, selectedProducts AS SP, products AS P<BR>WHERE P.product_id = SP.product_id<BR>AND SP.user_id = U.user_id<BR>GROUP BY U.name<BR>ORDER BY TotalPrice<BR><BR>So now the trick is putting the two of those together.<BR><BR>I don&#039;t know if this will work or not, but if it does, you are done:<BR><BR>SELECT U.name, C.balance + SUM(P.price) AS Rank<BR>FROM [user] AS U, company AS C, selectedProducts AS SP, products AS P<BR>WHERE C.user_id = U.UserID<BR>AND P.product_id = SP.product_id<BR>AND SP.user_id = U.user_id<BR>GROUP BY U.name<BR>ORDER BY RANK<BR><BR>The difficulty in that might be in the addition of the balance to the SUM aggregate function. I dunno if whatever DB you are using is clever enough to do that.<BR><BR>If not, then if the DB supports VIEWs or temp tables, you could use the second query to create a view/temp table and then join that view/temp table with the first query easily enough.<BR><BR>What DB are we talking about here?<BR><BR>(And if it&#039;s Access, you might have to alter the ORDER BY clauses to "ORDER BY 2" but that&#039;s a detail.)<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts