select unique record question

[Jassim Rahma]

Hi, I am using the following code in sql server to get list of mobile numbers and add the country code to it.

select ('973' + contact_moblle) AS mobile_number from persons where contact_moblle is not null and contact_moblle != '' and len(contact_moblle) = 8 and left(contact_moblle, 1) = '3' order by file_no

I want to do the same but with selecting unque records only.. something like this (but throwing error!)

select DISTINCT ('973' + contact_moblle) AS mobile_number from persons where contact_moblle is not null and contact_moblle != '' and len(contact_moblle) = 8 and left(contact_moblle, 1) = '3' order by file_no

[PIEBALDconsult]

How about a subselect if your database system allows it? SELECT ('973' + contact_moblle) AS mobile_number FROM (SELECT DISTINCT contact_moblle FROM persons WHERE ... ) T Also... "where contact_moblle is not null and contact_moblle != '' and len(contact_moblle) = 8" can be reduced to: "where len(contact_moblle) = 8"

[Lost User]

jrahma wrote:

something like this (but throwing error!)

You didn't post an error. I doubt that it'll update the correct field if the dataset is created using distinct values.

Bastard Programmer from Hell :suss:

[davidshenba]

select ('973' + contact_moblle) AS mobile_number from persons where contact_moblle is not null and contact_moblle != '' and len(contact_moblle) = 8 and left(contact_moblle, 1) = '3' GROUP BY contact_moblle order by file_no

Hope this will help. Check this and let me know the outcome

-Shenbaga Murugan Paramasivapandian I hate computers and I just mess them up with my code!

[Niladri_Biswas]

Try this

;With CTE AS
(
Select
Rn = Row_Number() Over(Partition By ('973' + contact_moblle) Order By file_no)
,('973' + contact_moblle) AS mobile_number
from persons
where contact_moblle is not null
and contact_moblle != ''
and len(contact_moblle) = 8
and left(contact_moblle, 1) = '3'
)

Select mobile_number
From CTE
Where Rn = 1

Hope this helps

Niladri Biswas