MySQL-query question

[Marc Vanzegbroeck]

Hi,

I have a question about a SQL-query.
I have a table with invoice that with a field that contains the amount.

My client ask me to calculate to total in a period for each client. So far no problem, but he also want to know 'the ranking' of that client.
So if client (a) have invoices for 1000€ , (b) for 1500€,(c) for 1600, (d) for 800, he want to know that client (a) is on the 3the place.

I can easely create a recordset with the sum(totaal) AS vtotaal GROUP BY client-clause with the totals for each client, but I don't know how to extract the 'ranking'.
I was thinking to add also add 'SORT ON vtotaal', but than I need to add the recordnumber in that recordset for each record.

[ADutheil]

Try to add ORDER BY vtotaal to your query. You´ll get the totals ranked.

Code: Select all  Expand view

SELECT field1, field2, SUM(fieldn) AS field3 FROM tablename GROUP BY field1 ORDER BY field3;

But I think if you use GROUP you can´t keep the record number without creating a stored procedure to keep them into na array.

[nageswaragunupudi]

This query is a sample using customer table in MySql. This customer table is imported into my mysql database from \fwh\samples\customer.dbf

Code: Select all  Expand view

  SELECT STATEID, SALARY, RANK
   FROM
   (
   SELECT SUMMARY.*, @prev := @curr, @curr := SUMMARY.SALARY,
   @rank := IF( @prev = @curr, @rank, @rank + 1 ) AS RANK
   FROM
   (
   SELECT `STATE` AS STATEID, SUM( SALARY ) AS SALARY
   FROM CUSTOMER
   GROUP BY `STATE`
   ORDER BY SALARY DESC
   ) AS SUMMARY, ( SELECT @rank := 0 ) V
   ) RANKED
   ORDER BY STATEID
 

Note:
1. Ranking is done in descending order. That is the highest value gets rank no.1. For ranking in ascending order remove "DESC" in the above query.
2. Two or more items having the same value have the same ranking no. This is the standard

This query is for MySql



You can choose to display final results in any order. I presented in the order of statecode.

[nageswaragunupudi]

For the sake of academic interest and for those who are interested, ranking in MSSQL.

Code: Select all  Expand view

static function mssqlrank

   local oCn, oRs, cSql

   oCn   := FW_OpenAdoConnection( "MSSQL,SQLEXPRESS,FWH,SA,mypassword" )

   TEXT INTO cSql
   SELECT STATEID,SALARY,RANK() OVER (ORDER BY SALARY DESC)
   FROM
   (
   SELECT [STATE] AS STATEID,SUM(SALARY) AS SALARY
   FROM CUSTOMER C
   GROUP BY [STATE]
   ) SUMMARY
   ORDER BY STATEID
   ENDTEXT

   oRs   := FW_OpenRecordSet( oCn, cSql )
   xbrowser oRs
   oRs:Close()
   oCn:Close()

return nil

Again I used customer table imported into my SQLEXPRESS from customer.dbf in fwh samples folder.

We may compare results of MySql query above with built-in Rank() function of MsSql.

[nageswaragunupudi]

My above postings are basically for academic interest.
Now, my personal advice is that let us not always spend too much time how to accomplish certain complex tasks through SQL only. We have a highly powerful (x)Harbour in our hands.

For example, for ranking we can use the power of our Arrays.
Copy Unique Values into an array. Sort the array in Ascending or Descending order based on our system of ranking.

Once done, AScan( aArray, nValue ) --> nRank

Here is a sample

Code: Select all  Expand view

