retrieving last record for all distinct users
I have a table SESSIONS with the following fields: SESSION_ID LOGIN IP TIMESTAMP I am trying to select the last login record for all distinct users. The closest I can get to is: select distinct LOGIN, TIMESTAMP, IP from SESSIONS group by LOGIN order by TIMESTAMP desc This kind of works but it does not get the correct IP for the last TIMESTAMP. This seems easy enough, but I can't seem to figure this one out today. Can anybody see what I'm missing? Thanks! -m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: order by question
I think I figured it out. I had the gallery_id field zero filled... It works now, thank you very much! -m -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 3:49 PM To: mysql Cc: motorpsychkill Subject: Re: order by question * motorpsychkill Ahhh...I see now. I'm still in MySQL 3.23.54. Thanks anyways! It should work also in version 3.23.54... actually, it should work since version 3.20.17: URL: http://www.mysql.com/doc/en/News-3.20.17.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: order by question
-Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 10:54 AM To: mysql Cc: motorpsychkill Subject: Re: order by question * motorpsychkill SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY ? What I'm trying to do is get the results in the order specified in the IN clause, i.e. (3, 2, 1). Is this possible? (I'm having trouble searching the mail archives). Thanks! Use the FIELD() function: SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY FIELD(gallery_id, 3, 1, 2) HTH, -- Roger Thanks Roger, but that didn't seem to work. It still gave me the results ASC, i.e. by gallery_id 1, 2, 3. Anyone else? :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: order by question
Ahhh...I see now. I'm still in MySQL 3.23.54. Thanks anyways! -Original Message- From: Cabanillas Dulanto, Ulises [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 2:52 PM To: mysql Subject: RE: order by question I execute the SELECT and it works!. I have MySQL 4.0.4 Regards, Ulises -Mensaje original- De: motorpsychkill [mailto:[EMAIL PROTECTED] Enviado el: Jueves 7 de Agosto de 2003 04:43 PM Para: mysql Asunto: RE: order by question -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Thursday, August 07, 2003 10:54 AM To: mysql Cc: motorpsychkill Subject: Re: order by question * motorpsychkill SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY ? What I'm trying to do is get the results in the order specified in the IN clause, i.e. (3, 2, 1). Is this possible? (I'm having trouble searching the mail archives). Thanks! Use the FIELD() function: SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY FIELD(gallery_id, 3, 1, 2) HTH, -- Roger Thanks Roger, but that didn't seem to work. It still gave me the results ASC, i.e. by gallery_id 1, 2, 3. Anyone else? :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
order by question
SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY ? What I'm trying to do is get the results in the order specified in the IN clause, i.e. (3, 2, 1). Is this possible? (I'm having trouble searching the mail archives). Thanks! -m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Difficult count query?
I have two tables: bc_post and bc_message. This is what I have so far: select bc_post.*, count(bc_message.message_to) responses from bc_post left join bc_message on bc_post.post_id = bc_message.message_topostid where bc_post.post_status = '1' and bc_post.user_id = '1' group by bc_post.post_id This gets correct posts but not correct responses. I want to limit the responses (the count) to only those where bc_message.message_to = bc_post.user_id. If I add that statement, I get the correct responses but I lose any posts that do not have any messages (which I do not want to do). How do I retrieve the count (responses) and return 0 if there aren't any? CREATE TABLE bc_post ( post_id int(10) unsigned NOT NULL auto_increment, post_date date NOT NULL default '-00-00', post_details text NOT NULL, post_message text NOT NULL, post_gender1 varchar(7) NOT NULL default '', post_gender2 varchar(7) NOT NULL default '', user_id varchar(25) NOT NULL default '', post_ip varchar(20) NOT NULL default '', post_timestamp timestamp(14) NOT NULL, post_status int(1) NOT NULL default '0', postgeo_id char(3) NOT NULL default '', PRIMARY KEY (post_id) ) TYPE=MyISAM; CREATE TABLE bc_message ( message_id int(10) unsigned NOT NULL auto_increment, message_topostid int(10) NOT NULL default '0', message_from varchar(25) NOT NULL default '', message_to varchar(25) NOT NULL default '', message_message text NOT NULL, message_ip varchar(20) NOT NULL default '', message_timestamp timestamp(14) NOT NULL, message_status varchar(4) NOT NULL default '0', PRIMARY KEY (message_id) ) TYPE=MyISAM; Thank you for any help!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Difficult count query?--nevermind, I found the answer...
select bc_post.*, count(bc_message.message_to) responses from bc_post left join bc_message on bc_post.post_id = bc_message.message_topostid and bc_post.user_id = bc_message.message_to where bc_post.post_status = '1' and bc_post.user_id = '1' group by bc_post.post_id -Original Message- From: motorpsychkill [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 23, 2003 11:20 AM To: mysql Subject: Difficult count query? I have two tables: bc_post and bc_message. This is what I have so far: select bc_post.*, count(bc_message.message_to) responses from bc_post left join bc_message on bc_post.post_id = bc_message.message_topostid where bc_post.post_status = '1' and bc_post.user_id = '1' group by bc_post.post_id This gets correct posts but not correct responses. I want to limit the responses (the count) to only those where bc_message.message_to = bc_post.user_id. If I add that statement, I get the correct responses but I lose any posts that do not have any messages (which I do not want to do). How do I retrieve the count (responses) and return 0 if there aren't any? CREATE TABLE bc_post ( post_id int(10) unsigned NOT NULL auto_increment, post_date date NOT NULL default '-00-00', post_details text NOT NULL, post_message text NOT NULL, post_gender1 varchar(7) NOT NULL default '', post_gender2 varchar(7) NOT NULL default '', user_id varchar(25) NOT NULL default '', post_ip varchar(20) NOT NULL default '', post_timestamp timestamp(14) NOT NULL, post_status int(1) NOT NULL default '0', postgeo_id char(3) NOT NULL default '', PRIMARY KEY (post_id) ) TYPE=MyISAM; CREATE TABLE bc_message ( message_id int(10) unsigned NOT NULL auto_increment, message_topostid int(10) NOT NULL default '0', message_from varchar(25) NOT NULL default '', message_to varchar(25) NOT NULL default '', message_message text NOT NULL, message_ip varchar(20) NOT NULL default '', message_timestamp timestamp(14) NOT NULL, message_status varchar(4) NOT NULL default '0', PRIMARY KEY (message_id) ) TYPE=MyISAM; Thank you for any help!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get equivalents via SQL?
I have a table like this (table_gender): gender_id gender_description --- 0 female 1 male I also have a table like this (table_post): post_id post_gender1post_gender2 --- --- 21 0 1 22 0 0 23 1 0 Is there any way that I can pull a singe row from table_post (for e.g. row 21) and have the results look like: post_id gender1 gender2 --- 21 female male Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which is better: big SQL statement or bigger db?
I need to have a user input a city and have MySQL pull up any records with that city OR nearby cities (within 10 mi). Which of the following would be the most efficient way to do this: Case A: When a user enters a city, an array of nearby cities is created so that an SQL statement like the following is generated: SELECT * FROM bc_posts WHERE post_citysoundex = 'A265' OR post_citysoundex = 'A415' OR post_citysoundex = 'A453' OR post_citysoundex = 'A430' OR post_citysoundex = 'A624' OR post_citysoundex = 'A350' OR . . . This statement would probably be much larger (upto 150 lines) and would query one table without additional joins. Case B: Here, when a user enters a city, the soundex of it is created and then queries a table that contains every city in the db PLUS all surrounding cities (calculated and inserted with each new city insert). Obviously, here the table would get large while my actual SQL statement is pretty straightforward but would require a join. I'm not sure which of these is the more elegant approach or would scale up much easier. Any input from the DB gurus would be appreciated! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
