How To Write Query To Select The Max(version) For Each Unique File_name Record?
I am a MySQL newbie trying to write a query that selects file_name records
possessing the highest numbered version for that unique file_name. I show
sample data and two trial queries below. Logically I want to use
max(version) as a constraint in a Where Clause. However, the max() function
is not allowed directly in a where clause.
I have contemplated a second table to track the max version for each file
name. I would like to structure the data in an efficient manner for query
performance when the data set grows to many thousands of unique file_name
records with many hundreds of versions each........
View Complete Forum Thread with Replies
Related Forum Messages:
Unique Table Version Record
Unique table version record Hello, I am using a table record to store informations about database dump,I use this table : CREATE TABLE dump_version( count BIGINT unsigned NOT NULL DEFAULT 0, timestamp TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT 0 ); And I added a record (INSERT INTO dump_version () VALUES();) that I modify each time I dump the database. The only problem is that I can't ensure that this table will contain only a single record, is that a way to ensure a king of 'UNIQUE' record or something?
View Replies !
Want To Select Unique Record Only?
i have a field and this field has many records. but some record have duplicates. now how can i select the uniqe record name only in the field? for example, user_name ----------- john john joey joe gina karen warren karen george warren i want to select them all but not including the duplicate so if i want to select the the name only not including the dupliate then the result is user_name ----------- john joey joe gina karen warren george
View Replies !
Select Query, Unique Ids With Matching Values
trying to figure out how to select the pid's from the following table which have matching wid's. ie. the table below would return a result set of 8, 22 and 68 because they are shared by wid 1 and 2. PHP Code:  wid        |    pid --------------- 1        3 1        8 1        22 1        56 1        68 1        92 2        2 2        8 2        15 2        22 2        68 2        77Â
View Replies !
Query To Select Unique Products From 2 Tables
What i am trying to achieve is to compare invoices against orders and to show the results. Both tables Invoices and Orders have/may contain duplicated products and where there are duplications in either table, I want to merge it into 1 line and combine the quantities and costs on products that are duplicated in each table.. which I have got working. But the problem i now have is that... both Invoices and Orders may contain products that may not be in a either on of the tables.... e.g... Invoices may have product 1234a but is not in Orders and vice versa.... What i need is to show these products from both tables. From the query's i've done i can only get it to show these in the Invoices table.. but i can't get it to show them in the Orders table aswell.... but if i swop Invoices and Orders around in my query.. then i get the results for Orders and not Invoices for the products that aren't in the Invoices. Here are my query's....
View Replies !
UPDATE Step Thru Every Record W/out Unique Id Field
How can I update every record of a given table one by one. I need to update a field (date) with a different, random date for each record. loop { generate random date; update one record with new date; goto new record } Problem. Table does not have a unique id field table (item, date) peaches - 2003-05-13 pears - 2003-11-24 peaches - 2004-01-04 the table is a given... meaning I have to work with it the way it is.
View Replies !
How To Write This Select?
I'm having some troubles writing a select. I have the following tables: theater ------- - id - name movie ----- - id - name showtime --------- - id - theater - movie - from - to - schedule I would like to list all the theaters with all the available showtimes and movies. I tried to, but i didn't get the result i was expecting. Can somebody help?
View Replies !
Select .. Into Outfile Write
global scope: trying to execute on a schedule a .sql file which writes out a .csv file. windows(dont ask)/mysql in the batch file i have mysql -u myUser --password=somePass < myFile.sql the sql query in the file Code: use mydb; select field1,field2,field3 union select `actField1`,`actField2`,`actField3` into outfile 'F:folder est.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM `detailsTable`;
View Replies !
Best Way To Write This Query
Query 1: obtains results in boolean mode from products table based on keywords Query 2: Would like to grab all manufacturers names from manufacturers table based on mf_id in products table to then create a brand filter. What is the best way to do this in the most efficient query possible. I was thinking of building a php array from query 1 of mf id's and the putting them in query 2 and separating them using OR operators. any ideas? here is the query fyi: PHP $sql = "SELECT products_id as prodid, products_name as name, manufacturers_id as manufacturers_id, products_price as price, products_msrp as msrp, products_date_added as pda, products_status as status, products_sku as sku, products_weight as weight, categories_id as cat_id, " .boolean_sql_select( boolean_inclusive_atoms($search_string), $fulltext_key)." as relevance " ."FROM $table_name " ."WHERE " .boolean_sql_where($search_string,$fulltext_key)." " ."HAVING relevance>0 "; if (isset($HTTP_GET_VARS['sort'])) { if ($_GET['sort']=="brand") { $sql.="ORDER BY products_name "; } elseif ($_GET['sort']=="size") { $sql.="ORDER BY products_weight "; } elseif ($_GET['sort']=="price_low") { $sql.="ORDER BY products_price ASC "; } elseif ($_GET['sort']=="price_high") { $sql.="ORDER BY products_price DESC "; } elseif ($_GET['sort']=="fav") { $sql.="ORDER BY products_name "; } } else { $sql.="ORDER BY relevance DESC "; }
View Replies !
How To Write A SELECT/COUNT + Other Fields
I have a table like so: NAME | AGE Joey | 21 Mary | 18 Lass | 43 Moch | 33 Joey | 23 Mary | 25 Mary | 65 Lass | 90 I what to write a single query that retrieves the total number of times a single name is found in the the table (Joey appears two times, Mary appears 3 times, and Lass appears two times) AND also retrieves each record. SELECT COUNT(name) as name_total, name, age FROM persons_table WHERE 1 RESULT: row#: name_total, name, age: row1: 2, Joey, 21 row2: 3, Mary, 18 row1: 2, Lass, 43 row1: 1, Moch, 33 row1: 2, Joey, 23 row1: 3, Mary, 25 row1: 3, Mary, 65 row1: 2, Lass, 90
View Replies !
How To Write Group By Query
i create table item-info. i want to view my data in the following way. i write query use group by but i want name show only one time no repeatation name next line. item-info table: id name item price 1 A XX 15 2 B XX 16 3 A YY 17 4 C YY 20 5 B ZZ 50 6 A ZZ 40 View my data in the following way: name item price A XX 15 YY 17 ZZ 40 B XX 16 ZZ 50 C ZZ 20
View Replies !
To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 12004-01-10110 22004-01-20120 32004-01-20270 42004-01-10280 52004-01-15110 62004-01-25310 Output : RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 22004-01-20120 32004-01-20270 62004-01-25310 Now I want to use a single SQL query to find a result where there exist one record for each unique "othertableid" where the record selected for the "othertableid" should be the recent one with regard to "EffectiveDate". That is from the above records, I want to select Records with "RecordId" = 2 and 3 because they are the recent one for "othertableid" = 1 and 2 respectively. Please be sure that I want to retrive all fields including "RecordId". The result should not depend on any other fields but "EffectiveDate" only. I am using MySQL 4.0.12 and it does not support "SUBQUERIES" which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any "SUBQUERY" SQL statement can be written with proper "JOINS".
View Replies !
How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!". I have 2 tables, same DB. Table 1 is named Policies Table 2 is named Assignments Both contain a PolicyID field. I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments. So if "Select PolicyID from Assignments" returned the following: 1, 2, 3, 4, 5, 6 And "Select PolicyID from Policies" returned: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 What do I use to return this? 7, 8, 9, 10 Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!
View Replies !
Trying To Write A Two-part Fast Query
The situation is this: there are two tables, one called "objects" (fields: object_id[int] and object_name) and properties (fields: object_id[int] and property_name and score[int]). Objects have one or more properties. In short, what these 2 queries need to ask are "what objects could an unknown object be, given what properties I know?" and "what property should I measure next to most narrow down the possible objects?" The objective of the first query is to return an object who has all of the given properties scored above or below the given value. For example, I might look for the object_id of an object who has the property "green" scored above 3, the property "hot" scored below 2, and the property "bright" scored above 0. It might have other properties, too, but it must have these 3. And then, for the second part, I need a query that will select properties which are NOT in this set of given properties but are relevant to the returned set of objects. In this part, I'm not concerned with the property's score - just that it is relevant to the objects. In other words, this query might tell me that the property "hard" is relevant to 3/4 objects (even though one of them had a very low score for the hard property, another had a very high one, etc). Basically I want to know which new property, once I can determine the score of this new property, will help narrow down the object-set the most, so that I can collect data for the most relevant property to narrow down the object set.
View Replies !
ERROR 1022 (23000): Can't Write; Duplicate Key In Table On SELECT
I'm using the mysql server that comes with ubuntu-server 8.04 (mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2) and I have a rather big database (about 5M records). If I try to do this mysql> select count(*),lastname from data group by lastname; all i get is the following error: ERROR 1022 (23000): Can't write; duplicate key in table '' The lastname column is the only one returning this error. (The statement works for firstname, city, street, etc...) Does anybody have an idea as to what's happening here? I've tried searching but could only find this error in relation to database updates and insertions (which would be logical considering the 'duplicate key' part).
View Replies !
Write Tricky Mysql Query (related Tables)
member id(int, pk) name(varchar) profile_pic(varchar) other_fields books id(int, pk) cat_id(int) name comment id(int, pk) comment_type(enum: books, more_type) comment_id(books id) comment Sample data of comment id comment_type comment_id comment ------------------------------------- 1 member 1 comment about first member 2 books 1 this book is really nice 3 books 2 horror books are instesting 4 member 5 paype is the sailor is now a member now i need a SQL Query to "select all books information having cat_id=1 along with user inputed comments about those books".
View Replies !
Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables. What is the best approach one of these or another? After generating a candidate 'unique string' the two strategies that came to mind are: 1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or 2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again. I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.
View Replies !
Select Unique
I have a table of names and addresses (this list can not be split up into two tables), I would like to return one row per household. For example if there are 4 people living in the same home how can I return only one row? I read that I can have multiple columns in distinct but mysql didnt like my query. Can someone offer possible syntax: Distinct(last_name, street_number, street_name, city)
View Replies !
Select Unique Names
I have two tables, sales_2004 and sales_2005, in each table there is a column named item_sold. I am trying to run a query between both tables that will just pull the unique item_sold from both tables.
View Replies !
Select Unique Records
I am using php, have a database table with 200 records, each record has an ip field, How can i select only records with unique ip values? rather than all the records?
View Replies !
Select Unique Entries
A simplified version of my table is like this: ID Time Thread -------------- 1__4:15__44 2__4:30__44 3__5:10__32 4__6:45__12 5__7:33__44 6__9:14__32 What if I want to select only one of each unique thread? And the ones that get selected must be the latest time? So here's my result: ID Time Thread -------------- 4__6:45__12 5__7:33__44 6__9:14__32 .
View Replies !
Version 4 Query Slowing Killing Me
I want to create a query that will select all the rows in table A that dont appear in table B based on a column of ints that appears in both tables. In other words table A has a column of memberIDs and table B has a column of memberIDs. I want to select all the memberIDs from table A that dont appear in table B.
View Replies !
Select From Table Using 2 Columns As Unique
How do I select from a single table using 2 columns (surname,postcode)as the unique criteria? e.g. "Surname","Forename","Salutation","Postcode" "Bear","Daddy","Mr","CT10 7AF" "Bear","Mummy","Mrs","CT10 7AF" "Bear","Baby","Miss","CT10 7AF" "Piggy","Daddy","Mr","CT10 7AF" "Piggy","Mummy","Mrs","CT10 7AF" "Piggy","Baby","Miss","CT10 7AF" "Piggy","Cousin","Ms","CT10 7AF" would return just the following 2 rows:- "Bear","Daddy","Mr","CT10 7AF" "Piggy","Daddy","Mr","CT10 7AF" I don't mind whether it's the first unique row or the last unque row. All I am interested in removing the duplicates.
View Replies !
Unique Results :: Select Repeated Value Once
I have a column which has a variety of strings, some of which are repeated several times in the column. I'm trying to use a SELECT statement which will only select unique values from the column (only select repeated values once), according to further conditions in my WHERE clause. So, just for example, say I have names, and there's 20 Joe's and 15 Frank's, or whatever; I only want one Joe and one Frank to come from the SELECT statement
View Replies !
Select Unique Records From A Join?
I have two tables, both tables contain a matching JobID. Some records in the job table are not present in the joblocation table. I need to Select only those records. I thought I could just do a left join on the two tables with a not equal to where clause? SELECT job.JobLocation1, job.City, job.Province, job.SiteName1, job.Code, job.JobID, joblocation.JobID FROM soileng.joblocation joblocation LEFT OUTER JOIN soileng.job job ON joblocation.JobID = job.JobID WHERE job.JobID != joblocation.JobID limit 100 Any ideas on how to get this to work?
View Replies !
Unique Query
I don't know if this can be done but here goes I have a mysql table with the fields id, user_name, L1 and L2 the first query is Code: select L1 from tbl_name where id=some_value now I want it to check if L1 is NULL if it is not then check if L2 is null if it is not take the value of L1 as some value and redo the query and if both L1 and L2 are not NULL in this query go back to the original L2 and take that value as some_value and do the query and continue like this until a NULL value is found then do an update I am not very good at explaining so if this is not clear please ask
View Replies !
Unique Query Help
I have two tables Table 1 has 3 unique URL (for e.g. hotmail.com) and Table 2 has 4 records of (hotmail.com)...The only way to display the combination for a particular URL is to use group by. Table 1 id url 1 hotmail.com 2 sitepoint.com 3 youtube.com Table 2 id userid (user who added) url notes 1 user1 hotmail.com good site 2. user2 hotmail.com email site 3. user3 hotmail.com great site 4. user4 hotmail.com cool site Now I want to JOIN table 1 and table 2 and display unique records for hotmail.com...Only way i can do is by using group by...I don't want to use that. And it doesn't really matter if it display record by any user, but as long as it is only one record for hotmail.com in Table 2 Can anyone please help me If I use inner join from Table 1 to table 2 ON URL, it still shows 4 records of that url, though i only want to show the first one if repeat record occur.
View Replies !
Unique/distinctrow Query ???
I have two tables each with two fields I want to retrieve: name and number. I only want to retrieve the rows from which a year=2007. This is my query: SELECT table1.name, table1.num, table2.name, table2.num FROM table1, table2 WHERE table1.year=2007 AND table2.year=2007 When I do this directly in MySQL it brings back the results: ANDREA 1 ALVIN 1 ANDREA 1 BARBARA 2 ANDREA 1 THREE-E 3 BARRY 2 ALVIN 1 BARRY 2 BARBARA 2 BARRY 2 THREE-E 3 where table1 is on the left and 2 is on the right. This data is only in the actual table once (in other words, for whatever reason it's returning each row twice. The problem is I'm trying to execute it in PHP and it only returns table 2's data. Is it something in my query that I am missing or do I need to focus on the PHP side? I've been studying the query altering it a little with DISTINCTROW and whatever else I can think of but can't quite get the desired results.
View Replies !
Select Last Record
I have this in my query: SELECT flash_data.flash_data, h_package_data.hpackage_data FROM flash_data, h_package_data What i want to do is add the values in two tables together? There are alos a load of other tables but i have only used two here. I want to be able to select the last entered record of each table, then add them together. I know i could just put a "+" but this will not work with all the rest of the tables and data i need to use.
View Replies !
Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17): "update user inner join events on user.username = events.username set user.LastMessageDate = '$current_time' where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'" but fails on my production server running v3.23.56. Anyone any ideas why it doesn't run and what I need to do to fix it?
View Replies !
Query To Show Unique Entries
In the table word_list is a list of words. This field is not unique so you can get 3 of the same word (they have different attributes in other fields). How can I alter this query below so that I only get unique entries in the word field (so that the query does not return more than one of the same word).
View Replies !
SELECT Where There's No Record In Table
[products] ID name [users] ID fname lname [products_users_lookup] uID pID Ok, I need the following: SELECT all products FROM these 3 tables WHERE there's no record in the lookup table. So I need all products that are not attached to the user account.
View Replies !
Unique Results On Join/Union Query
I have 2 tables: Table 1: groupid, groupname etc, Table 2: groupid, firstname, lastname etc; I have these data in table 1: 1, groupname1 2, groupname2 In Table 2 I have: 1, Fname1, lname1, 1, fname2, lname2 1, fname3, lname3 2, Fname1, lname1, 2, fname2, lname2 For each loop, i'm looking for these results: ROW1 Groupname1 + all matching fname,lname using its groupid ROW2 Groupname2 + all matching fname,lname using its groupid
View Replies !
SELECT JOIN Where A Record Doesn't Exist
I have two tables. A company table and a table with a list of modules that links to the company table. I have had think about this but not come up with any sort of solutions. How to I select all companys where a company module record doesn't exist. i.e. Company1 module1 module2 Company2 module1 module2 Company3 module1 module2 Company4 module1 module3 So how would I select only Company4 where there is no module2. Please note there is many to one relationship between company and module, i.e many module to 1 company. If I use something like SELECT * FROM company, modulelist WHERE modulelist.module!=module2 it doesn't appear to work.
View Replies !
Join/Select Only Most Recent Record
I have two tables. The first is a list of customers and the second is a list of sales invoices. I want to link the 2 tables but only select the newest/most recent invoice. There can be many invoices for a single customer. Customer Table CustomerID integer (primary key); CustomerName char Sales Table SaleID integer (primary key) CustomerID integer (key from customer table) SalesDate Date SalesAmount integer Here is what I have so far: select customertable.customername, salestable.salesdate, salesamount from customertable inner join salestable on customertable.customerid = salestable.customerid How can I return only a single record for each customer (the most recent/newest sale)?
View Replies !
Position Of A Record In A Given SELECT Statement
I want to make an iteration (on www) through some dataset (let's say - address book). One record maight be: - third out of 1200 from city MyCity - 134th out of 102000 from district MyDistrict - etc... Any ideas how to get position in any of those subsets in some elegant way? Till now I was doing SELECT someID FROM address WHERE myCondition(ie. city=MyCity) ORDER BY neededOrder and then iterated one by one through result searching for my ID and therefore knew it was ie 10th out of 120. Everyone will certainly agree that it's not elegant and far from optimized.
View Replies !
Query- Get Every X Record?
My app loads my database every 30 seconds with weather data from several different weather stations (2880 records per station per day). I need to extract every 10th record for a station (288 records per day) to build a PHP array for a graph.
View Replies !
Is SELECT Speed Dependent On Record Size?
My current Mysql database has a table with a Text field and a few blob fields. The average record can range from 500kb to 5mg, the bulk coming from the blob fields. I select from each record based on words in the Text field, but after about 3000 records, the search takes too long. If I setup another table that has just the text field and shares a key with a table containing the blobs, will it be quicker to search the Text field? In other words, does having larger records slow down my search in the Text field?
View Replies !
Select Record Based On 'change' Date
I currently have a database containing a table with the following fields: signid, sign_typ, changedate and time. The 'changedate' field is the date on which the star sign changes and becomes 'active' until the next 'changedate' in the table. I am trying to search this field by a date the user enters and display the 'sign_typ'.
View Replies !
Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing. Code: SELECT Artists.Artist, Albums.Album_Name FROM Artists, Albums WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id
View Replies !
|
TRACKER
