Solved

How to select the rows which has the maximum characters in the column

Posted on 2009-05-18
7
191 Views
Last Modified: 2012-05-07
Sample Data:
ID      name
2      adadas
2      adadasdad
2      adadasdasdasd
3      sdada
3      sadasdasd
3      ggggggggggggggg
4      xxxx
4      fgfgfgfg
4      fdfd
4      ghhjhjhjhjhjj
5      fhgfhf
6      opopoppp
6      opo

I wanted to select the ID and the name which has the maximum characters, can someone please help me find.
0
Comment
Question by:asadeen
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24415901
select top1 ID from urtable order by len(name) desc
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24415905
select top (1) * from SomeTable order by len([Name]) desc
0
 

Author Comment

by:asadeen
ID: 24415958
this will return one record from the table which contains the maximum, but I wanted the result to be
2      adadasdasdasd
3      ggggggggggggggg
4      ghhjhjhjhjhjj
5      fhgfhf
6      opopoppp
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:asadeen
ID: 24416040
I found the answer
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24416058
U should have given the sample output , :)
0
 

Accepted Solution

by:
asadeen earned 0 total points
ID: 24416067
SELECT ID, Name from mytable as mt1 WHERE LEN(Name) = (SELECT MAX(LEN(Name)) from mytable as mt2 WHERE mt2.ID = mt1.ID)
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24416079
I would do it this way instead.

SELECT ID, Name from mytable as mt1
WHERE [Name] = (SELECT top (1) [Name] from mytable as mt2 WHERE mt2.ID = mt1.ID order by len([name]))
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now