Group By, Limit only group?

Greetings.

I'm looking to select data from a table and group by some values.. but
I'm looking to limit the number of rows returned for each group, but
not the entire query. Here's my table set up (Not from MySQL, just
typed up.. but it'll give you the idea):

CREATE TABLE bank_ident_all_t(
ident_id INT(9) UNSIGNED ZEROFILL NOT NULL,
ident_type ENUM('ach','cc') NOT NULL,

bank_id INT UNSIGNED NOT NULL,

ref_count INT UNSIGNED NOT NULL DEFAULT 1,

last_updated INT UNSIGNED NOT NULL,

PRIMARY KEY(ident_id, ident_type, bank_id),
INDEX(ident_id, ident_type)
) Type=MyISAM;

Now, in the query I'm looking to do something like:

SELECT * from bank_ident_all_t
GROUP BY ident_id, ident_type
ORDER BY ident_id, ident_type, ref_count;

But I only want the first three results for each bank_id in this
group.

Currently, I've got two ways look at doing this, that work:

1) Create a procedure and use cursors and just process the data. When
hitting a count of three for the same ident_id, ident_type; keep
reading and ignore rows until we hit a new ident_id, ident_type.

2) Select the ident_id, ident_type and do a sub query to select where
ident_id, ident_type match, order by ref_count limit 3.

If there's a more efficient way to do this, please let me know.
Solutions 1 and 2 both work.. I'm interested to know if anyone may
have other suggestions as to how this can be done. I'm also open for
any discussion or questions that may arise. I'd like to do this with
as few passes as possible, as the table may grow quite large. This is
being used for a report generation of the three most popular entries
in the table for a given ident_id and ident_type.. but mostly for fun
and as a learning experience.

Regards,
Michael Martinek

On Nov 10, 12:40 am, Michael Martinek <michael.marti...@gmail.com>
wrote:
> Greetings.
>
> I'm looking to select data from a table and group by some values.. but
> I'm looking to limit the number of rows returned for each group, but
> not the entire query. Here's my table set up (Not from MySQL, just
> typed up.. but it'll give you the idea):
>
> CREATE TABLE bank_ident_all_t(
> ident_id INT(9) UNSIGNED ZEROFILL NOT NULL,
> ident_type ENUM('ach','cc') NOT NULL,
>
> bank_id INT UNSIGNED NOT NULL,
>
> ref_count INT UNSIGNED NOT NULL DEFAULT 1,
>
> last_updated INT UNSIGNED NOT NULL,
>
> PRIMARY KEY(ident_id, ident_type, bank_id),
> INDEX(ident_id, ident_type)
> ) Type=MyISAM;
>
> Now, in the query I'm looking to do something like:
>
> SELECT * from bank_ident_all_t
> GROUP BY ident_id, ident_type
> ORDER BY ident_id, ident_type, ref_count;
>
> But I only want the first three results for each bank_id in this
> group.
>


Similar to method two, you might consider this syntax: http://tinyurl.com/yvesd2