How can I merge these mysql statements

Question

I have 2 working MYSQL statements, that I would like to merge into 1 query:

from mysql select statement with unique and maximum selects? I have:

SELECT field_a, max(dup_number) as dup FROM table1 GROUP BY field_a

my second query is:

where contacts=0 ORDER BY date LIMIT 3

so a group of records would look schematically like:

ID     FIELD A     DUP_NUMBER  CONTACTS 
1       text1        0           1        --
2       text2        0           3        --
3       text2        1           3         --
4       text2        2           3        --
5       text3        0           2        --

I've come up with:

SELECT *
FROM `table1 `
where max(`DUP_NUMBER `) as dup  and `CONTACTS`=1
GROUP BY `FIELD_A`
ORDER BY date
LIMIT 3

When I test in PHPMyAdmin I get

#1064 - You have an error in your SQL syntax.

Can anyone show me how to rewrite this correctly?

can you explain more what are you trying to do? – John Woo Apr 10 '13 at 14:08 — John Woo, Apr 10 '13 at 14:08
you said two working queries . is the second working ? – echo_Me Apr 10 '13 at 14:31 — echo_Me, Apr 10 '13 at 14:31

score 1 · Accepted Answer · answered Apr 10 '13 at 14:10

1

Try this.

SELECT *, max(DUP_NUMBER) as dup FROM table1 where  CONTACTS=1 GROUP BY FIELD_A ORDER BY date LIMIT 3

answered Apr 10 '13 at 14:10

Slowcoder

2,060
3
16
21

score -2 · Answer 2 · answered Apr 10 '13 at 14:11

-2

Problem's here:

where max(`DUP_NUMBER `) as dup  and `CONTACTS`=1
                         ^^^^^^

you can't alias things in a where clause, only in the field list, e.g.:

SELECT max(DUP_NUMBER) as dup
HAVING dup=???

is how you'd use it.

answered Apr 10 '13 at 14:11

Marc B

356,200
43
426
500

How can I merge these mysql statements

2 Answers2

Linked