SQL SELECT names LIKE $ name but not exact name

Question

I use this SQL query to go through a table and search for a client name and return a column of this row and date:

SELECT custName, date, id FROM booking WHERE custName LIKE '%$s' OR custName LIKE '$s%' 

($ s is a PHP variable)

If I'm looking for John Dorian, I can enter $ s as the first name John or the last name Dorian, and my function will find it. My problem is that John Dorian can appear on multiple lines, and if in this case I would like the query to return only the very last line (using the date column to figure it out).

IE, if my table looks like this and $ s = John:

 (custName, date, id) John Dorian - 2013/01/01 - 1 John Doe - 2013/01/02 - 2 John Dorian - 2013/01/10 - 3 

I want my request to be returned

 John Doe - 2013/01/02 - 2 John Dorian - 2013/01/10 - 3 

Answer 1

What about:

 SELECT custName, date, id FROM booking b INNER JOIN ( SELECT max(date) MaxDate, custName FROM booking WHERE custName LIKE '%$s%' GROUP BY custName ) bm ON b.custName = bm.custName AND b.date = bm.maxDate WHERE custName LIKE '%$s%' ORDER BY b.date DESC 

Answer 2

1) Using DISTINCT or GROUP BY you can easily find and get only one row for each client:

 SELECT DISTINCT custName FROM booking WHERE custName LIKE '%$s' OR custName LIKE '$s%'; 

or

 SELECT custName FROM booking WHERE custName LIKE '%$s' OR custName LIKE '$s%' GROUP BY custName; 

2) You can get the maximum date by linking the aggregation function (i.e. MAX ) with GROUP BY

 SELECT custName, MAX(date) as date FROM booking WHERE custName LIKE '%$s' OR custName LIKE '$s%' GROUP BY custName; 

3) Finally, you can get the full row of the table by adding the results to the original table:

 SELECT b.custName, b.date, b.id FROM booking AS b INNER JOIN (SELECT custName, MAX(date) AS maxDate FROM booking WHERE custName LIKE '%$s' OR custName LIKE '$s%' GROUP BY custName ) AS gb ON b.custName = gb.custName AND b.date = gb.maxDate; 

or (maybe slower):

 SELECT b.custName, b.date, b.id FROM booking AS b INNER JOIN (SELECT custName, MAX(date) AS maxDate FROM booking GROUP BY custName ) AS gb ON b.custName = gb.custName AND b.date = gb.maxDate WHERE b.custName LIKE '%$s' OR b.custName LIKE '$s%'; 

---

ps

The following may seem promising and can sometimes give the right results, but performance is not guaranteed.

 SELECT * FROM ( SELECT custName, date, id FROM booking WHERE b.custName LIKE '%$s' OR b.custName LIKE '$s%' ORDER BY date DESC ) AS t GROUP BY custNAME; 

Unfortunately, you cannot rely on GROUP BY to support your order.

EDIT See also

• MySQL order by group to
• How can I select rows with MAX (column value), DISTINCT by another column in SQL?
• http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/debunking-group-by-myths.html

Answer 3

I think you need a separate custName column. Although I have not tried this myself, but I think the BadWolf answer will return both lines that have the name "John Dorian":

 SELECT distinct(custName), date, id FROM booking WHERE custname LIKE '%$s%' GROUP BY custName, date, id ORDER BY date DESC 

Answer 4

At first glance, I thought it was a simple question, but I'm wrong.

I can only work with instructions.

 select * from (SELECT custName, date, id FROM booking WHERE custName LIKE '%$s' OR custName LIKE '$s%' order by date desc) as t group by (t.custName) order by date ; 

he may have other better solutions.