human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the maximum length of friendships with Alice?
assistant
SELECT MAX( year ) FROM personfriend WHERE friend = 'Alice';
human
Who holds a relationship of that length?
assistant
SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = 'Alice' AND T2.year = ( SELECT MAX( YEAR ) FROM PersonFriend WHERE friend = 'Alice' );
human
Also show age and job title.
assistant
SELECT T1.name , T1.age , T1.job FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = "Alice" AND T2.year = ( SELECT MAX( YEAR ) FROM PersonFriend WHERE friend = "Alice" ) ;