I can't figure out what I thought would be a simple query..
All; I am having tremendous trouble attempting to do the following query; and any help would be appreciated. I am using Mysql 4.0.15a; and I cannot upgrade. Given the following TEMPORARY table (it's a table I have created from a whole host of sources): table: endtime_table +-+-+---+ | endtime | need_id | non_unique_id | +-+-+---+ | 2003-08-17 00:46:59 | 18724 | 6646 | | 2003-08-17 00:46:59 | 18724 | 6647 | | 2003-08-17 00:46:59 | 18724 | 6648 | | 2003-08-17 00:46:59 | 18724 | 6649 | | 2003-08-17 00:46:59 | 18724 | 6650 | | 2003-08-17 00:46:59 | 18724 | 6651 | | 2003-08-17 00:46:59 | 18724 | 6652 | | 2003-08-17 00:46:59 | 18724 | 6653 | | 2003-08-18 00:20:10 | 19143 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-18 00:20:10 | 19143 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | | 2003-08-23 00:11:10 | 14443 | 6650 | I would like, for each UNIQUE non_unique_id; to get the latest endtime for that unique ID. for instance; the result set I am looking for above would be: | 2003-08-22 00:02:10 | 17512 | 6646 | | 2003-08-18 00:20:10 | 19143 | 6647 | | 2003-08-18 00:20:10 | 19143 | 6648 | | 2003-08-18 00:20:10 | 19143 | 6649 | | 2003-08-23 00:11:10 | 14443 | 6650 | | 2003-08-18 00:20:10 | 19143 | 6651 | | 2003-08-18 00:20:10 | 19143 | 6652 | | 2003-08-18 00:20:10 | 19143 | 6653 | as you can see, there are 3 records for 6646 non_unique_id column; but the latest one is the date 2003-08-22 00:02:10 which has the need_id of 17512. and so forth. For the life of me, i can't figure out how to do this. i've tried various max(), group_by's, and such, but nothing has worked so far. either it can't be done (doubtful) or my brain can't figure it out (probable). short of doing something rediculous like invividual selects for each unique non_unique_id; is there a way i am missing? I hope? Thanks, --jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I get the query results I want?
lets say i have a query the returns me the following data: +++-+-+-+-+ | status | id | case_id | analysis1 | analysis2 | analysis3 | +++-+-+-+-+ | FAILED | 1 | 453 | 0 | 0 | 0 | | FAILED | 6 | 464 | 3 | 2 | 5 | | FAILED | 7 | 468 | 0 | 2 | 5 | | FAILED | 8 | 469 | 0 | 2 | 5 | | FAILED | 13 | 453 | 0 | 0 | 0 | | FAILED | 14 | 464 | 0 | 0 | 0 | | FAILED | 15 | 468 | 0 | 0 | 0 | | FAILED | 16 | 469 | 0 | 0 | 0 | | FAILED | 21 | 636 | 6 | 0 | 0 | +++-+-+-+-+ this is multiple join amongst several tables to get the above data. now, what i really want to be able to do, is to get the 'id' in which there are more then 1 'FAILED' status for a particular case_id, ie, for the above, i'd get id's 1,6,7,8,13,14,15,16 ... but not 21 because it only has one FAILED status for case_id 636. my problem is i am a bit unsure how (or if) i can do this via a query in mysql. i can't group by status (that i know of) because then i lose the individaul 'id' columns ... and i can't do a 'having' clause because i have no counts. any help? --jim When I read about the evils of drinking, I gave up reading. - 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
unsure of performance vs. replication of data...
I have a general DB question, but since I'm using MySQL, I figure one of our friendly listreaders might be able to help me out. I have 4 tables, say table1-table4, and I need to get some data from table4 which relies on some values from table1. However, the only way I can get those table1 values, is by going through tables 2 and 3. So... the query, in essence, looks like: select table4.id from table1,table2,table3,table4 where table4.table3_id = table3.id and table3.table2_id = table2.id and table2.table1_id = table1.id and table1.value = VALUEIMLOOKINGFOR. now -- the reasons i have 4 tables such as this is each table holds a key peice of data. *BUT*, there's nothing from preventing me from adding a column to table4 called table1_id and doing something like select table4.id from table1,table4 where table4.table1_id = table1.id and table1.value = VALUEIMLOOKINGFOR my questions falls on the replication of data issue (and understand, a DB Guru I am not, but I've read Paul DeBois book and think i have a pretty fair handle of things). Anyway, since I can infer the data without having to replicate anything (ie: the first query) is it good DB practice that, if you are making this type of query, to go ahead of be able to reference the data directly (ie: the second query), even though table4 would, in essence, be storing the table1_id when it could be gotten via a query anyway? Am I making myself clear? I am trying to determine when it's ok to have extra columns the duplicate data you can get elsewhere but would make a query potentially faster (i believe) but the tables larger? If anyone can point me elsewhere as well... --jim -- And then there was one... - 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: Different logs for different databases?
I was wondering, is it possible to have different log files for different databases somehow? RedBack? You're just down the street... Sort of. Ya, but for how long (for either of us). ahem. difficult to do (add the necessary code to MySQL), provided that the logging code is as straightforward as the binary logging code. i figured something like that, though i don't know the internals. it seems it knows what's being connected to, so to make seperate logfiles can't be too hard. i keep running into this issue, but oh well. thanks for the input. - 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 the newest/oldest record...
Question: given data in a table such as id, value, date 0, value1, 1999-01-25 1, value2, 1999-01-02 2, value2, 1999-01-04 3, value1, 1999-01-22 4, value2, 2000-01-01 how can i select the latest 'id' for a given same set of 'value' based on 'date'? ie: i would like to be able to get records id's 0 and 4 from above, since id 0 has the latest date for value1 (1999-01-25) and id 4 has the least date for value2 (2000-01-01). i was thinking of using a least function on it, but my problem is I don't know how exactly it can be done without some (outside) processing. Am I missing something? thanks, --jim -- And then there was one... - 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
Question about a (better) query.
I have 3 tables that I am joining together based on that fact that one (or more) values appear in the 2nd table, but those values that need to appear are garnered from the 3rd table (and i want the id that is associated by the 1st table). Err well, an example would be better: Table 1: id - int whatiwant - int Table 2: id - int table1_id - int table3_id - int Table 3: id - int value - varchar The relationship is for every record in table 1, you can have 0 to (as many different records their are in table 3) in table 2, and Table 3 is a seperate table that just holds a bunch of values. My query would be then, as such: ## SELECT ##COUNT(table1.id) AS total, ##table1.whatiwant ## FROM ##table1,table2,table3 ## WHERE ##table2.table1_id = table1.id ## AND ##(table3.id = table2.table3_id ## AND ##(table3.value = 'some' ## OR table3.value = 'needed' ## OR table3.value = 'values' ##) ##) ## GROUP BY ##table1.id ## HAVING ##total = 3; which basiclly is a way to get a correspondance between table3 and table1 via table2. What this will give me are all the record in table1 which have all the values i want from table3 associated via table2 (there can be more then 3, or less then 3). And the value I want is that record in table1 that satifies all those requirements. The above query does work. BUT!!! am I doing something laughably wrong? This query requires that for each OR statement in table3, all values are looked at in table2, then grouped, and then the only ones returned are those that group to the same number of OR's we are looking for. I have got to think this is inefficient. Anyone have suggestions? Comments? Anything? Thanks, --jim When I read about the evils of drinking, I gave up reading. - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