static function Ranking

   local oCn, oRs, cSql
   local aVals  := {}, nVal

   oCn   := FW_OpenAdoConnection( "MSSQL,SQLEXPRESS,FWH,SA,mypassword" )

   TEXT INTO cSql
   SELECT [STATE] AS STATEID, SUM(SALARY) AS SALARY
   FROM CUSTOMER
   GROUP BY [STATE]
   ENDTEXT

   oRs := FW_OpenRecordSet( oCn, cSql )
   oRs:MoveFirst()
   do while ! oRs:Eof()
      nVal  := oRs:Fields( "Salary" ):Value
      if AScan( aVals, nVal ) == 0
         AAdd( avals, nVal )
      endif
      oRs:MoveNext()
   enddo
   oRs:MoveFirst()

   ASort( aVals, , , { |x,y| x > y } )  // Descending Order
   // Now nRank := AScan( aVals, nSalary )

   xbrowser oRs COLUMNS "STATEID", "SALARY", { || AScan( aVals, oRs:Fields( "Salary" ):Value ) } ;
      SETUP oBrw:cHeaders := { "StateID", "Salary", "Rank" }

   oRs:Close()
   oCn:Close()

return nil
 

Let us compare results:


Not only in this case, but in general, let us add the power of (x)Harbour and FWH with SQL to the extent we know, instead of losing time on finding ways to do everything with SQL.

[Marc Vanzegbroeck]

This query is a sample using customer table in MySql. This customer table is imported into my mysql database from \fwh\samples\customer.dbf

Code: Select all  Expand view

  SELECT STATEID, SALARY, RANK
   FROM
   (
   SELECT SUMMARY.*, @prev := @curr, @curr := SUMMARY.SALARY,
   @rank := IF( @prev = @curr, @rank, @rank + 1 ) AS RANK
   FROM
   (
   SELECT `STATE` AS STATEID, SUM( SALARY ) AS SALARY
   FROM CUSTOMER
   GROUP BY `STATE`
   ORDER BY SALARY DESC
   ) AS SUMMARY, ( SELECT @rank := 0 ) V
   ) RANKED
   ORDER BY STATEID
 

Note:
1. Ranking is done in descending order. That is the highest value gets rank no.1. For ranking in ascending order remove "DESC" in the above query.
2. Two or more items having the same value have the same ranking no. This is the standard

This query is for MySql



You can choose to display final results in any order. I presented in the order of statecode.

Thank you for the example. I will try it.

[ADutheil]

Marc,

Here is the way to keep the recordnumber (SQL_ROWID in my sample) without stored proc.

Code: Select all  Expand view

SELECT STATEID, SALARY, RANK, IDS
   FROM
   (
   SELECT SUMMARY.*, @prev := @curr, @curr := SUMMARY.SALARY,
   @rank := IF( @prev = @curr, @rank, @rank + 1 ) AS RANK
   FROM
   (
   SELECT `STATE` AS STATEID, SUM( SALARY ) AS SALARY, GROUP_CONCAT( SQL_ROWID ) AS IDS
   FROM customer
   GROUP BY `STATE`
   ORDER BY SALARY DESC
   ) AS SUMMARY, ( SELECT @rank := 0 ) V
   ) RANKED
   ORDER BY LPAD(RANK,7, " ");

Result

Code: Select all  Expand view

