SQL Question (First 3 records per field)

[Chris100682]

Hi,

How do I write my SQL query to pull the first 2 account numbers per promo no?? Please help me!!!

I have the attached table:

+--------+----------+
| promo | account |
+--------+----------+
| 22054 | 412986 |
| 22054 | 412987 |
| 22054 | 412988 |
| 22054 | 412989 |
| 22055 | 412990 |
| 22055 | 412991 |
| 22055 | 412992 |
| 22055 | 412993 |
| 22055 | 412994 |
| 22066 | 412995 |
| 22066 | 412996 |
| 22066 | 412997 |
| 22077 | 412998 |
| 22077 | 412999 |
| 22077 | 413000 |
| 22077 | 413001 |
| 22077 | 413002 |
+--------+----------+

Many Thanks
Chris

[kujawski]

mmm... there is a way ive done it before, but fogotten, i'll try and find it but a simple way would be.

select promo,min(account),max(account) from mytable
group by promo

which will output
22054 412986 412989
22055 412990 412994
22066 412995 412997
22077 412998 413002

ok not the first two, but two per promocode.

[kujawski]

ok found it

would be in the form:

select promo,account
from mytable
where (select count(*) from mytable as f
where f.promo = mytable.promo and f.account < mytable.account
) < 2

this will output

22054 412986
22054 412987
22055 412990
22055 412991
22066 412995
22066 412996
22077 412998
22077 412999

hope this helps!

[kujawski]

oh yeah forgot to say the 2 at the end is the number of records you want returned per promocode
so if you change it to 3, the first 3 per promocode will be out put and so on.

[Chris100682]

Thanks - A great help!!