CODEWITHSUNDEEP
mysqlsql
user5543922
user5543922

Reputation: 23

MySQL - List details of names which precede a a given name alphabetically (first name and last name separate)

In a database called students where first_name and last_name are separate columns the following problem was asked:

Question: List the details of all students whose name precedes Harry Callaghan alphabetically (under the convention that names are ordered by last name and then by first name).

My attempt below would miss out on any person with, for example, first_name = Mark and last_name = Brennan.

> SELECT *   
> FROM students   
> WHERE last_name <= 'Callaghan' AND first_name < 'Harry';

How can I filter this query so that for last names preceding Callaghan any first name is possible?

Upvotes: 2

Views: 289

Answers (2)

Blue
Blue

Reputation: 22911

Another way to do this (Albeit worse then @maraca's because it wont be able to utilize indexes, is this):

WHERE CONCAT(last_name, ' ', first_name) < 'Callaghan Harry'

Upvotes: 1

maraca
maraca

Reputation: 8743

WHERE last_name < 'Callaghan' OR (last_name = 'Callaghan' AND first_name < 'Harry')

The first name only matters if the last name matches exactly.

Upvotes: 4

Related Questions