MYSQL :: Select Distinct Records

Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day.

This is my query:

select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable
group by mydate

It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....

SELECT DISTINCT, (and Display Other Fields Not Distinct.)
I am using SELECT DISTINCT to select 1 of a duplicted field. So far I have;

SELECT DISTINCT `field1`, `field2`, `field3` FROM table1

This returns what I need. There is also another field (field4) which I also want to select, but not distinctly.

Something like: SELECT DISTINCT `field1`, `field2`, `field3` NOT_DISTINCT `field4` FROM `table1`

The field that is not being selected distinctly contains a '1' or a '0'. My table is ordered by field4 (0 first) does this mean the select distinct will select those with '0' before those with '1' (I want '0' to have priority when select distinct)

I will only be using this SQL query once to remove duplicates from a database, I am not concerned about performance issues which someone has mentioned to me.

How can I display this not-wanted-distinct field in a distinct query?

Select Distinct And Include Non-distinct Columns
I have a publication table that tracks the products assigned to various publications.

I want to select all of the distinct products, based on product_ID, assigned to a specific publication but I also want to return additional columns that do not need to be distinct.

If I use the following select:

Select distinct publication.product_ID, publication_ID.code, publication.region from publication where publication_ID = '12'

I would get a list showing the three columns that I want to see which are product_ID, publication_ID and region for publication 12 but I get too many items as the distinct function means that all three of these columns must be distinct and I only need the product_ID to be distinct.

So how do I find all of the distinct product_ID but also show other columns such as region?

Do I need to do this with some kind of self-join?

Distinct Records
Distinct records not working, I still get duplicate's

$query = "SELECT DISTINCT(ITEM), `NAME` , `PRICE` , `IMG`, `id` FROM `iflorist` WHERE `PRICE` >= 0 and `PRICE` <= 29.99 ORDER BY `NAME` DESC LIMIT 0 , 999";

If I take out the "id" field no duplicates will output, But I need "id" included.
"id" is setup currently with : INT, INDEX, and auto_increment ??

DISTINCT Still Showing Duplicate Records
Why won't distinct work? The sql IS selecting correctly but its showling records with the same f.preplyid

CODE

SELECT DISTINCT (f.preplyid), f.pid, f.ptid, f.ptid2, f.puid, f.psubject, f.ptype, f.ptime, t1.tid, t1.tname tname1, t2.tid, t2.tname tname2 FROM forumpost as f, thread as t1, thread as t2 WHERE t1.tid = f.ptid AND t2.tid = f.ptid2 AND f.puid =2 AND f.ptype=1 AND f.puid NOT IN (SELECT fp.puid FROM forumpost as fp WHERE fp.ptype=0 AND fp.pid=f.preplyid) ORDER by f.ptime desc LIMIT 7

Selecting Non Duplicate Records Without Using DISTINCT
how not to display duplicate records without using DISTINCT. Say i have a database:

ID Channel Description
41 Africa News Latest news from africa
42 Technology News Latest Tech News
43 PC News Latest PC related news
44 Africa News Latest news from africa

I run the query 'SELECT DISTINCT * FROM Mytbl' and obviously get the results as shown above with 'Africa News' appearing twice as the primary key ID makes it unique.

How can i return only one of the Africa entries in the results while still having the primary key value returned as well?

Unfortunately with this particular app it is inevitable that duplicate values will occasionally be added to the database but i never want duplicates to be returned from a search query. Can this be done?

Distinct Records Ordered By Date
I have the basis of a forum similar in workings to this one. I am using perl with MySQL the problem i have is a simple SQL one.

I have a table storing all post data including fields.
forumID,
threadID,
postID,
postTitle,
datePosted,
timePosted

I want to list the threads in the order of last posted/replied to. So similar to this you click on the forum you want view and you get a list of the threads in that forum with the most recently active at the top. I don't mind how much data i can get for each thread, i can handle just getting the threadID and then i can loop back through it again and get the specific data for that thread (infact this would be preferable as it would give me more control although would work the server alittle harder).

the closest SQL i have to working is:

SELECT DISTINCT threadID FROM postdata WHERE forumID='$fid' ORDER BY dataPosted, timePosted DESC;

but the order seems not to change.

Difference Between Select * And Select Distinct *
What is the difference between SELECT * and SELECT DISTINCT *
Which one is faster?

