[RESOLVED]Distinct Help

[drawlings]

Hi all,

I need to know if this is possible. I want to run a select statement that selects only the first 4 characters of the field and make sure there are no duplicates.

Examples of some of the data:

1234-01
1234-02
1234-03
2222-01
2222-03

You see all I want returned is 1234 & 2222.

Any ideas? I have tried "Select Distinct left(Ref,4) from Table" but this does not work

Thanks

[riteshjain1982]

select DISTINCT left(Ref,4) from Table should work properly,can you tell us what ouput you get when you run this query?

[Hack]

How about

Select Left(Ref,4) from Table
GROUP BY ref

[riteshjain1982]

How about

Select Left(Ref,4) from Table
GROUP BY ref

this wont worked,considering OP's sample data it will return

1234
1234
1234
2222
2222

[drawlings]

Perfect they both worked. I did try that before posting the original comment and it did not work. Oh well, thanks for your help guys.

[Hack]

The GROUP BY will weed out the duplicates.

[riteshjain1982]

The GROUP BY will weed out the duplicates.

hack still i am confused how Group by query will give 1234 & 2222

should't it should be

Select Left(Ref,4) from Table
GROUP BY Left(Ref,4) 

or

Select DISTINCT Left(Ref,4) from Table
GROUP BY ref

[drawlings]

I have just looked at Hacks orignal post, I added the DISTINCT before the left command.

Select DISTINCT Left(Ref,4) from Table GROUP BY Ref

[riteshjain1982]

I have just looked at Hacks orignal post, I added the DISTINCT before the left command.

Select DISTINCT Left(Ref,4) from Table GROUP BY Ref

you already have DISTINCT clause so dont use Group by as it will be sort of overkilling your query

[drawlings]

Both examples worked for me. So i'm happy

[drawlings]

Thanks mate, will remove the Group By Clause