Welcome to WebmasterWorld Guest from 54.242.193.41

Forum Moderators: open

Message Too Old, No Replies

Help with a MySQL query

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

Junior Member

10+ Year Member

joined:Aug 10, 2004
posts:97
votes: 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 .....

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

New User

10+ Year Member

joined:Sept 7, 2006
posts:7
votes: 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.

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

Junior Member

10+ Year Member

joined:Sept 19, 2005
posts:44
votes: 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.
4:12 am on Dec 23, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:Oct 4, 2006
posts:44
votes: 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;

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

Junior Member

10+ Year Member

joined:Aug 10, 2004
posts:97
votes: 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

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

New User

10+ Year Member

joined:Sept 7, 2006
posts:7
votes: 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.