[RESOLVED]Distinct Help

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

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

How about

Code:

---

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

---

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.

Quote:

---

Originally Posted by Hack

How about

Code:

---

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

---

---

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

Code:

---

1234
1234
1234
2222
2222

---

The GROUP BY will weed out the duplicates.

Both examples worked for me. So i'm happy

Quote:

---

Originally Posted by Hack

The GROUP BY will weed out the duplicates.

---

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

should't it should be

Code:

---

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

---

or

Code:

---

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

---

:blush:

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

Code:

---

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

---

Quote:

---

Originally Posted by drawlings

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

Code:

---

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 ;)

Thanks mate, will remove the Group By Clause