Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 To: Anders Wennerwik
  Where is Anders Wennerwik?
 Ross-on-Wye
 United Kingdom
 Anders Wennerwik
 Tags
Subject: RE: Ranking
Thread ID: 112413 Message ID: 112449 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: General VFP Topics
Date: Wednesday, November 08, 2006 5:49:22 PM         
   


> I forgot to add that I have a 4th column that should be filled with the ranking number i.e
> Argentina, Company A, 11 employees, ranked 3
> Argentina, Company B, 6 employees, ranked 6
> Argentina, Company C, 8 employees, ranked 4
> Argentina, Company D, 75 employees, ranked 1
> Argentina, Company E, 8 employees, ranked 4
> Argentina, Company F, 35 employees, ranked 2
>
> the ranking of the company of will be based on the number of employees in each company by country.
>
> Anders

Unfortunately in VFP there are no ranking functions as in SQL Server 2005, I can simulate something like that BUT it is no so good. Also does it matter for you if the last column doesn't have sequential numbers? Here one example:
CREATE CURSOR crsTest (Country C(20), Company C(20), Employees I)
INSERT INTO crsTest VALUES('Argentina','Company A', 11)
INSERT INTO crsTest VALUES('Argentina','Company B',  6)
INSERT INTO crsTest VALUES('Argentina','Company C',  8)
INSERT INTO crsTest VALUES('Argentina','Company D', 75)
INSERT INTO crsTest VALUES('Argentina','Company E',  8)
INSERT INTO crsTest VALUES('Argentina','Company F', 35)

SELECT crsTest.Country, Company, crsTest.Employees,;
       CEILING(Tbl1.Employees/crsTest.Employees) AS Rank;
       FROM crsTest;
INNER JOIN (SELECT Country, SUM(Employees) AS Employees FROM crsTest GROUP BY Country) Tbl1;
      ON crsTest.Country = Tbl1.Country;
ORDER BY crsTest.Country,Rank



Borislav Borissov



ENTIRE THREAD

Ranking Posted by Anders Wennerwik @ 11/8/2006 10:55:55 AM
RE: Ranking Posted by Noker Lodia @ 11/8/2006 12:04:46 PM
RE: Ranking Posted by Borislav Borissov @ 11/8/2006 12:09:13 PM
RE: Ranking Posted by Anders Wennerwik @ 11/8/2006 4:31:58 PM
RE: Ranking Posted by Borislav Borissov @ 11/8/2006 5:49:22 PM
RE: Ranking Posted by Tamar Granor @ 11/8/2006 10:46:59 PM
RE: Ranking Posted by Anders Wennerwik @ 11/9/2006 9:35:31 AM
RE: Ranking Posted by Borislav Borissov @ 11/9/2006 11:20:59 AM