topic-27220

Board index FiveWin for Harbour/xHarbour MySQL-query question

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

MySQL-query question
Posted: Fri Sep 13, 2013 06:20 AM

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

Posts: 368

Joined: Sun May 31, 2009 06:25 PM

Re: MySQL-query question
Posted: Fri Sep 13, 2013 10:11 AM

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

Code (fw): Select all Collapse

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.

Regards,

André Dutheil

FWH 13.04 + HB 3.2 + MSVS 10

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: MySQL-query question
Posted: Fri Sep 13, 2013 10:14 AM

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 (fw): Select all Collapse

   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.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: MySQL-query question
Posted: Fri Sep 13, 2013 10:52 AM

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

Code (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: MySQL-query question
Posted: Fri Sep 13, 2013 12:31 PM

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 (fw): Select all Collapse

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.

Regards

G. N. Rao.

Hyderabad, India

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: MySQL-query question
Posted: Fri Sep 13, 2013 01:14 PM

nageswaragunupudi wrote: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 (fw): Select all Collapse
   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

Posts: 368

Joined: Sun May 31, 2009 06:25 PM

Re: MySQL-query question
Posted: Fri Sep 13, 2013 05:18 PM

Marc,

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

Code (fw): Select all Collapse

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 (fw): Select all Collapse

"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"

Regards,

André Dutheil

FWH 13.04 + HB 3.2 + MSVS 10

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: MySQL-query question
Posted: Fri Sep 13, 2013 05:35 PM

ADutheil wrote: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

Posts: 368

Joined: Sun May 31, 2009 06:25 PM

Re: MySQL-query question
Posted: Fri Sep 13, 2013 06:28 PM

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().

Regards,

André Dutheil

FWH 13.04 + HB 3.2 + MSVS 10

FiveTech Support Forums

Continue the discussion