MYSQL :: SELECT DISTINCT, (and Display Other Fields Not Distinct.)

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;

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

Multiple Distinct Fields
Okay SQL gurus, I have a table laid out as so:
RowID | UserID | UserType | pID

Now, how can I write a SQL query that would select every distinct UserID and pID combination.

So if the data in the table were:

1 jjdoe internal 19
2 jjdoe internal 6
3 jjdoe internal 6

It would only return rows 1 and 2.

How To Get All Distinct Words From Many Fields Of A Table
I have a table with many text and varchar fields and I would like to get
all distinct words from these filelds.

For example:
table Pet:
id int(10) unsigned
legende text
notes varchar(255)

#id #legende #notes
1 mysql is very very good' is it true
2 just mysql test

I would like to get a list (sorted if possible) like:
#word #occurence number #id
good 1 1
is 1 1
just 1 2
mysql 2 1,2
test 2 1
very 2 1

Getting Two Distinct Fields From One Tbl From A Join Query Of Two Tables
Im trying to get distinct (the same row returned only once) user_id and
forename from a tbl of users (they are always distinct) where user_id
in that tbl equals friend_id in another table friend_id, which 'should'
be unique ie there 'should' only be one row of a 'friend connection',
but in the case of bugs etc this might not be true, what i want to do
is select only the row of tblusers with the user details that are
matched by join condition below, but only ONCE for each, ie not in as
many times as there are the same connection in tblfriends.

For some reason the result i am getting is a repitition of the 2 fields
in tblusers (that i am selecting) for as many rows as there are in
tblfriends that match the join condition.

How can i make it only return a row once?

Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value.
example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates).

how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?

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

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

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

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 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 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 Domain Name From An Email Field?
Is it possible to select distinct domain names from an email field in a table in a single statement, extracting the domain after the token '@'?

SELECT DISTINCT On A Varchar Column (big Table)
I have a table with 10 million rows in it. Now, I need to export a list of unique values from a varchar column. Normally I would do:

SELECT DISTINCT MyColumn INTO OUTFILE '/dumpfile.txt' FROM MyTable

But this is VERY time consuming and slows my PC down.

Can you give me an advice about what should I attempt to make it last the less?
For instance, should I create an index on MyColumn AND THEN execute the SELECT DISTINCT?

I may look silly, but I also tried using a Memory table thinking I would cut the indexing computation time but my RAM is not big enough to contain all the rows.