SQL SELECT names LIKE $name but not exact same name twice

Question

I'm using this SQL query to go through a table and search for a customer name and return that row's id and date column:

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

($s being a PHP variable)

If I'm looking for John Dorian, I could input $s as the first name John, or family name Dorian and my function will find him. My problem is that John Dorian may appear in more than one row, and if that's the case I would like the query to return only the most recent row (using the date column to figure this 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 would like my query to return

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

You should select a correct answer. – jmilloy Sep 26 '13 at 13:31 — jmilloy, Sep 26 '13 at 13:31

score 2 · Accepted Answer · answered Sep 13 '13 at 01:51

2

How 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

answered Sep 13 '13 at 01:51

Bad Wolf

8,206
4
34
44

need to aggregate the non-group columns – Thilo Sep 13 '13 at 01:52

score 1 · Answer 2 · edited May 23 '17 at 11:52

1) You can search and get only one row per customer easily using DISTINCT or GROUP BY:

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 max date by coupling an aggregrate function (ie MAX) with the 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 table row by joining the results back 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 (probably 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%';

p.s.

The following may seem promising, and may even give the correct results sometimes, but is not guaranteed to work.

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 can't rely on the GROUP BY to maintain the supplied order.

EDIT See also

score 0 · Answer 3 · answered Sep 13 '13 at 02:01

0

I think you need a distinct on the custName column. Though I haven't tried this out myself, but I think BadWolf's answer would return both the rows 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

answered Sep 13 '13 at 02:01

sfali16

114
1
4

This doesn't make a lot of sense. If you `GROUP BY` all three values (custName, date, and id), then you might as well not GROUP at all. – jmilloy Sep 13 '13 at 04:30
I like this because it's much simpler than the other solutions. I only GROUPed BY custName though (grouping by all three wasn't returning any results). Also at the moment, I'm only getting the oldest row, not the most recent. I tried playing with the order by date asc/desc but to no avail. Will try more later. – Juicy Sep 13 '13 at 13:15
It may be simpler, but it is not actually going to work. `GROUP BY` chooses the row values *arbitrarily* when no aggregate function is supplied. In this query, the `GROUP BY` happens before the `ORDER BY`, so the `date` and `id` values are already (arbitrarily) chosen before the rows are ordered. However, even if you order the results *first* in an inner `SELECT`, and then group the results in at outer `SELECT', it's not guaranteed to work. See my solution, and [this article](http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/debunking-group-by-myths.html). – jmilloy Sep 13 '13 at 13:49

score 0 · Answer 4 · answered Sep 13 '13 at 02:17

At first sight, I thought it was a easy question, but i'm wrong.

I can only work out by follow statement.

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 ;

it may have other better solutions.

SQL SELECT names LIKE $name but not exact same name twice

4 Answers4