General information
Category:
Coding, syntax & commands
Title:
Find person with longest name
Hi: Using foxpro, I have a table as follows:
ID Name
------ --------------------
1234 Fred
5678 Robin
2345 Bertha
2345 Bertha Smith
5678 Robin Jackson
I am trying to figure out an SQL query that will identify the people with the longest names, and as a side answer: the number of rows that contain
the same ID. Thank you, Steve
This gets me into the ballpark and I suspect that foxpro can do this:
SELECT r1.id, r1.name, LEN(ALLTRIM(r1.name)) as l, COUNT(r1.id) ;
FROM aan as r1 LEFT JOIN aan as r2 on r1.id = r2.id .and.;
LEN(ALLTRIM(r2.name)) < LEN(ALLTRIM(r1.name)) .or.;
LEN(ALLTRIM(r1.name)) > LEN(ALLTRIM(r2.name));
GROUP BY r1.name, r1.id
But I can't figure out what count(ID) is doing, how to return the number of rows per ID, or how to limit my results
to the rows that have the longest names?
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only