"STATEID"   "SALARY"    "RANK"  "IDS"
"NE"    "1407200,00"    "1" "315,418,214,195,295,84,232,370,490,145,249,385,55,475,99,335"
"NY"    "1323200,00"    "2" "432,329,17,158,487,365,260,112,126,451,468,209,244"
"IN"    "1256800,00"    "3" "45,11,106,376,135,302,321,238,121,254,150,202,184,392,169"
"HI"    "1236000,00"    "4" "389,118,251,181,353,442,57,298,9,405,23,87,166,493"
"AR"    "1166600,00"    "5" "361,171,326,91,62,305,49,108,241,499,287,271,343,14,483"
"OK"    "1096200,00"    "6" "149,479,301,58,42,168,236,120,284,424,200,320,88"
"IL"    "1088600,00"    "7" "275,33,227,309,175,4,1,347,381,191,399,141,80"
"MT"    "1082700,00"    "8" "379,466,485,225,94,173,51,243,64,125"
"WI"    "1055400,00"    "9" "319,354,24,494,406,133,267,235,390,72,282"
"DE"    "988100,00" "10"    "316,296,215,22,233,265,250,439,386,457,70,146,56"
"KY"    "982900,00" "11"    "470,415,367,97,278,160,211,114,19,331,348,434,246"
"SC"    "979700,00" "12"    "107,324,122,152,427,482,393,342,47,221,377"
"RI"    "957500,00" "13"    "102,216,477,441,387,86,338,71,8"
"NC"    "956500,00" "14"    "240,90,28,463,76,256,61,447,410,13"
"WA"    "951400,00" "15"    "323,151,360,27,286,270,136,12,498,220,186,255,303"
"LA"    "931600,00" "16"    "332,82,416,230,20,311,129,471,67,349,383,143,193,161"
"WY"    "911700,00" "17"    "128,81,400,293,469,277,142,261,18,3"
"FL"    "894300,00" "18"    "183,357,408,134,375,253,391,25,445,43"
"WV"    "883900,00" "19"    "7,351,264,100,456,337,437,164,476,131"
"NJ"    "856100,00" "20"    "252,119,339,460,199,443,423,182,148,299,355"
"MN"    "831300,00" "21"    "481,497,185,239,89,446,285,409"
"CA"    "808600,00" "22"    "53,159,452,382,66,229,310,192,34,210,113,488,176"
"GA"    "791800,00" "23"    "223,288,500,156,448,172,307,465,258,429"
"PA"    "764600,00" "24"    "362,327,50,188,109,30,63,138,484"
"VT"    "750200,00" "25"    "259,380,32,207,111,396,16,290,189,308,364"
"ID"    "748900,00" "26"    "231,248,5,83,369,162,454,194,474,130,36,435"
"NH"    "746100,00" "27"    "266,440,317,372,132,165,117,420,281,40,492,297,234"
"OR"    "745300,00" "28"    "411,205,272,394,123,154,428,187,137"
"UT"    "736000,00" "29"    "139,449,110,412,273,363,157,345,206"
"CT"    "675900,00" "30"    "436,419,116,37,263,6,336,371,163"
"OH"    "662600,00" "31"    "85,39,458,101,404,352,180,197"
"MD"    "636700,00" "32"    "147,388,198,421,318,459,103,373"
"MS"    "632800,00" "33"    "78,289,93,31,224,430,15,344,395,124"
"SD"    "602900,00" "34"    "144,334,384,489,262,247,313,453,401,473,212"
"TN"    "598800,00" "35"    "203,75,60,46,462,26,359,341"
"AL"    "591300,00" "36"    "425,105,218,74,340,358,496"
"AK"    "591100,00" "37"    "283,73,495,104,407,10,356,300,444,268"
"NM"    "583000,00" "38"    "461,237,44,269,219,59,480,201"
"AZ"    "542900,00" "39"    "226,330,52,398,96,291,245,414"
"KS"    "526400,00" "40"    "306,29,77,378,92,155,257,242"
"MA"    "513500,00" "41"    "276,292,127,433,366,228,2"
"IA"    "510000,00" "42"    "294,333,68,54,368,417,177,472,35,312"
"NV"    "495700,00" "43"    "140,346,328,95,208,450,431,190,413"
"ME"    "495000,00" "44"    "403,280,179,38,438,196,491"
"MI"    "462500,00" "45"    "374,41,422,167,478,217"
"ND"    "449600,00" "46"    "98,279,115,455,314,350"
"CO"    "423700,00" "47"    "153,304,204,464,48,222,325"
"TX"    "394200,00" "48"    "467,65,486,274,397,174,79"
"VA"    "306100,00" "49"    "178,213,21,402,69"
"MO"    "212400,00" "50"    "170,322,426"
 

[Marc Vanzegbroeck]

Marc,

Here is the way to keep the recordnumber (SQL_ROWID in my sample) without stored proc.

Thanks Andre,

Is SQL_ROWID a field? I get 'Unknown column SQL_ROWID' error.

[ADutheil]

Yes it is the field that stores the id of each record from the table. It´s a primary key I created to fake Harbour recno().