A SQL Query Question!

[visual poet]

I have this query:

SELECT LEFT(CallerTelNumber, 2) AS Series
FROM Call
ORDER BY CallerTelNumber

The result is when I run it:

78
78
78
79
79
79
79
79
91
91
...

But what I want, is to group them by that expression I used in the select statement itself 'LEFT(CallerTelNumber, 2) AS Series' to get the following:

78
79
91
...

so i tried using this query...

SELECT LEFT(CallerTelNumber, 2) AS Series
FROM Call
GROUP BY Series
ORDER BY CallerTelNumber

I thought it would work but it didn't, so can someone direct me please as to how I can group a resultset by expression, or if there is a workaround, I'd appreicate the tip...

Thx a lot

V.P.

[Hack]

Would a "SELECT DISTINCT LEFT(etc") do the trick for you?

[Bonker Gudd]

Try...

SELECT DISTINCT LEFT(CallerTelNumber, 2) AS Series
FROM Call
ORDER BY CallerTelNumber

[visual poet]

I already treid that and it gave me an error, but after I read the error carefully, I found out why it didn't work....

it should actually be:

SELECT DISTINCT LEFT(CallerTelNumber, 2) AS Series
FROM Call
ORDER BY Series


Thank you so much for your help

[TheVoid]

Friend of mine was looking over the shoulder and this was his suggestion

SELECT LEFT(CallerTelNumber, 2) AS Series
FROM Call
GROUP BY LEFT(CallerTelNumber, 2)
ORDER BY CallerTelNumber

EDIT: didn't work so he suggested this.