Re: subselect query
Yeah, that nasty thing with temporary tables and such? Or am I looking at the wrong page? If you could point me in the right direction, I'd appreciate it (slow modem connection, etc). Christopher Thompson wrote: > Read the manual. MySQL doesn't support subselects but offers you > alternatives. > > On Monday 15 April 2002 2:45 pm, Jeff Shipman wrote: > >>I'm trying to do a fairly simple select of a >>column from a table where the date of that column >>happens to be the highest value. I tried this: >> >>SELECT name FROM products WHERE prod_type='foo' AND >>added=(SELECT MAX(added) FROM products WHERE >>prod_type='foo'); >> >>But I get the error: >> >>ERROR 1064: You have an error in your SQL syntax >>near 'SELECT MAX(added) FROM products WHERE >>prod_type='foo')' at line 1 >> >>Is there a problem with the parentheses? I've >>used this syntax with Oracle before with no >>troubles. >> >>name and prod_type are TINYTEXT and added is >>a DATE. >> >>Is there another way I should be doing this? I >>could probably order descending by the added date >>and then just LIMIT 1, but I don't think that's >>very clean. >> >>'mysql --version' returns: >> >>mysql Ver 11.15 Distrib 3.23.41, for -freebsd4.4 (i386) >> >>Thanks in advance, >> > > -- Jeff "Shippy" Shipman E-Mail: [EMAIL PROTECTED] Computer Science MajorICQ: 1786493 New Mexico Institute of Mining and Technology Homepage: http://www.nmt.edu/~shippy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
subselect query
I'm trying to do a fairly simple select of a column from a table where the date of that column happens to be the highest value. I tried this: SELECT name FROM products WHERE prod_type='foo' AND added=(SELECT MAX(added) FROM products WHERE prod_type='foo'); But I get the error: ERROR 1064: You have an error in your SQL syntax near 'SELECT MAX(added) FROM products WHERE prod_type='foo')' at line 1 Is there a problem with the parentheses? I've used this syntax with Oracle before with no troubles. name and prod_type are TINYTEXT and added is a DATE. Is there another way I should be doing this? I could probably order descending by the added date and then just LIMIT 1, but I don't think that's very clean. 'mysql --version' returns: mysql Ver 11.15 Distrib 3.23.41, for -freebsd4.4 (i386) Thanks in advance, -- Jeff "Shippy" Shipman E-Mail: [EMAIL PROTECTED] Computer Science MajorICQ: 1786493 New Mexico Institute of Mining and Technology Homepage: http://www.nmt.edu/~shippy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Complex query
I've got a mysql table and I need to perform a complex query on it, but I'm not sure how to formulate it. This is what I need to do: select id,tcc_cn,entered,updated,enteredby,num_bump, status FROM book_hold WHERE 1) There are no books with the tcc_cn that already have a status of 'NOTIFIED'. 2) Its updated date is the MIN(updated) for that tcc_cn. So, for example, if I have the following items: tcc_cn updated status - 990227 03-08-02 WAITING 990227 03-10-02 WAITING 990228 03-08-02 WAITING 990228 03-15-02 NOTIFIED I would get information back for this item: 990227 03-08-02 WAITING Is there a good way to do this in one or two queries? Any tips would be most appreciated. Thanks in advance! Jeff Shipman E-Mail: [EMAIL PROTECTED] Systems Programmer Phone: (505) 835-5748 NMIMT Computer Center http://www.nmt.edu/~jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
selecting what is not in something
I have two tables and that have a couple of identical columns. I would like to find out what of a certain column is in one table and not in another. I've tried the following, but I get errors: select col1 from tab1 where col1 not in (select col1 from tab2); select tab1.col1 from tab1,tab2 where tab1.col1 not in tab2; Please tell me how I can do this type of query. Jeff Shipman E-Mail: [EMAIL PROTECTED] Systems Programmer Phone: (505) 835-5748 NMIMT Computer Center http://www.nmt.edu/~jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
copy from one db's table to another
I have two databases that have identical table structures. I would like to copy all data from the 'tickets' and 'notes' in one db to the 'tickets' and 'notes' field in the other. How could I do this? Thanks, Jeff Shipman E-Mail: [EMAIL PROTECTED] Systems Programmer Phone: (505) 835-5748 NMIMT Computer Center http://www.nmt.edu/~jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problems compiling on solaris 2.7
I'm having problems compiling mysql 3.23.37 on Solaris 2.7. It gets to the final linking stage and I get the following error: /usr/local/lib/gcc-lib/sparc-sun-solaris2.7/2.95.1/libgcc.a: could not read symbols: Bad value Anyone have this happen to them before and/or have a fix? I've also compiled it on Linux with no problems. I followed the recommended configure line in the README. Thanks in advance Jeff Shipman E-Mail: [EMAIL PROTECTED] Systems Programmer Phone: (505) 835-5748 NMIMT Computer Center http://www.nmt.edu/~jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: complicated query
This does not work for me. It returns syntax
errors.
Jeff Shipman E-Mail: [EMAIL PROTECTED]
Systems Programmer Phone: (505) 835-5748
NMIMT Computer Center http://www.nmt.edu/~jeff
On Fri, 20 Apr 2001, Braxton Robbason wrote:
} you want a function that prepends the letter a to the category/subcategory
} names that are not other. then you order by that function, but do not
} display it.
}
} select category,subcategory from foo2
} order by if(category=
} 'other','zz',concat('a',category));
} i.e. everything except other begins with an a as far as the order by is
} concerned, while other is zz for sorting purposes.
}
}
} -Original Message-
} From: Jeff Shipman - SysProg [mailto:[EMAIL PROTECTED]]
} Sent: Friday, April 20, 2001 2:39 PM
} To: [EMAIL PROTECTED]
} Subject: complicated query
}
}
} I would like to do something similar to an ORDER BY
} in one of my select statements, but I'mt not sure
} how to do something as complicated as this:
}
} I have two columns, category and subcategory, that
} I am retrieving. I would like category and subcategory
} to be sorted alphabetically. This is easy with an
} 'ORDER BY 1 2', but I would like categories that
} are named 'other' to be put off until the end. So,
} I'd get something like this:
}
} abcd
} ghikj
} z
} other
}
} Is there a way to do this type of query? Thanks in
} advance.
}
} Jeff Shipman E-Mail: [EMAIL PROTECTED]
} Systems Programmer Phone: (505) 835-5748
} NMIMT Computer Center http://www.nmt.edu/~jeff
}
}
}
} -
} Before posting, please check:
}http://www.mysql.com/manual.php (the manual)
}http://lists.mysql.com/ (the list archive)
}
} To request this thread, e-mail <[EMAIL PROTECTED]>
} To unsubscribe, e-mail
} <[EMAIL PROTECTED]>
} Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
}
}
}
} -
} Before posting, please check:
}http://www.mysql.com/manual.php (the manual)
}http://lists.mysql.com/ (the list archive)
}
} To request this thread, e-mail <[EMAIL PROTECTED]>
} To unsubscribe, e-mail <[EMAIL PROTECTED]>
} Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
}
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: complicated query
} } You don't need to create another column, just use: } } ORDER BY category = 'other', category } Are you sure this works? My query is: select category,subcategory from categories ORDER BY category = 'other', category; And I get: ERROR 1064: You have an error in your SQL syntax near '= 'other', category' at line 1 Jeff Shipman E-Mail: [EMAIL PROTECTED] Systems Programmer Phone: (505) 835-5748 NMIMT Computer Center http://www.nmt.edu/~jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
complicated query
I would like to do something similar to an ORDER BY in one of my select statements, but I'mt not sure how to do something as complicated as this: I have two columns, category and subcategory, that I am retrieving. I would like category and subcategory to be sorted alphabetically. This is easy with an 'ORDER BY 1 2', but I would like categories that are named 'other' to be put off until the end. So, I'd get something like this: abcd ghikj z other Is there a way to do this type of query? Thanks in advance. Jeff Shipman E-Mail: [EMAIL PROTECTED] Systems Programmer Phone: (505) 835-5748 NMIMT Computer Center http://www.nmt.edu/~jeff - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
