Hi,

I have a table as below:

item code
A01000
A01001
A0100A
A0100B
A0102B
A0102C
A0102D
B01041
B0104A
B0104C
.
.
.

from a table above, i want to list out a reault as this:


ITEM CODE
A01000
A0102B
B01041

I ONLY WANT TO LIST THE FIRST ITEM FOR THE ITEM IN THE SAME GROUPING OF THE FIRST 5 DIGITS.

HOW AM I WRITE A SQL TRANSACT AS ABOVE?


THANKS !


JANE

Try something like this:

SELECT MAX(itemcode) FROM tablename
GROUP BY SUBSTRING(itemcode, 0, 5)

Your syntax may vary depending on your DB platform. The important part is that you need to group by the desired portion of the field using the appropriate sub string syntax.

Peter
------------------------------------------------------
Work smarter, not harder.