How do you show only the Top N results in a grid ?

Scriptcase 8 Discussion
#1

I have been struggling to show only the top 10 results in a grid application. I created my own SQL statement that returns the top 10 totals descending:

SELECT
cl.clName,
sum(mi.mntTotalCharge) as total ,
mi.mntMonth,
mi.mntYear
FROM
Clients cl INNER JOIN Agreements agr ON cl.cID = agr.cID
INNER JOIN MonthlyInvoices mi ON agr.agrID = mi.agrID
where mi.mntMonth = Month(now()) and mi.mntYear =year(now())
Group by cl.clName,mi.mntYear,mi.mntMonth
order by total DESC
limit 10

With reference to :
http://www.scriptcase.net/blog/mysql-limit-query/
and
http://www.scriptcase.net/forum/foru…id-application

How can this be done ?

Scriptcase changes the query to :

[SIZE=12px]SELECT clName, mntMonth, mntYear, total from
(SELECT cl.clName, sum(mi.mntTotalCharge) as total , mi.mntMonth, mi.mntYear
FROM Clients cl INNER JOIN Agreements agr ON cl.cID = agr.cID INNER JOIN MonthlyInvoices mi ON agr.agrID = mi.agrID where mi.mntMonth = Month(now()) and mi.mntYear =year(now())
Group by cl.clName,mi.mntYear,mi.mntMonth limit 10) nm_sel_esp [/SIZE][SIZE=12px]order by total DESC[/SIZE]

[SIZE=12px]​This returns 10 records, but the first 10 alphabetically not the top 10 totals.

The query should be: [/SIZE]

[SIZE=12px]SELECT clName, mntMonth, mntYear, total from
(SELECT cl.clName, sum(mi.mntTotalCharge) as total , mi.mntMonth, mi.mntYear
FROM Clients cl INNER JOIN Agreements agr ON cl.cID = agr.cID INNER JOIN MonthlyInvoices mi ON agr.agrID = mi.agrID where mi.mntMonth = Month(now()) and mi.mntYear =year(now())
Group by cl.clName,mi.mntYear,mi.mntMonth [/SIZE]order by total DESC[SIZE=12px] limit 10) nm_sel_esp [/SIZE]

I have disabled sorting of the grid.

Is this a bug or am I missing something ?

Update I have created a view as a workaround but this is not the most elegant solution…

#2

hi, did you try to add order by total asc ? or order by ‘total’ ASC

#3

Hi Mike , no I have not , I am trying to get the Top 10 descending totals , what would your suggestion accomplish ? I will give it a try…

#4

force the order by ASC, might override the DESC which you said scriptcase is adding

also for single quotes around the names, sometimes word is forbidden by mysql you have to add the quotes or to be used as string

try and let us know, otherwise, if scriptcase keep changing the sql statement without control over that, then address it as bug to them…

hmmm i remembered once sql was being changed by scritpcase then later noticed if crated fresh application it works fine, so assumed that was because of something else within the application and didn’t go through it… like group and similar stuff

#5

Thanks Mike, I have tried your suggestion and the result remains the same. I will use the view for now and address it with support if it becomes a major issue. I think to better articulate the bug to support I will try to reproduce the result with one of the sample applications.

#6

I could not reproduce the result in the samples database, the following SQL to return the Top 10 order totals for the year executed in a scriptcase grid application as expected:
SELECT
c.companyname,
sum(od.totalprice) as total,
year(o.orderdate) as years

FROM
local.customers c INNER JOIN local.orders o ON c.customerid = o.customerid
INNER JOIN local.order_details od ON o.orderid = od.orderid
where year(o.orderdate) = ‘2008’
group by c.companyname, o.orderdate
order by total DESC
limit 10;

I changed my query(swapped the columns around with total at the end of the select clause) and created a grid application from scratch, disabling search,detail,summary and chart before running it for the first time. It executed as expected. I used the same query in a chart application and got SQL errors, so it is still not 100%. I would like to know the root cause of this behaviour…

#7

that’s is why, i told you before, sometimes could be according to something else… e.g. group by rule or any pitty thing, summary, totals…etc. if worked with you in scratch application then either rebuild your Grid and let go, or digg into all changes, i would go with scratch application, rename as the old one, kept the old one in my archive folder and upload the new app to prod instead of current one