Select With Distinct Only One Row
Here is my sql command and the result:

mysql> select distinct(Name), Location from inv_view limit 5;
+------------------------+------------------------+
| Name | Location |
+------------------------+------------------------+
| Adjustable Race | Subassembly |
| Adjustable Race | Miscellaneous Storage |
| Adjustable Race | Tool Crib |
| All-Purpose Bike Stand | Finished Goods Storage |
| AWC Logo Cap | Finished Goods Storage |
+------------------------+------------------------+
5 rows in set (0.03 sec)

But, this is not I want. I want not to show row 2 and 3.
The point is, that I want to show the table with column Name unique, regardless of another column.

Select Distinct
I have a DB with song info. The main fields we're dealing with are:

Album
Artist
Title

What I'm trying to do is list the 5 most recently added albums. Considering the fact that albums have several songs each, I want to just display the [Artist] and [Album] one time.

I used the following mySQL statement
SELECT DISTINCT album, artist, buycd, date_added, picture, albumyear from songlist WHERE songtype='S' GROUP BY album, artist ORDER BY date_added DESC LIMIT 5 Code:

Select Distinct
I have a query where I am searching through a DB multiple times and don't want duplicates. For example I might search through the DB which has 15000 records for all the records that have a 12 in the number field.

The catch is that there are multiple numbers in the field so the same company could come up 4 or 5 times but ultimately it will all bring up the same data for the 4 or 5 companies.

When I use the distinct method it will only return the name not any of the other fields that I need for the query I am processing. Is there a way to remove the duplicates out of the search and still be able to access the other fields? So I can get a distinct name and the corresponding ID?

Select Distinct
I need to do a query on a db, the db has several columns that I need to use such as name, url, uid

there are duplicates in name but not url or uid and I only want to pull 1 each of the names such as:

name url uid
dave www.yahoo.com 1
dave www.google.com 2
joe www.joesplace.com 3
joe www.joesplace2.com 4

I want to pull just 1 each of dave and joe. I have tried using:

SELECT DISTINCT name FROM database

but that only pulls the name column any ideas on how to accomplish my task?

Select * With Distinct On Some
How can I select all fields in a table, but only want distinctive records based on 2 or 3 fields?

Select Max Distinct
i have a users table and a row (banda1) where the users write yours favorite musical band...

now, i want do a "Top 10 more" where select the name (distinct) in order by more write name...

Select Distinct Max
I have a table with the following pertinant fields:

completed
quarter

For each quarter there will be 10 records (40 records per year). A date will be inserted when each of the 10 requirements are fullfiled.

What I want to do is get the last date the 10 requirements are completed so I can see when the querterly is completed.

1. completed will always be a date
2. quarter will look like "1:2005", "2:2005", "3:2005", "4:2005", "1:2006"

So I want to:

1. Select quarterly
2. Select completed
3. Select distinct quarterly where MAX completed

SELECT DISTINCT
I'm using a SELECT DISTINCT to grab values from mysql, my question is,
is it possible to get other field data from the statement, besides the DISTINCT data,

example

sql = "SELECT DISTINCT Value1,Value2 FROM SomeTable WHERE id="&whatever.

can I only get data from Value1,Value2, or is there a way to add in other field names to get data from without them being part of the DISTINCT directive?

Select Distinct
if i do a
select distinct field1 from table1

ill get the distinct field1

but how do i get distinct field1 values and distinct field2 values.

if i do a
select distinct field1,field2 from table1

the result will contain similar values too...

Select Distinct Only On Certain Columns?
We have a table with 10 columns, which log user actions.

Let's say four of them are: time, visitor, visitor_info, siteid

sample data looks like this:
time, visitor, visitor_info, siteid
a)10, 1, 2, 5
b)10, 2, 7, 9
c)11, 1, 2, 5
d)12, 3, 8, 9
e)12, 1, 2, 5

as you can see rows A, C, E are identical except the time part. If I do a select distinct on it, all three rows are returned (as they should), but is it possible to do a select distinct ignoring a specific column, so it would only return a result of one row? (ie a distinct only on visitor, visitor_info, and siteid)?

This is for MySQL5 (using PHP5).

Select Distinct On One Field
Let's say I have a table called T1 with the fields id, datetime and userid.

It can look like this:

T1
id | datetime | userid
1 | 2007-03-03 20:20:20 | 1
1 | 2007-03-03 20:20:25 | 1
1 | 2007-03-03 20:20:30 | 1
1 | 2007-03-10 17:15:30 | 2
1 | 2007-03-10 17:15:45 | 2
2 | 2007-03-15 18:34:45 | 1
I'd like to select distinct values of id. By doing my select query I would like to retrieve two rows where the id is 1 (one with userid 1 and one with userid 2) and one row where the id is 2.

Select Distinct From 2 Fields?
I have a table with 2 similar fields containing state names (e.g. primary_state and secondary_state). How can I find distinct states from both fields?

Select Distinct Union?
I have a query script that works with a classifieds script

class_prodimages.pid can have more than one image entry

my client only wants only one image to be displayed

here is what I have now

$sqlm = @mysql_query("
SELECT
class_products.shortDescription,
class_products.id,
class_products.title,
class_products.price,
class_prodimages.image,
class_prodimages.pid
FROM class_products,class_prodimages
WHERE class_products.featured = 'Y' AND class_prodimages.pid = class_products.id
");
I have put DISTINCT all over the place but it doesnt work.

then searche the forum and see union and tried from the examples but its also not working

SELECT shortDescription, id, title, price
FROM
class_prodimages
UNION
SELECT image, pid
FROM class_prodimages
WHERE class_products.featured = 'Y' AND class_prodimages.pid = class_products.id

Select Distinct From Two Different Tables
I'd like to run a query that will pull distinct items from two separate tables. For example, I have a "user1" table and a "user2" table.

In the "user1" table is a field called "username". In the "user2" table is a field called "thisuser". Some users are in both tables - some are only in one.

I want to run a query to get me a list of all of the users, and only show them once even if they appear in both tables

Any Way To Speed Up Select Distinct
I have a table with a varchar(255) column, it has 111,000 rows. When I do a
select distinct on that column it takes 16 seconds and returns about 25
distinct values, I'd like it to take much less time. I tried creating an
index but the explain on the query shows that it isn't using an index. Is
there any way to speed this up or should I just maintain another table with
the distinct values myself?

Select Distinct On 2 Tables
using mysql 4.0.21

is this the best way to get distinct names from both tables where shift is
20031007:

SELECT m.user_full_name FROM press_maint m
WHERE shift_date = 20031007
UNION
SELECT p.user_full_name FROM press_prod p WHERE shift_date = 20031007
GROUP BY user_full_name

Distinct Select On 2 Tables
I'm trying to do a select distinct data from from 2 tables and the solution I'm having almost works but it's still pulling multiple records from both tables.

Here's the Select statement I'm trying:

SELECT DISTINCT table_1.table_1_id, table_1.title, table_2.field_1 FROM project, conversation WHERE table_1.table_1_id = table_2.table_2_id

Select Distinct Order
I have a database containing my server log files. A sample of the rows would be similar to:

+--------------+------------+-----------------+--------+
| ip | time | resource | status |
+--------------+------------+-----------------+--------+
| 195.93.21.71 | 1112868867 | /about/ | 200 |
| 195.93.21.4 | 1112868869 | /css/normal.css | 200 |
| 195.93.21.2 | 1112868872 | /css/print.css | 200 |
+--------------+------------+-----------------+--------+

Some of the IPs and resources will be the duplicates in the full table. I can select out the number of unique IPs and resources with the following query:

SELECT COUNT(DISTINCT ip) FROM stats

But what I now want to do is find which resource has been requested the most. By this I mean extract the distinct resources (one of each) ordered by number of times it occurs. I've been trying variations on the following with no luck:

SELECT resource from stats ORDER BY COUNT(DISTINCT resource)

Can anyone tell me if this is even possible and/or point me in the right direction to go?

SELECT DISTINCT W/ Joins?
I have three tables - one for documents (document), one for documentviews (documentview) and third for connecting a specific news to a specific documentview (doc2docview). The doc2docview table has a structure of ID, owner (the document ID) and reference (the documentview ID).

One document can be linked to several documentviews, which creates a problem - with this query, if a document is assigned to three documentviews, I get the same title three times.

SELECT DISTINCT document.title as title,
doc2docview.owner,
document.ID as did,
document.summary as summary,
document.source as source,
document.ctime as time,
documentpart.filename as filename
FROM doc2docview
LEFT JOIN document ON doc2docview.owner = document.ID
LEFT JOIN documentpart ON document.ID = documentpart.document
WHERE reference IN (55,56)
AND
document.status = 'A'
ORDER BY document.ctime DESC
LIMIT 100

What should I do to get rid of the duplicates?

Select Distinct On Two Tables
I have two tables, both containing an 'authors' column. Is there a way
to get a unique list of authors from the two tables?

I tried SELECT DISTINCT `authors` from `table1`, `table2`;

but I got an "Column 'authors' in field list is ambiguous" error.

Is there also a query to return only the count of distinct authors from
the two tables?

Increment Distinct In Select
Is it possible to create a field in a select query that is a counter of unique IDs

eg. I have a table of data that includes sessionIDs I want to display the data and number the sessionIDs, so for the first unique sessionID encounters I would want the value to be 1 and then when a second unique sessionID is enountered it would be 2 etc...

MySQL Select Distinct
I have a table that i use to log IP Addr's and dates of people looking at my site, I want to count the entries for each dates so I can make a graph, any ideas?

table is simple looks like;

t_id t_ip t_date

Select Two Fields With Distinct
Here is my query:
CODESELECT DISTINCT(LOCATION) AS LOC FROM tblNetDesc
LEFT JOIN tblNetClass ON tblNetClass.NetClassID=tblNetDesc.NetClassID
LEFT JOIN tblLocation ON tblLocation.LocationID=tblNetClass.LocationID;

Use Of Index For SELECT DISTINCT
I may be wrong but i find something strange in index use when selecting distinct value. For example, let's assume we have a table like that:

a: integer
b: varchar(128)

Let's say that b has only a few distinct values but the table has MANY rows (2 000 000). The column b is indexed, with of course a VERY low cardinality.

Why optmimizer will always scan the 2 000 000 (using index on b though) to execute query SELECT DISTINCT b FROM table while it knows there are only a few distincts values from the index?

SELECT DISTINCT() Issue
I'm trying to select rows with a distinct field along with several other fields in the select query. The query looks like this:

PHP Code:
SELECT
DISTINCT(user_log.session_id),
users.username,
user_log.section,
user_log.session_id,
user_log.access_time
FROM user_log
LEFT JOIN users
ON user_log.userid = users.userid
WHERE user_log.access_time > (" . time() . " - 1200)
ORDER BY user_log.access_time DESC
LIMIT 0,40

SELECT DISTINCT ERROR
SELECT DISTINCT ERROR keep getting an error that says
Table '#sql_fc_0' is read only
when I try to use the SELECT keyword I have tried reinstalling mysql using different versions etc etc buit same result so I am thinking it has something to do with permissions I don't know much about permissions or how to set them is there some special user that mysql uses or something I am on windows server 2003 running mysl 4.0.23 and php 4.3.10 tried using mysql 4.1.9 also same thing worked fine before so I don't know whats wrong something changed but I don't know what does anyone have an idea of what may be causing this?

Distinct Select On 2 Tables
I'm trying to do a select distinct data from from 2 tables and the solution I'm having almost works but it's still pulling multiple records from both tables.
Here's the Select statement I'm trying:

SELECT DISTINCT table_1.table_1_id, table_1.title, table_2.field_1 FROM project, conversation WHERE table_1.table_1_id = table_2.table_2_id

Select Distinct Query
I have a table in my db with two fields, one called link and the other shop. I have another table called link_partners. This latter table has an id column that matches the link column in the former table. I am trying to run a sql statement on the two tables and am able to do so until I try to just show the records in the link_partners table with distinct category column value. Here is my sql Code:

mySQL="select DISTINCT category from link_partners inner join link_partners on link_partners.id = links.link where links.shop = 77"

Select Distinct Date
Is there a way to select just the distanct dates from a datetime. If I try a distanct(date) it will return a high number because they all have different times, but the same date.

Here is a sample:

2005-2-1 00:03:45
2005-2-1 03:10:25
2005-2-1 05:03:20
2005-2-1 12:17:11

returns 2005-2-1

Many To Many Select Using DISTINCT Or Not Return The Same Results
I was on MySQL website looking for an answer, but still need assistance please.

I want to do the opposite of the following:

SELECT * FROM myTable WHERE id IN(1,2,3,4,5) ;

I want to use my array(1,2,3,4,5) to exclude items in the result set, not include them.

So, I want to do something like this:

SELECT * FROM myTable WHERE id NOT_IN(1,2,3,4,5) ;

SELECT DISTINCT With Multiple Fields
I'm kind of stumped on a query I'm trying to build. basically I have a forum table like this:

Post_ID | Thread_ID | Post_EntryDate | etc.....
90 | 22 | ....
89 | 21 | ....
...
...
...
85 | 1 | ....
84 | 1 | ....
83 | 10 |....

and so on. I would like to get the last 20 threads with most recent posts filtering for Thread_ID duplicates of course. so far I have this:

SQLtemp = "SELECT DISTINCT Thread_ID FROM Posts ORDER BY Post_ID DESC LIMIT 0,20"

this succesfully returs a list of unique Thread_IDs of the last 20 threads with most recent posts. However, I need to get the Post_IDs and other fields but when I throw Post_ID in the SELECT statement all I get is duplicate values for Thread_ID

Select DISTINCT Between Multiple Columns??
So i have a table 'hobbies' in which i store up to 6 user's hobbies. The columns are called:

hobby1
hobby2
hobby3
hobby4
hobby5
hobby6

Now I want to list all hobbies but without having the same results over and over.

I tried:
SELECT DISTINCT hobby1, hobby2, hobby3, hobby4, hobby5, hobby6
but this filters only hobby1

Also tried:
SELECT DISTINCT hobby1, DISTINCT hobby2, DISTINCT hobby3, DISTINCT hobby4, DISTINCT hobby5, DISTINCT hobby6

Select Multiple Distinct Values
How do I select a distinct value, and the id for that row?

SELECT DISTINCT Error Message
I am using php 4.3.10 and mysql 4.1.9 on Windows Server 2003 I can SELECT Update insert delete just fine but when I use the DISTINCT keyword it craps out saying "table 'table_#SQL_43_34' is read only" I have a feeling it has somethig to do with making a temporary table or possibly permissions but dont know how to rectify.

MySql Select Distinct Not Working?
Im using the following statement but still getting duplicate Parents ....

How To Select Distinct Rows Using The WHERE Clause
I have an app at work that will only allow me to customize the WHERE clause on a SQL statement.

Currently the SQL Statement is hard coded something like this:

SELECT * FROM `ERRORS`

Is there a way to do a DISTINCT in the WHERE clause that would return the same results as:

SELECT DISTINCT `MEMBER_NUMBER` FROM `ERRORS`

Select Distinct Returns Duplicates
I only want to return one row for driver_guide 46 but "select distinct driver_guide,driver_make..." returns both records:
CODEdriver_guide| driver_make
46 |Jaguar
46 |Ford

SELECT DISTINCT Then Additional Fields
I am trying to do the equivalent of :

Code:

SELECT DISTINCT email, * FROM student_bookings

I know this will never work but think it explains best what I am looking for - I want to select additional fields from each DISTINCT email.

I know I could use a subquery but are there any other ways of doing this? (unfortunatly I can't use subqueries as we don't have the correct version of mySQL for that ).

Select Distinct Results Without Throwing Away Data?
id series_id image date
6 2 1191956895.jpg 2007-10-09
5 3 1191956773.jpg 2007-10-09
4 2 1191451083.jpg 2007-10-03
3 1 1191451026.jpg 2007-10-03
2 1 1191451012.jpg 2007-10-03
1 1 1191450488.jpg 2007-10-03

if you take a close look at the code above you might be able to see that i have these images assoicated with one of 3 series. id like to be able to select the most recent from each one and discard the rest. however a command link distinct on the series_id throws away all the other data around it that i need.

the results would look more like

seires_id image
2 1191956895.jpg
3 1191956773.jpg
1 1191451026.jpg

i imagine this would be much like a forum sorting the most recent topics posted in a forum by finding the most recent posts for each distinct topic.

Select Distinct Records

I am trying to compare the date part of a datetime value field with today's date.... Here's the sql: mySQL = "Select * from Test WHERE TheDate LIKE'"&date()"' ORDER BY TheDate" Set rs= Con.Execute( mySQL ) That return nothing even though Test has records for today..

I am trying to compare the date part of a datetime value field with today's date....

Here's the sql:

mySQL = "Select * from Test WHERE TheDate LIKE'"&date()"' ORDER BY TheDate"
Set rs= Con.Execute( mySQL )

That return nothing even though Test has records for today..