Hi,

I have this query:

SELECT
 EXTRACT(YEAR FROM XDate) as YearNo,
 EXTRACT(MONTH FROM XDate) as MonthNo,
 SUM(IntNumber) as IntNumber,
 UserName
FROM jobs
GROUP BY YearNo, MonthNo, UserName
ORDER BY YearNo, MonthNo, IntNumber DESC

It returns something like this:

2007 6 500 Paul....
2007 6 399 Randy...
2007 6 345 Tom.....
2007 6 299 I don't want this row
2007 6 295 I don't want this row
2007 6 256 I don't want this row
2007 5 800 Greg....
2007 5 689 Zoran...
2007 5 556 Edy.....
2007 5 398 I don't want this row
2007 5 201 I don't want this row
2007 4 700 Greg....
2007 4 449 Randy...
2007 4 256 Tom.....
2007 4 198 I don't want this row
2007 4 101 I don't want this row

However, I only want only the top 3 rows for each group Year, Month. Is it possible in SQL?

TIA
Lucian

Lucian,

ORDER BY YearNo, MonthNo, IntNumber DESC

Just add

Range 1 to 3

to your query.

regards Uli

Ulrich

>Just add
>
>Range 1 to 3

Does that work for each group? I thought it was for the query as a whole.

Roy Lambert [Team Elevate]

Roy,

> Does that work for each group? I thought it was for the query as a whole.

You are right: I should have read more carefully.

Uli

>Range 1 to 3

As Roy pointed out, that does not work.
regards
Lucian

I have some old DBISAM code that I'm converting to ElevateDB. There, after running the query, I was manually removing unwanted records from the result ... this however is something that does not work in ElevateDB anymore.

Lucian,

> As Roy pointed out, that does not work.

I am not an expert in SQL, but I fount this:

http://rickosborne.org/blog/2008/01/sql-getting-top-n-rows-for-a-grouped-query/

Maybe it'll give you some hints how to proceed.

Regards Uli

Uli


Brilliant link. Looks like a good book as well. I might just treat myself to a second hand copy unless we can persuade John Hay to write a DBISAM / ElevateDB specific one.

Roy Lambert

Lucian

>I have some old DBISAM code that I'm converting to ElevateDB. There, after running the query, I was manually removing unwanted records from the result ... this however is something that does not work in ElevateDB anymore.

I personally think its sad that canned (non-sensitive) queries can't be edited. The options are now to create either a temporary table or an in-memory table and edit that.

I've tended to use in-memory tables. A bit more difficult to create than with DBIASM but Tim gave me a script which I modified and now use. Temporary tables are a doddle to create (look up CREATE TEMPORARY TABLE) and the reason I don't use them is I like the self cleaning nature of in-memory tables in the event of a crash.

Roy Lambert

Roy,

<< unless we can persuade John Hay to write a DBISAM / ElevateDB specific one. >>

I'd like to place my order for that one without even knowing the price.

Uli