CODEWITHSUNDEEP
phpmysql
Doan G
Doan G

Reputation: 33

select multiple rows from mysql but show one result from each id

i have mysql table like this

id   id_p   name    date
1     1     test1    2015-01-01
2     1     test1    2015-02-02
3     2     test2    2015-03-01
4     2     test2    2014-03-05

celect by date

select name, date from table1 where date <= (now() - interval 6 month)

and result is

name   date
test1  2015-01-01
test1  2015-02-02
test2  2015-02-02
test2  2014-03-05

how to get this result how to limit

name   date
test1  2015-01-01
test2  2015-02-02

thank you for your help

Upvotes: 0

Views: 43

Answers (1)

random_user_name
random_user_name

Reputation: 26160

You should use GROUP BY, which then means that your fields need to be properly set up otherwise mysql doesn't know which value to return. In this case, I'm wrapping your date field in the MIN function since it appears you wanted the lowest / earliest date.

So, in your case, modify the query like so:

SELECT `name`, MIN(`date`) as `date`
    FROM table1 
    WHERE `date` <= (now() - interval 6 month)
    GROUP BY `name`

Upvotes: 1

Related Questions