unique values combined with COUNT()

[preek]

Hi there,

The following statement produces multiple results, but I only need one.

SELECT CREDITOR_NAME, CREDITOR_NO, INV_ID, TRAIN_COUNT, COUNT(CREDITOR_NO) as number_of_inv
FROM DIPPMX.ADMIN.TI$ST_RECO
GROUP BY CREDITOR_NAME, INV_ID, TRAIN_COUNT, CREDITOR_NO
HAVING COUNT(CREDITOR_NO) >= 5

Normally I would include the CREDITOR_NAME in MIN(), but this wont work due to the COUNT(CREDITOR_NO). Any help is appreciated.

Thanks in advance,
Alain

[code green]

You probably need a WHERE condition to filter your result.
You haven't explained which one result you need and why, but you could use TOP.

[preek]

I have found my solution; I actually did use MIN() as suggested:

SELECT CREDITOR_NAME, CREDITOR_NO, MIN(INV_ID), COUNT(CREDITOR_NO) as no_of_inv
FROM DIPPMX.ADMIN.TI$ST_RECO
GROUP BY CREDITOR_NAME, CREDITOR_NO
HAVING COUNT(CREDITOR_NO) >= 3

[code green]

OK. Well done.
So you needed the lowest INV_ID number. This is generally the oldest.
I have to admit I cannot see what you are trying to do.
It helps future posters who may have a similar problem if you explain why you need such a specific result