0

I have a complex query that requires a rank in it. I've learned that the standard way of doing that is by using the technique found on this page: http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/. I'm using Infobright as the back end and it doesn't work quite as expected. That is, while a standard MySQL engine would show the rank as 1, 2, 3, 4, etc... Brighthouse (Infobright's engine) would return 1, 1, 1, 1, etc.... So I came up with a strategy of setting a variable, a function, and then execute it in the query. Here's a proof of concept query that does just that:

SET @rank = 0;

DROP FUNCTION IF EXISTS __GetRank;
DELIMITER $$
CREATE FUNCTION __GetRank() RETURNS INT
BEGIN
    SET @rank = @rank + 1;
    return @rank;
END$$

DELIMITER ;

select __GetRank() AS rank, id from accounts;

I then copied and pasted the function into Jasper Report's iReport and then compiled my report. After executing it, I get syntax errors. So I thought that perhaps the ; was throwing it off. So at the top of the query, I put in DELIMITER ;. This did not work either.

Is what I'm wanting to do even possible? If so, how? And if there's an Infobright way of getting a rank without writing a function, I'd be open to that too.

Johan
  • 74,508
  • 24
  • 191
  • 319
Jason Thompson
  • 4,643
  • 5
  • 50
  • 74

2 Answers2

0

Infobright does not support functions. From the site: http://www.infobright.org/forums/viewthread/1871/#7485

Indeed, IB supports stored procedures, but does not support stored functions nor user defined functions.

select if(@rank is null,@rank:= 0,@rank:= @rank +1) as rank, id from accounts

Does not work, because you cannot write to @vars in queries.

This:

 SELECT 
   (SELECT COUNT(*) 
    FROM mytable t1 
    WHERE t1.rankedcolumn > t2.rankedcolumn) AS rank, 
 t2.rankedcolumn
 FROM mytable t2 WHERE ...;

will work, but is very slow of course.

Disclaimer, not my code, but Jakub Wroblewski's (Infobright founder)

Hope this helps...

Community
  • 1
  • 1
Johan
  • 74,508
  • 24
  • 191
  • 319
  • Thank you for the answer, but it's not exactly what I'm looking for. That works great MySQL, but not using the brighthouse (Infobright) engine. It would display rank as 1 for all elements in brighthouse. – Jason Thompson Apr 14 '11 at 15:15
  • Have you tested it?, because then the output should be all 0's not 1's. – Johan Apr 14 '11 at 15:21
  • Alternatively you can write a stored procedure that writes to a temp table and then read the temp table. – Johan Apr 14 '11 at 15:51
  • Changed the answer to reflect infobright specific stuff, why is there **no** tag for infobright? – Johan Apr 14 '11 at 15:52
  • I tried tagging it infobright, however; it said I had to have 1500 reputation points before creating my own tag. Strange that it's never been created before. – Jason Thompson Apr 15 '11 at 15:50
  • I requested a tag "infobright", got it a few minutes later :-) until the tag was moderated and removed :-(. – Johan Apr 15 '11 at 17:08
  • I'd definitly suggest putting infobright in the title though, maybe the moderator will wake up and allow the tag to be added. – Johan Apr 15 '11 at 17:10
  • Infobright apparently does allow for functions. I finally figured out a solution. See below if you're curious. – Jason Thompson Apr 24 '11 at 21:21
0

Here's how I solved this. I had my server side program execute a mysql script. I then took the output and converted it to a CSV. I then used this as the input data for my report. A little convoluted, but it works.

Jason Thompson
  • 4,643
  • 5
  • 50
  • 74