.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Query to find data with maximum length

Posted By: Naresh     Posted Date: May 12, 2010    Points:2   Category :Sql Server
Hi,

I have the foll. table.


Id name Address
1 aaa No 27,I cross
1 aaa No 27,I cross,GG Nagar,Chennai:45
2 sss No 3,Ng St,Chidabaram,ph-34534254
2 sss No 3,Ng St,Chidabaram
3 mmm No 222, Sj St,Madurai




I need to get the IDs, and only one address column with maximum length.

The OutPut Should be,

Id name Address
1 aaa No 27,I cross,GG Nagar,Chennai:45
2 sss No 3,Ng St,Chidabaram,ph-34534254
3 mmm No 222, Sj St,Madurai



I tried the Query ,

SELECT id,name,address from
Employee where id='1' and len(address)=(select max(len(addredd)) from employee
where id='1')
group by id,name,address

Iam Getting the output for one,

Id name Address
1 aaa No 27,I cross,GG Nagar,Chennai:45


My Question is how to get it for all Ids,



Thanks
Naresh

Responses
Author: Usha             
Posted Date: May 12, 2010     Points: 5   

Hi Naresh,

Why you are providing where Id='1', As u want data for all ID's

Plz, Chk out below query, And let me know whether it works for u or not

SELECT distinct id,name,address from
Employee where address=(select max(len(address)) from employee)
group by id,name,address


Thanks & Regards,
usha

Author: Suresh          Accepted Answer   
Posted Date: May 12, 2010     Points: 10   

Hello Naresh,
Kindly try the below query, it will give solution for your issue.

Declare @t table
(
id int,
name varchar(25),
address varchar(100)
)

Insert into @t Select 1 ,'aaa' ,'No 27,I cross'
union all
Select 1 ,'aaa' ,'No 27,I cross,GG Nagar,Chennai:45'
union all
Select 2 ,'sss','No 3,Ng St,Chidabaram,ph-34534254'
union all
Select 2,'sss','No 3,Ng St,Chidabaram'
union all
Select 3,'mmm','No 222, Sj St,Madurai'

Select max(len(t1.address))addressLength,t1.Id,t1.name,t1.address from @t t1
Join @t t2
on t1.id=t2.id
group by t1.Id,t1.name,t1.address
having max(len(t1.address))>=max(len(t2.address))

Let Me know, do you need any further clarification


Author: Naresh             
Posted Date: May 12, 2010     Points: 5   

Hi Usha,

I tried the query but iam getting the error

Error converting data type nvarchar to bigint.



Thanks Usha Can u provide any other solution


Naresh

Thanks
Naresh
Author: Naresh             
Posted Date: May 12, 2010     Points: 5   

Hi Suresh,

I tried ur query.
It is working correctly.

You Provided the query which i needed accurately.
Thank u a lot



Thanks
Naresh

Thanks
Naresh


Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend