MYSQL :: How To Write Query To Select The Max(version) For Each Unique File

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?

Looking Up Duplicate Record Or Adding New Unique Record
CREATE TABLE tbllayer (
LayerID int(11) NOT NULL default '0',
LayerSize int(11) NOT NULL default '0',
IceTypeID int(11) NOT NULL default '0',
Fingerprint char(16) binary default NULL,
PRIMARY KEY (LayerID),
UNIQUE KEY Fingerprint (Fingerprint),
KEY IceTypeID (IceTypeID)
) TYPE=MyISAM;

We have an internet monitoring application which stores objects in the above
table, with the fingerprint an MD4 of the object. In general about 30% of
the time an object monitored is already in the table, in which case we don't
want to re-insert it, we just want to find out it's ID. The percentage may
vary between 10% and 50% though.

Currently we have a cache in our application which works like this:

- object is monitored and its fingerprint taken
- is the fingerprint in the cache? if so, take its ID from there
- if not, do a SELECT on the table - if a match is found add it to the cache
and use its ID
- if not, INSERT the record into the tablem use its ID and possibly add it
to the cache too

Ok. In general, is it better to:

- do a SELECT to see if the record exists and if not INSERT it
or
- do an INSERT, and if it fails then do a SELECT to locate the record

What about if the duplicate ratio is high or low?

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?

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.

Who Do I Write Select With Dates And Times
i have to cols, date and time. who can i do, SELECT between (date 1,time 1) to (date 2, time 2)?

for example i want to get the recordes between 2007-01-01 23:00:00 to 2007-01-03 03:00:00 ?

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
";
        }

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!

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".

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.

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)

Select Unique
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?

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 .

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.

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.

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.

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?

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.

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.

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

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.

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.

Select Record With Max Value
I'm more used to oracle than mysql, so I thought this was a really simple query:

select * from gallery where gallery_id = (SELECT MAX(gallery_id) from gallery);

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.

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.

Query For Counting Unique Values
I'm trying to find out if I can construct a query to MySQL that will return the number of unique values in a given column. The reason is because I have a column that just contains just the YEAR of a given report. I want to see how many different values populate the YEAR column through the entire query result so I know how many tables to render on screen (one table for each year).

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).

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?

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)?

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.

Buliding A Query:selecting Unique Records
I am nearly positive that there is some way to have mysql select only the first occurance of a data entity on a select.

Maybe a better explanation is given by example. Think of having a table of employees, with one of the columns being their bosses email address. Now I want to do a select statement on the bosses email addresses that only returns one entry for each bosses email, even though that bosses email address will occour > 1 in the table. (more than 1 employee has the same boss...)

I could parse out the duplicates when I get to PHP but I would rather have mysql do the work for me.

Get The First And Last Record From A Query?
I need to return a max, min, open and close in the query but I only have one column which is value. The open would be the first record and the close would be the last record. Is there a way to do this? ...

Query Last Record?
Hello I have a DB with 4 fields. id title event date.

I would like to query the last record only and display it. I am using mysql and php. The trouble I am finding is how to query the last record by id?

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?

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'.

Query A NULL Record
Is there an easy way to test, in a query, whether all of the fields in a record are NULL?

I appreciate that it is unusual to create a table that contains all null records but the circumstance that I have requires it.

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

Query Help: Get Oldest Record W/ Grouping?
I'm building a system that allows a maximum of two records per unit. When a new record is created for that unit, if there are already two records the oldest record is deleted.

Thus, I'm trying to build a query that gets the oldest record for each unit, but also returns the number of records per unit. The trouble I'm having is in making sure the record I'm getting is the oldest one! I thought I could use the Order By clause to get the oldest record, but it doesn't work. In a database table where there are a total of four records:

recordID unitID recordFile recordTime

10 1 someFile1.txt 1179778828
11 3 someFile5.txt 1179778828
12 3 someFile5.txt 1179778990
13 1 someFile5.txt 1179778956
The following query:

SELECT
unitID,
COUNT(recordID) AS numRecords,
recordID AS oldestID,
recordFile,
recordTime
FROM records
GROUP BY unitID
ORDER BY recordTime DESC
Returns the following result:

unitID numRecords oldestID recordFile recordTime

3 2 12 someFile5.txt 1179778990
1 2 13 someFile5.txt 1179778956
These are the newest records, not the oldest. I'm not even sure that is a reliable case. I get the exact same result using the same query but with ASC instead of DESC!

How To Ommit First Record In Query Result?
How can I ommit the first record of a Query?

Query To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script,
my table having the details of uploading time, file name, & uploader name.

