question

aj2017 avatar image
aj2017 asked

Select all employees which first names start with the same letter

Select all employees which first names start with the same letter?
query-results
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
I would advise you research the following areas [SELECT][1] [FROM][2] [WHERE][3] *and* [LIKE][4] Take a go your self and let me know if you have any issues. Good luck! [1]: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql [2]: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql [3]: https://docs.microsoft.com/en-us/sql/t-sql/queries/where-transact-sql [4]: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aj2017 avatar image
aj2017 answered
Anything like that? select a.name, b.name from emp as a, emp as b where SUBSTRING(a.name,1,1) = SUBSTRING(b.name,1,1) and a.id < b.id
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

aj2017 avatar image
aj2017 answered
No at all, as this will fetch all names starting with "B". I have to pull all the Names STARTING with the SAME letter (first letter must be the same - it can be ANY letter, not a specific one)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
A few different ways to approach this but the following comes right to mind (the substring function is going to force scans). It finds any first initials that are used more than once (`having count(*)>1`), then pulls all the names that begin with any of those initials. You could easily pull the subquery into a CTE and join against it, use row_number(), etc... select name from Employee where substring(name, 1, 1) in ( select substring(name, 1, 1) as firstInitial from Employee group by substring(name, 1, 1) having count(*) > 1 )
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@KenJ. Brilliant!! Thank you so much.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.