CODEWITHSUNDEEP
mysqlsqlmax
DLO
DLO

Reputation: 309

SQL to select email and max id

I have a table which records email addresses for newsletter purposes. When someone subscribes it sets a subscribe ind to 'Y' and there is an incremental id. When someone subscribes/unsubscribes, I need to select all table entries for the email address, but then select based on the highest id.

Once I have this info I can use the subscribe ind to see if they are subscribed or not. I have been trying to figure this one out but without success. I am trying something like:

mysql_query

SELECT * FROM newsletter WHERE email=$email and 
id=(select max(id) from newsletter)

Should I be doing something else?

Upvotes: 0

Views: 927

Answers (3)

David Miró
David Miró

Reputation: 2758

Try this:

SELECT MAX(id) as id, email
FROM newsletter 
GROUP BY email
HAVING email=$email

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

SELECT col1,col2,max(id)
FROM newsletter 
WHERE email=$email
group by col1,col2

EDIT

select *
from newsletter 
WHERE email=$email
and id in (select max(id) from newsletter group by email)

Upvotes: 2

Matti John
Matti John

Reputation: 20477

You need to group by email:

SELECT * FROM newsletter WHERE email=$email and 
id=(select max(id) from newsletter group by email)

Upvotes: 0

Related Questions