p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   Find Out First Item of each item Group (http://p2p.wrox.com/showthread.php?t=6517)

Jane November 22nd, 2003 07:15 AM

Find Out First Item of each item Group
 
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

planoie November 22nd, 2003 12:42 PM

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.


All times are GMT -4. The time now is 12:45 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.