Help with a MySQL query

Forum Moderators: open

Message Too Old, No Replies

Help with a MySQL query

someone

11:36 pm on Dec 20, 2006 (gmt 0)

My table contains data like this:

table name is ans_table

responseID Ś questionID Ś answer

6 Ś 4 Ś abc
6 Ś 5 Ś cde
6 Ś 6 Ś tj@hotmail.com
8 Ś 7 Ś dkfjsdkf
8 Ś 8 Ś kjkd
8 Ś 9 Ś abc@hotmail.com
9 Ś 7 Ś dkd
9 Ś 8 Ś dkjds
9 Ś 9 Ś kdk@hotmail.com
10 Ś 7 Ś dkslsd

I need to select the lastest row with the email address, which in this case is the 2nd row from the bottom (kdk@hotmail.com). I don't know the email address, responseID, and questionID ahead of time, so the query cannot refer to a specific value in the table.

I only got:
select answer from ans_table where answer like "%@%" and .....

erb5

4:49 pm on Dec 22, 2006 (gmt 0)

I'm still learning MySQL so take my reply with a grain of salt, but this worked for me

SELECT answer FROM ans_table WHERE responseID = (SELECT MAX(responseID) FROM ans_table WHERE answer LIKE "%@%")

I'm assuming responseID's are assigned chronologically. I did do my tests on a table I have (just working out a similar situation), so I didn't test this verbatim; if it doesn't work, let me know and I'll be glad to have another go (if you haven't already figured it out).

There's also got to be a way using HAVING, but I wasn't able to figure it out, so while this might not be the most efficient, it's perhaps a start! Good luck.

crevier

4:52 pm on Dec 22, 2006 (gmt 0)


SELECT answer FROM ans_table WHERE answer LIKE "%@%.%" ORDER BY responseID DESC LIMIT 1;

Just doing a descending sort on the responseID column, then getting only the first row.

Discovery

4:12 am on Dec 23, 2006 (gmt 0)

table name is ans_table

responseID Ś questionID Ś answer

6 Ś 4 Ś abc
6 Ś 5 Ś cde
6 Ś 6 Ś tj@hotmail.com
8 Ś 7 Ś dkfjsdkf
8 Ś 8 Ś kjkd
1 Ś 8 Ś 9 Ś abc@hotmail.com
9 Ś 7 Ś dkd
9 Ś 8 Ś dkjds
9 Ś 9 Ś kdk@hotmail.com
10 Ś 7 Ś dkslsd

from given result i don't think that respondID is as primary or unique key so what u can do just u add a auto increment value that will solve ur problem like :

sno Ś responseID Ś questionID Ś answer

1 Ś 6 Ś 4 Ś abc
2 Ś 6 Ś 5 Ś cde
3 Ś 6 Ś 6 Ś tj@hotmail.com
4 Ś 8 Ś 7 Ś dkfjsdkf
5 Ś 8 Ś 8 Ś kjkd
6 Ś 8 Ś 9 Ś abc@hotmail.com
7 Ś 9 Ś 7 Ś dkd
8 Ś 9 Ś 8 Ś dkjds
9 Ś 9 Ś 9 Ś kdk@hotmail.com
10 Ś 10 Ś 7 Ś dkslsd

and u can use ,

SELECT answer FROM ans_table WHERE answer LIKE "%@%.%" ORDER BY sno DESC LIMIT 1;

someone

5:55 pm on Dec 28, 2006 (gmt 0)

erb5, your query returns 2 results if the table is modified a bit. If it has one more row, the last one.

6 Ś 4 Ś abc
6 Ś 5 Ś cde
6 Ś 6 Ś tj@hotmail.com
8 Ś 7 Ś dkfjsdkf
8 Ś 8 Ś kjkd
8 Ś 9 Ś abc@hotmail.com
9 Ś 7 Ś dkd
9 Ś 8 Ś dkjds
9 Ś 9 Ś kdk@hotmail.com
10 Ś 7 Ś dkslsd
10 Ś 8 Ś kjsdf@hotmail.com

Then your query would return both

dkslsd
kjsdf@hotmail.com

erb5

8:48 pm on Jan 5, 2007 (gmt 0)

Oops; still climbing my way up that learning curve! The others' solutions are much more graceful, anyhow. Thanks for noting a problem with mine to me; trying things out has been a great way to learn.