i need to find the recent uploaded file for all uploaders.

i tried with this query,

select file_name,uploded_by,MAX(date_time) from upload group by uploded_by;

it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.

Insert A Record Using MySQL Query Browser
I know I can probably type in the actual query, but I was wondering if there was a means of inserting a record without typing "insert into table_name (blah, blah2) values (blah, blah)". I'm looking for a method as easy as typing into an Excel spreadsheet or Access DB. (I know I'm gonna get bashed for mentioning Microsoft products.)

Install New Version Of Mysql With Old Version Running
I would like to install the new versin of mysql but currently i have mysql version 3.23 running...

I need to have 2 version of mysql running at the moment in win2000 for different app.

what is the steps for me to install the new version of mysql without causing error and able to run concurrently.

How To Modify The "select" Function In The Source-code-version?
I have a source-code-version of MySQL at hand,and I want to modify the "select" function to optimize it.

The question is:how can I start? I wonder whether "select.cc" is the right file I can edit.

Rolling Back A DB From Version 5 To Version 4
I've got a local MySQL DB with my MediaWiki data that was created in version 5.0.17, but I recently discovered that my webhost is running version 4.0.27, and when I tried to import the database as-is, I received an error.

I'm looking for suggestions as to how I could roll my database back to the older version of MySQL, in case my webhost decides not to let me upgrade. I've considered using an intermediary, such as converting to MDB or CSV, but so far I haven't had much luck with finding a tool to do so without spending a pile o' cash.

Unique Combinations Of Model And Make (was "Query Question")
I'm trying to write a query and was wondering if someone could help me formulate it. Here is an example table I'm working with:

Table: vehicles
Model Make
Ford Taurus
Ford Taurus
Ford F150
Chevy Tahoe
Chevy Blazer
Chevy Tahoe

I want a query that returns all unique combinations of model and make. So the result set should be:
Model Make
Ford Taurus
Ford F150
Chevy Blazer
Chevy Tahoe

I know how to return distinct values for one variable, but not for two like that. Any help is greatly appreciated.

Count Of *unique* Visits By Page For Each Day (was "Need Help With Query")
Using MySQL 4.1

I have a table 'orders' with the following fields:
ipaddress
pagenum
xtime (which is just the yearmonthday)

this is the query:
SELECT distinct pagenum,ipaddress,xtime,count(pagenum) AS pagenumtotal FROM `orders` group by pagenum,xtime ORDER by xtime, pagenum asc

What I am trying to do is return a count of *unique* visits by page for each day. This query returns a count of *all* visits by page for each day, including repeats. So if a visitors hits a certain page on the site 10 times and adds a record 10 times, the count result will include all 10 records in the count. I want this to only count as 1.

Automatically Record Date Of Record Entry
I have my database table set up and I have an HTML form that is PHP driven that will add the information entered into the form into to my database table.

I have a local buy-sell-trade Website. The way it has worked is that people fill out a form and the results emailed to me. I then take the information and enter it into a Web page. I only want the ads to be displayed for 30 days. I keep the ad for a total of 6 weeks (displays for 30 days and sits in limbo for 2 weeks afterwords) and if not renewed within that 2 week limbo period - I delete it.

Entering all the ads and keeping up with the dates manually has become a burden. I've only recently began looking into databases. My hosting company provides me with phpmyadmin and mysql 5.0. I'm new to all of this but I have managed to set up a database table and a HTML form that is PHP driven that allows ads to be automatically added to the database table.

There is a lot I need to do to make this ideal, but one step at a time.

First, I need to know the date (March 02, 2006) the ad was created or added to the database table. I know that I need to add some piece of code to my php form to record this information, but what code and where do I put it? I know I will need to create an extra field in my database table to house the date - I can handle this.

I've read the date and time information here http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

but there's a lot there and I don't know which is right for my needs. Plus, it doesn't tell me how or where to insert it into my php form (or does it?).

Want Only One Record Returned Per Post_id (was "Help With Query")
I can't figure out how to do this query. Help would be muchly appreciated!

I want to get the DISTINCT(post.post_id) but not sure how to do this.

Here is the query I have thus far but it's returning two records each with the same post_id but different cat_id's.

SELECT post.post_id, auth_alias, pc.cat_id, post_heading, DATE_FORMAT(post_created, '%M %d, %Y'), post_body FROM posting post LEFT OUTER JOIN post_cats pc ON post.post_id = pc.post_id WHERE post_status = Ƈ' ORDER BY post.post_created DESC LIMIT 0,5

How To Write Query To Select The Max(version) For Each Unique File_name Record?