"Michael Vardinghus" <michaelvardinghus@hotmail.com> wrote in message
news:uDNhWo8JFHA.4028@tk2msftngp13.phx.gbl...

"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:eCbksY9JFHA.1176@TK2MSFTNGP15.phx.gbl...
> Try:
>
> select
> left (Code, 7) as Code
> , Name
> from
> MyTable o
> where
> o.Code =
> (
> select
> min (i.Code)
> from
> MyTable i
> where
> left (i.Code, 7) = left (o.Code, 7)
> )
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Michael Vardinghus" <michaelvardinghus@hotmail.com> wrote in message
> news:uDNhWo8JFHA.4028@tk2msftngp13.phx.gbl...
> Hi All
>
> Have a table with this structure
>
> Code Name
>
> 100000010 Project A - area 1
> 100000011 Projekt A - area x
> 200000010 Project B - area 1
> 200000011 Projekct - area x
> ..
>
> From this I would like to create a new table with this structure
>
> 1000000 Project A - area 1
> 2000000 Project B - area 1
>
> I need to get left(code,7) but my problem is how to select one of the name
s
> and attaching this to the 7 digit code. In my example below I just chose
the
> first Name I encounter - I cannot
> take x characteres because this differ.
>
> But is it possible to select the name from the first...if so - how ?
>
> /Michael V.
>
>