MYSQL :: Query To Show Unique Entries

Count Unique Entries
I have a table that keeps track of visits. It has three fields: id, visitor_id and a timestamp.I want to be able to count the number of visits per month, which I can do with the following query.

[MYSQL] "SELECT date_format(timestamp, "%m") as `Month`, count(*) from visits group by `Month`;" [/MYSQL]

I would also like to be able to count the number of unique visitors for each month. That would be counting the number of unique visitor_ids for a month.

MySQL Unique Entries Question
Just a quick one

In my database I have a number of entries that are assigned to different sections (ie 3 entries to section1, 5 entries to section 2, 4 entries to section 3)

What I want to do is run a mysql query that will select 1 entry from each of the 'sections' and return the result

ie so i end up with 1 random entry with a section ID of section 1, 1 random entry with a section ID of section 2 etc etc

Making A Table With Unique Entries
I am trying to make a table with unique entries. Problem is, the entries
are upto 1000 characters long. Can I still apply a unique index and how and
if so, what field type should I choose?

Help In Assigning Unique Keys To Table Entries
Allow me to share some design problem that I am facing.
I have the following table definition:

CREATE TABLE `image_info` (
`Image_ID` int unsigned not null,
`Title` varchar(255) default '' not null,
PRIMARY KEY (`Image_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
This simple table holds information about images. Each image has a title.
Note that the primary key was NOT defined as auto_increment (explanation below)
Also note that the table engine was defined as MyISAM (a system requirement)

The range of image IDs that can added into the table is split into 2:
1) IDs 1,000,000 and above are reserved for images that come from an external program
which defines the IDs.
2) IDs 1 - 999,999 are reserved for images uploaded by users in runtime in my site.

This means:
When I add an image from my application (1), I already know in advance what Image ID I need
to use and I can safely add it using:

insert into image_info (Image_ID, Title) values (1000010, 'I am a photo from an external program');
I know that image ID 1000010 is reserved just for that photo and so there won't be any duplicates.

When a user uploads a photo to my site (2), that photo needs to be stored in the table
with the next available ID which is < 1,000,000.
I use the following query:

select max(Image_ID) from image_info where Image_ID < 1000000;
I then add 1 to the value I am getting and this is the ID I will use for the image uploaded by the user.

The problem occurs when too many users are uploading photos at the same time.
This is a problem of atomicity. 2 Users may be assigned the same "free" image IDs and then
one of the queries will fail when adding the record to the table because of duplicate IDs.

I cannot change the order of the ID ranges (making 1 - 999,999 for my external program and 1,000,000 and above
for images uploaded by users), as this is a system requirement.
If I didn't have this requirement, I could have set auto_increment on the image_id field.

I am looking for a solution to my problem.
Can anyone think of one?
Maybe using an additional table which uses auto_increment to assign IDs?
I am developing in PHP, so maybe I need an application-level solution on top of MySQL

Need A Query To Find All Spam Entries In My DB
I need to tweak the following query to help weed out spammers who have created accounts. I want to try and:

1. Show accounts that have alpha characters in the zip field
2. Show accounts that use more than 5 numbers in the zip field

select zip, country from users where country = 'United States'

Query Brings First Letters Of Entries Only
I've got a problem I don't really understand:

I have a form with a drop down meny (<select>) that gets it's alternatives from a database. When I press submit (and something in the form is missing so your still at the page of the form, only difference the empty fields are highlighted) the options in the drop down meny dissapears. I've done a print-out of the mysql-query results and found suprisingly that it contains of (notice that the combination of letters equals the first letters of the correct print-out):

MyOLEDB Show Tables Query
Is there a way to retrieve the names of the tables in a database if you are using the myOLEDB provider in a C# application? Unfortunately Show Tables doesn't seem to work.

Mysql Logs Show Query With T_tablename
select col1 from table1;

shows

select t_table1.col1 from table1;

in the logs.

Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57
# User@Host: counter[counter] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647
SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7;

mysql> select count(*) from _1_log;
+----------+
| count(*) |
+----------+
| 111824 |
+----------+
1 row in set (0.00 sec)

Change To Simple Query Causing It To Run Show, Help?
I m looking for some help if possible. I ve got a very simple query which just outputs all the records from the table (tbl_addresses). It has 8587 records.

SELECT a.ID, a.address, a.datecreated, a.datemodified
FROM tbl_addresses as a
WHERE a.statusid = 1
LIMIT #arguments.pagesize# OFFSET #arguments.startrow#

I ve been asked to update the query to get the name field from another table called tbl_organisations. This table has 8883 records. There is a link table to relate the two (lnk_addresses). This table has 8243 records.

I ve updated my query to the following:

SELECT o.name, a.ID, a.address, a.datecreated, a.datemodified
FROM tbl_addresses as a
LEFT JOIN lnk_addresses as la ON a.id = la.addressId
LEFT JOIN tbl_organisations as o ON la.orgId = o.id
WHERE a.statusid = 1
LIMIT #arguments.pagesize# OFFSET #arguments.startrow#

The query now runs at snails pace and takes minutes to display anything. Even If I remove the 2nd join (LEFT JOIN tbl_organisations as o ON la.orgId = o.id) its slow so I guess its related to the tbl_addresses and lnk_addresses.

How To Show All Records In MYSQL Query Using Default Value
I'm working on somesite and i face this problem:

i want to send a parameter entered by a user from text box that called (txt1) to the query below ....

Can Error Message Show Entire Sql Query Instead Of Excerpt
I'm running MySQL 3.23.58 on Red Hat Linux 9.

In my web server error log I see things like:

[Thu Jan 18 00:33:56 2007] [error] [client 72.86.29.196] DBD::mysql::st
execute failed: You have an error in your SQL syntax near ' (NOW(),
"Setup [precheck] failed for 72.86.29.196 port 1239 [72.86.29.196]
(Con' at line 1 at /var/www/html/blah/mysql-help.pl line 34.

Is there a MySQL setting I can change somewhere so that it will log the
entire SQL query that generated the error, instead of just an excerpt?
It's a lot easier to figure out what went wrong if I can see the whole
query.

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

MyOLEDB Doesn't Support Commands Like SHOW FIELDS, SHOW TABLES
My code is shown below, I can select * from employees but I cannot get any data back from SHOW FIELDS, I do not receive an error, in fact, something is returned but I'm not sure what. I can execute the command:

SHOW FIELDS FROM test.employees

in the Control Center just fine, even cmd.ExecuteReader will enter reader.Read() once, something is returned but what does it look like? ......

What's Wrong With My Query To Filter Double Entries And Skip Empty Rows?
I am trying to get filter a database table.
- skip empty rows (i.e. ecardNameSender is empty)
- filter double entries

$sql = "SELECT COUNT(*) as total FROM tblEcards WHERE ecardNameSender != '' GROUP BY ecardEmailFriend";
$result = @mysql_query($sql, $connDB);
$row = mysql_fetch_assoc($result);
$totalPics = $row['total'];
echo $totalPics;
What's wrong with my query?

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.

How To Check That Entries Are Linking To Dead Entries?
I've got two tables that have an one-to-one relationship (I divided it up into two for specific purposes). table2 has a field 'field' that refers to table1's 'field'. The 'field' on table1 is a primary key. table2's 'field' is unique to ensure a 1:1 relationship.

If an entry gets removed from table1, the entry in table2 that belongs to the removed entry should be found easily so I can remove it too. But... I'm not sure how to go about it.

The thing I thought of was:

SELECT table1.field FROM table1 JOIN table2 USING (field) WHERE table1.field NOT LIKE table2.field;

But that didn't give me anything at all. But I think what I wanted to achieve should be clear in this one. Does anyone have some ideas how to go about it?

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

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.

One Table With Many Entries Or Many Tables With A Few Entries Each
I have a friend who is working on a web forum. He would like to design his database so that everytime a user starts a new thread, a new table for that specific thread is created. He will have another table that stores the names of the thread tables.

I suggested the idea of creating two tables. A table for threads, and a table for posts. All the posts go in the post table and have a threadID associated with it that references the thread table.

He thinks his idea of creating a table for each post is faster because instead of going through an entire table (extremely large table) to find the posts for a thread, his method will just take all the entries from the thread table for that thread. I disagree, I think his method is slower because using many tables will slow things down, and I'm assuming MySQL uses a hash system which will help find the entries almost instantaneously.

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

Delete Except Latest 2500 Entries (was "help With Query")
I have a table that holds information on activities.
For each activity I store the following:
ID, When, Description
The ID is the key for the table
The When column holds the datetime when the activity was added

The table gets filled up pretty quickly.
I would like to run a query that will delete ALL the entries except for the 2500 latest ones. The When column should be the one to use when deciding which are the 2500 latest activities

Can someone show me the most efficient way of doing this?

Latest 3 Entries From My "friends" Query
I have a hypothetical query i'm struggling with:

table1: users
table2: user_posts
table3: friends_association_table

1) i have a list of id's that are my "friends" that i get by querying the friends_association_table

2) i need to get the latest 3 entries from each of these friends for a total of 15 or fewer (if i only have 1 friend with 1 entry for instance) entries

ignore the fiends aspect for a moment and consider only a query that asks

"what are the latest 3 post entries for each of these specific users (my friends) up to a total of 15 entries"

Ps -fax Show 1 Thread, Others Show 5
Where do I configure "spare servers"???

Show/not Show Option
I have a php page that displays pictures. The pictures comes from a mysql database. The way I am creating the recordset to dislpay is "Select * from tblehouse" . Now what I want to do is instead of selecting every picture from the database maybe create a new field that will take in the option of being checked or unchecked. Then have the query select only the "checked" to display. So my question to you guys is how would create a field that takes in a check box and what would be the sql statement to query just the checked records.

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.

Display X Out Of Y Entries
I have a MySQL DB with ~1100 entries, 13 Fields per entry.

I have a search feature on the page, but when someone searches, they get all of the matches at one shot. What I would like to do is display, say 5 matches, at a time. Then have a next and previous at the bottom of the page along with page numbers.

Get All Entries In A Table
I would like an area of my site to display all the entries of a table. It is meant as a "thought of the day archieve" where my admins and mods are able to upload their "deep, intelligent thoughts"

I'd like the entries to displayed in a list. Could someone supply me with a code that would do this?

Double Entries
I am running the latest MySql on a windows 200 machine. I also use the
control center gui to do all my work with. I have a Perl program that
parses online order data and then using Perl's DBI I write this data to the
database. Now, It all works fine except that each order and orders items
are written twice to the database. I have looked over my code but only see
one insert statement for order information and the ordered items. I guess I
need a fresh set of eyes to see where my error is. Code:

My.Ini - InnoDB Entries
I have been using MySQL in a Windows environment for about 6 months and have successfully written a couple of applications. Reading all and anything about MySQL I have been looking into the My.Ini entries, particularly those affecting InnoDB tables. Code:

Exchanging Two Entries
I have a simple table with an integer auto increment primary key (id, say) and I want to be able to move an entry higher up in the table by exchanging one record for the one just before it. Now i can't guarantee that the 'one just before' a given id number is (id - 1) as there may have been deleted records.

Max Number Of Entries
I am having a problem with a query that I have been trying to solve for the past 2 hours and I can't seem to get it.

I have a table called Listing with a field MemberID and I am trying to print out the MemberID that occurs the most times in the table.

The following query returns the count of each MemberID in the table, all I need to do is get the maximum value out of there:

SELECT Listing.MemberID, COUNT(Listing.MemberID)
FROM Listing
GROUP BY Listing.MemberID;

I am trying to do something along the lines of MAX(COUNT(Listing.MemberID)) but that does not seem to work.