Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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

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
asadeen
Asked:
asadeen
  • 3
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
select top1 ID from urtable order by len(name) desc
0
 
BrandonGalderisiCommented:
select top (1) * from SomeTable order by len([Name]) desc
0
 
asadeenAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
asadeenAuthor Commented:
I found the answer
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
U should have given the sample output , :)
0
 
asadeenAuthor Commented:
SELECT ID, Name from mytable as mt1 WHERE LEN(Name) = (SELECT MAX(LEN(Name)) from mytable as mt2 WHERE mt2.ID = mt1.ID)
0
 
BrandonGalderisiCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now