I have some data in my database. I tried to do a retrieval and it should appear in such a way.
FName| lName | phoneNo | year
Tom | Tan | 9123456 | 1
Tom | Tan | 9012345 | 1 <----extra row
FName | lName| phoneNo| year | phoneNo2
Tom | Tan | 9123456| 1 |9012345
GROUP BY, use
MIN() to get first phoneno and
MAX() to get the second.
select FName, lName, min(phoneNo), max(year), max(phoneNo) from tablename group by FName, lName
You can also do a self
select t1.FName, t1.lName, t1.phoneNo, t1.year, t2.phoneNo from tablename t1 left join tablename t2 on t1.FName = t2.FName and t1.lName = t2.lName and t1.phoneNo < t2.phoneNo
Note: According to ANSI SQL
YEAR is a reserved word, so you may need to delimit it as