MYSQL :: Selecting Multiple Columnn Values Into 1 Distinct/unique List

Selecting Unique Values From A Table (NOT DISTINCT!)
Lets say we have a table that has the field photo_id and has values of it many times. For example 245 can exist any number of times (1, 5, 8, e.t.c.).

I want to select the values that exist in the table ONLY ONCE. Please note that i dont want to select each value once (using DISTINCT), that would return all the values that exist in the table.

For example lets say the table has the following values:

245
563
776
224
563
563
776
776
776
776

The query i want to make should return only the values 245 and 224 as 563 and 776 exist many times in that table.

SELECTing Distinct Values Over Multiple Columns
I have a table setup for populating a drop down menu.

CREATE TABLE catagory
( ID char(10) PRIMARY KEY NOT NULL,
cat1 varchar(50),
cat2 varchar(50),
cat3 varchar(50) );

Now each row is for one client to populate so that he can be listed under multple catagorys.

The problem comes in that I need DISTINCT catagories but say the client with ID 00000-0001 has a cat1 of greek, and a cat2 of pizza, where on the next, a client with a different ID has a cat1 of italian, but a cat2 of pizza. How would I word the query so that I can return distinct results from both rows AND columns or is this a problem on the PHP side of things (how i'm coding the page)?

as it stands right now, my query of:
SELECT DISTINCT cat1, cat2, cat3 FROM catagory;
isn't working very well (I get all the results, as if the DISTINCT wasn't there)

Assigning Unique ID To Distinct Values
I have a table `books` with booktitle and authorname columns. In my
applicatoin booktitle has unique values but authorname doesn't. ie an
author can have many books but not the other way around.

I need to add more author info in the database. So I need a new
`authors` table with authorname as a column (along with address, email,
phone etc) and an authorid primary key. Then I need to replace
books.authorname column with a column containing the corresponding
authorids

How can this be done in sql?

Help With Selecting Unique Values
I have a table called 'pictures', this holds information for every picture in an art exhibition.

Its structure is (catno, title, artistfirstname, artistsurname, size, medium,price,dimensions)

sample data would be something like.

1, birds, James, Quinn, 23*23, watercolour, 355, unsold

2, snake, David, Blake, 43*43, pencil, 499, sold

3, lion, David, Blake, 25*53, pencil, 250, unsold

4, tiger, Adam, Healy, 63*73, watercolour, 235, sold

5, fish, John, Smith, 12*15, charcoal, 450, sold

6, eagle, Peter, Blake, 54*54 pencil, 950, unsold

I need a page that lists all the exhibiting artists and was wanting to use the database to generate this rather than having to do it manually.

Artists can have multiple pictures in the system. Also some artists will share surnames. I need a statement that pulls out all the unique artists, giving a result like:

James Quinn
David Blake
Adam Healy
John Smith
Peter Blake

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 Multiple Distinct Values
How do I select a distinct value, and the id for that row?

Select With Multiple Primary Keys Against List Of Values
I have created a method that automatically parses the metadata of a specified table and searches each field to effectively create a database search.

The way it currently stands, it only supports tables with a single PK. Basically it returns a string that can be appended to the WHERE clause in a SELECT.

i.e. "ID IN (1, 2, 3, 4)"

Basically the PK field name and a list of matching PKs from the search.

I realized though that this will break if I have a table with multiple primary keys.

An elegant solution for this eludes me, and that is what I am asking for input on.

Right now I can only think of returning something like :

"(ID = 1 AND SSN = '123121234')
OR
(ID = 2 AND SSN = '432434321')
OR ..."

This obviously would become a monster if many results were returned.

And if I did something like :

"ID IN (1, 2) AND SSN IN ('123121234', '432434321')"

This will either return false positives or nothing at all ...

Is there a better way for me to create this conditional?

Selecting Two Values From Multiple Tables
My apologies for making this post, i'm sure that this question has been answered before, but for the life of me I just don't know what keywords to use.

Two tables, sold_items (buyer_id, seller_id) and users (id, nick).

My objective is to loop through the sold items for a particular buyer_id, and retrieve the buyer and seller username (nick).

The problem is that I don't get how I can select both nicknames when as far as I know, I can only join the buyer_id OR seller_id to the user table.

This is how I would do it normally

PHP

$query = mysql_query("SELECT s.buyer_id, s.seller_id, u.nick FROM sold_items s, users u WHERE s.buyer_id = u.id");

while ($row = mysql_fetch_array($query))
{
$buyer_nick = $row['nick'];
$seller_nick = mysql_result(mysql_query("SELECT nick FROM users WHERE id = ".$row['seller_id']),0);
}

As you can see this creates a query inside the loop, but how could I select both nicks in a single query?

Selecting Records Containing Multiple Values
I want to query a database for records with which the field 'network_letter' contains G,R,M, and Q and display those results back. Is there an easier way than doing the following:

$query1 = mysql_query("SELECT * FROM global_pops WHERE network_letter='G'");
$query2 = mysql_query("SELECT * FROM global_pops WHERE network_letter='R'");
$query3 = mysql_query("SELECT * FROM global_pops WHERE network_letter='M'");
$query4 = mysql_query("SELECT * FROM global_pops WHERE network_letter='Q'");

and then merging those results into one variable and printing them?

Enforcing Unique Field Values Accross Multiple Tables
I have 2 tables (t1,t2) with unique keys defined in each. In addition to enforcing unique key values in each table, I would also like to enforce unique key values accross both tables (ex. If key value 'XXX' appears in t1 I don't want to allow entry of key value 'XXX' in t2 and vice versa).

Distinct List Of Parents With A Child Count Result
Parent (id, name)
Child (id, parent_id, session_name)

I'm trying to write a query that will have one output row per Parent. Each row will show:
parent.id, parent.name, count of children for that parent AS children_count

Some parents have no children. Those rows could return null or 0 for the children_count field.

I know multiple methods external to SQL to solve the overall problem such as returning the join and counting where child.ids (for the same parent) is not null.

==>> but is there a way to do it in one SQL query that returns just the info I need?

Selecting DISTINCT Except For One
There's this membership script, I am adding usergroups to it and so I made a new field called groups and everything. Now I want to add it to the admin profile update page. I'd like it to be in a dropdown menu and I already know how to do that, using the distinct function and everything. But I'd like the user's current usergroup to be the default on the dropdown menu, so I know which one they are now.

I was going to retrieve the user's current group but if I get the groups from the whole list as well, it would come out twice wouldn't it?

Selecting Distinct
Can someone please tell me how I can do this:

I have an appointment table which has appointment_id, client_id,
appoinment_time (as unix timestamp). Clients can have multiple
appointments.

I want to select all appointments from the appointment table but only
one appointment (the latest one) for clients that have multiple
appointments.

Populating A List/menu With Table Data (non-unique)
I have a table (pictures) that has a unique field (pictureid) and several non-unique fields. I would like to populate a list box with the records that will likely have duplicates (username and galleryname).

I want to populate it in a way that there will be no duplicates in the list/menu, even though there are duplicates in the database. I can't seem to wrap my mind around the logic to get this done.

Selecting List Boxes
I am wondering if there is an easy way for php to automatically reselect an item from a list box.

The purpose is this:
The user will be filling out a form which, before it gets sent to mysql, I want them to double check what they typed in. I am doing this all in one php page. So the user clicks "submit" and it brings them back to the same form asking them to please double check. Everything then is entered in the form already. Witht he text fields I just set the initial value to be it vvalue in the $_POST array. However, I am not quite sure how to do this with the lsit boxes.

Selecting A List Of Date
Is there any way to select a list of date by specifying the from date and the to date.
Such as:

From Date : 01-01-2005
To Date : 05-01-2005

Results
-----------
01-01-2005
02-01-2005
03-01-2005
04-01-2005
05-01-2005 .

Selecting Distinct Rows
I have a mysql query which I am trying to select distinct information with.

Here is the query:

Selecting The Inverse Of Distinct
I would like to select the fields that exists more than one time.

Description of my table is:

Create table MYTABLE (CLASS_ID char(2),STUDENT_ID char(2));

I need to select the students that are in more than one class!

Selecting A Distinct Query
I want to: select name, address, city, state, zip from client;

However I want to query a distinct address(some of the people work at the same address, and I only want one letter to go to each address.).

Is it something like:
select name, distinct[address], city, state, zip from client;

I know this is not right, but does anyone know this? Thx.

Selecting Distinct Names
I am attempting to create a list of servers that have distinct usernames, and if there are multiple servers with the same name, show the one with the lowest ID.

Here is my existing SQL but the DISTINCT does not work:

SELECT DISTINCT(name),date_generated,location,pass_validation,ID,time_saved FROM SQL_SVA.servers

Selecting Unique Rows
I have a query that checks a list of users against a list of items and returns for example the following.

SELECT users.username, ratings.recording_uid
FROM users
INNER JOIN
ratings ON (users.uid = ratings.user_uid)
WHERE ((users.username = 'a') OR (users.username = 'b'))

which returns...

username recording_uid
a 1
b 1
a 2
b 2
b 3
a 4
b 4
b 5

How can I add to that query so that I know what user a has that user b doesn't and vice versa? eg. so that it returns

username recording_uid
a 3
b 5

Selecting Distinct Results In A Join
SELECT p.post_title
, p.post_name
, c.cat_name
, DATE_FORMAT(p.post_date, '%M %D, %Y') AS dateadded
FROM wp_posts AS p
LEFT JOIN wp_post2cat AS pc ON p.ID = pc.post_id
LEFT JOIN wp_categories AS c ON pc.category_id = c.cat_ID
WHERE MATCH(p.post_content, p.post_title)
AGAINST(TRIM('merchant') IN BOOLEAN MODE)
ORDER BY p.post_date DESC
I am doing a fulltext search on a Wordpress database. Some posts are in multiple categories. When using the above query I will get a result for every category a post is in. I don't want that. I have tried using distinct with no effect. The wp_categories has a row for each category a post is in and how I am joining it seems to be the culprit.

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 Values
Is there a way I can select unique values from two different columns so the resultset has unique values from both of the columns combined.
I have:

SELECT DISTINCT TEAM_ID, TEAM_ID2 from sf_team_player

But this as you all know only retrieves unique values from TEAM_ID then TEAM_ID2. Is there a way I can retrieve uniqye values from both combined?

Distinct Values
I am doing a script with php and mysql and i am having trouble on mysql statement what happens is that i have a "orders" table with session_id, username, total and date.
What i want to do is to distinguish duplicated session id's and still list all the fields on the results.
I have on the table two session id's that are the same the rest is different i used the mysql_query statement " Select Distinct session_id From orders" but in this case with will only show the session id fields and will sort the duplicated session_id but i want to have this but listing all the fields such as username, total and date.
If i do "Select Distinct session_id, username, total,date from orders" it will return all the values but to distinguish only the session_id won't work.
I want to know if there is a way to distinguish only the session_id field and return the rest of the field values.
I have an example on this page come2portugal.com/tests/test.php
you can see two session ids with the value 001 it should list only one 001 and list all the other results that are not duplicated.

Selecting Item List And Count From Other Table
I have been failing at trying to get this query to work. Here is the query I can't get to work:

SELECT cont.key_no, (
SELECT COUNT(1)
FROM items itm
WHERE itm.cont_key_no = cont.key_no ) AS item_count
FROM containers cont
WHERE cont.status = 'OPEN';

Here is a query I managed to get working, but it doesn't quite do what I want it to. It doesn't return a container and item count for containers that don't have items:

SELECT cont.key_no, COUNT(itm.key_no) AS item_count
FROM containers cont, items itm
WHERE cont.status = 'OPEN'
AND itm.cont_key_no = cont.key_no
GROUP BY cont.key_no

Here are what the tables look like:

containers
----------
key_no
status

items
---------
key_no
cont_key_no

If you can't tell from the examples above, I am trying to return a list of all 'OPEN' containers and how many items are in the container, even if no items are in the container.

Unique Values
I have a rather large database table that has a column with maybe 20 different values repeated on and off throughout the database (around 4000 records total)
Could someone shoot me a query example of how I can pull each value from this column once to create the list of the 20 or so reoccuring values? Lets call the column work_type.
I think I'm maybe just missing knowledge of a particular query command for the WHERE portion maybe?

Unique Values
I need to do the following:
From a table that have this fields : Id, Account1,Account2,Account3,Account4
I have to get all the DISTINCT values in the accounts fields (Account1,Account2,Account3,Account4)

Here is a example:

Id Account1 Account2 Account3 Account4
1 111 222 Null Null
2222 333 Null Null
3 Null Null 111 444

The result should be

Accounts
-----------
111
222
333
444

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.

Finding DISTINCT Values
I am combining two tables and want to have only the distinct rows from the db. The problem is I get ALL the rows in the tables since lastUsed.time is a time stamp and so it is almost always a distinct value. Is there a way to limit the effect of DISTINCT to only one column?
I tried SELECT ( DISTINCT lastUsed.componentID)... but got an error. When I remove lastUsed.time from the query I get the desired number of columns...
This is my code:PHP Code:

SELECT DISTINCT lastUsed.componentID, KOPF.DBEZEICHNUNG, lastUsed.time
FROM `lastUsed` , `KOPF`
WHERE componentID = KOPF.KHAUPTNUMMER
ORDER BY lastUsed.id DESC
LIMIT 0 , 20

Returning Unique Values
I currently have a table called â€œpagesâ€ containing the following columns:
pageid, domainid, location, lastread
I've put together a query that returns 10 rows with the lowest â€œlastreadâ€ values, for which this works fine:
SELECT * FROM pages ORDER BY lastread ACS LIMIT 0,10;

I now the have the added complication that I want to make sure each value in â€œdomainidâ€ is unique. That is to say, not repeated within the 10 results returned. I though I might be able to do something using â€œGROUP BY domainidâ€ but that cannot just work on the one column as far as I can see.

Finding Unique Values
I have a table with a box field and a box2 field ... I want to grab all unique box numbers ... if a box number is in box and box2 I only want to pull it once ... I tried a bunch of different queries but none seem to do what I want.

Counting By The Unique Values
Put simply I want to count how many rows there are in the database with each value for the collum such as the collum 'username' (e.g. how many rows have evenstar7139 for 'username'?).
When this code, whatever it is, is run through the database I should have numbers for how many rows each different username has. (e.g. evenstar has 207, nova has 225, roonie-roe has 98, etc.)
this also needs to order the results by highest number first descending to lowest number last.And if I could limit it by something like the 10 highest that would be great too.

Count Unique Values
I want a table show me how many of each year we have in the db.I tried this:Code:

SELECT year,COUNT(DISTINCT year) FROM rbindex WHERE courtID = 1

But I got a MySQL error:

Quote: #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
Is there another way I can do this?
I want my results to look like this

-----------------
year | total
----------------
2004 | 20
2003 | 5

How To List Duplicate Values In A Column?
I have a large table which has duplicate values in one of the columns. I can easily see how many duplicates there are by using COUNT and DISTINCT, but I can't see what the duplicates actually are.

I guess there's a simple way to list duplicates, but I can't get my head round it.

Distinct Values In Mysql Query
I have mysql query but i am not sure about it,bcoz it return many rows..
I want like distinct values from three tables with ic number.
Which is posted by user...

"SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,loguser.staffcode,
DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1,
DATE_FORMAT(loguser.creationdate, '%d-%m-%Y %H:%i:%s')As formatteddate
FROM dealerdtl,loguser,access where access.icnumber=loguser.icnumber AND loguser.icnumber = '$custic'";
This query return many rows i dont know why, i want only one row which is
inserted by the user as customer id.

DISTINCT Returning Different Values Than GROUP BY
Here's the results of the two queries obfuscated:
mysql> TRUNCATE normalized;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO normalized (a, b, c, d, e)
-> SELECT DISTINCT a, b, c, d, e
-> FROM flattened;
Query OK, 23003 rows affected (46.95 sec)
Records: 23003 Duplicates: 0 Warnings: 0

mysql>
mysql>
mysql> TRUNCATE normalized;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO normalized (a, b, c, d, e)
-> SELECT a, b, c, d, e
-> FROM flattened
-> GROUP BY a, b, c, d, e;
Query OK, 47629 rows affected (45.14 sec)
Records: 47629 Duplicates: 0 Warnings: 0

Note that the number of records in the DISTINCT was 23,003 while the number of records using the GROUP BY (which returned the correct number, btw) was 47,629.

For reference, the column types are as follows:

a = varchar
b = varchar
c = varchar
d = int
e = int

Why would GROUP BY return more (and correct) results vs. DISTINCT? Is there a number of fields that are limited?

One of the other things that doesn't make any sense at all is that the data from the distinct is actually wrong at times. For example, the value of the last column would be incorrect entirely with the data from the flat table.

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

Making Values Of Two Columns Unique
1. Two columns, one is CHAR type, second is INTEGER. None of them is Unique, but combination of them must be. How to make this in MySQL 4.1.10?

2. What kind of Index (INDEX, UNIQUE INDEX,...) I should make on this columns to get better performance?

Extraction Of A Substring In A List With ; Separated Values.
In my database, a have a table with a field which you can put many values with a ; between each. The software is done like this.

Exemple: Field: Version
Data: 3.0.1;1;2;4;5.1.2 etc.

I want to extract all entries in the database with version = 1.

The problem is that the result give me entries with version 3.0.1 , entries with version 5.1.2 and entries with version 1.

As you probably guess, I just want entries with version = 1.

I tried with WHERE (version IN ('1;')) but it returns nothing except entries
which version is only 1; I need something to find a substring = 1; with nothing in front
of the 1 or substring = ;1;....... something like that... or better way.

Getting Distinct Field Values From Joined Tables
I have two tables that each have a column 'name' The value of 'name' may be repeated in both tables. If I do, 'select a.name, b.name from tablea a, tableb b' I get all the names. However, I would like to get the distinct names. But mysql gives me a syntax error if I do 'select distinct(a.name, b.name) from tablea a, tableb b'. Is there a query that will give me the distinct names from both tables?

Echo Distinct MySQL Columns Values
Any way to echo distinct MySQL column values? I can only see COUNT(DISTINCT column). Example:

Red
Blue
Green
Blue
Blue

Would echo as:

Red
Blue
Green

Returning All Unique Values From A Table Field
I wrote a select that works fine but I am looking at it and thinking that the query might require mysql to scan EVERY row before giving me my answer.

Lets pretend I have a table with fruits and want to return all the available colors of those fruits that I currently have in the database.

Is this an efficient or horribly inefficient statement?

"select color from fruits group by color"

It seems to return what I want:

fruits:
------
blue
green
yellow
...

I am just wondering if it goes through EVERY row and groups them?
If I have the table indexed by color am I allright?

Primary VS Unique Keys & NULL Values
My table:

ID: INT
SET_ON: TIMESTAMP
CLOSED: DATETIME
OPTION1: ENUM
OPTION2, etc...

When a record is created, we populate all fields except CLOSED, which remains the default of NULL. This is the current record for this ID. We're tracking changes, so if a change is made to the options for an ID, we update the current record with NOW() to the CLOSED field and create another record.

My question is about how best to key this (InnoDB) table. PRIMARY doens't allow columns with NULL values, so using ID and CLOSED won't work (unless I switch to using 0 filled value instead of NULL, which I'd rather not.)

ID & SET_ON would work, but I'd like to restrict each ID to a single current record. I can add a UNIQUE key with ID & CLOSED, indexing ID again, but that seems wasteful. This also makes the table's PRIMARY key seem a bit depreciated... Is there a better way to get what I want?

MySQL Command To List Enum Values Of A Column? - ALL SET
I'm wondering if there is a MySQL command to list all the enum values of a given column. For example, I have a column defined as:

sgroup enum('none', 'aaaaa', 'bbbbb', 'ccccc') not null
What command can I run to get the list of these possible values?

Pivoting A Column W/ Undetermined Number Of Distinct Values
Suppose I have 2 tables and each table has 2 columns (name, category). I want to make a script that takes in one parameter from the user specifying which table they want to use and then give them a table that has as many columns as there are distinct values within the category column and a count of all people belonging to that distinct category. Code:

Selecting MAX Values
I would like to select the MAX of a value as well as the corresponding value in another column on the same row. Let me try to explain below:

Name NO Runs
Test1 1 200
Test2 0 100
Test1 0 30
Test2 1 110
Test1 0 210
Test2 1 150

A simple SELECT Name MAX(Runs) AS MAX FROM Table GROUP BY Name would give me:

Name MAX
Test1 210
Test2 150

I would like to select the NO value that corresponds with the Max value. So the below is what I ideally would want

Name Max NO
Test1 210 0
Test2 150 1

Right now by querying with SELECT Name MAX(Runs) AS MAX, NO FROM Table GROUP BY Name - it is only pulling back the first NO value it comes across, not the value that corresponds with the MAX value and giving me the following:

Name Max NO
Test1 210 1
Test2 150 0

Any ideas anyone has about getting the corresponding NO value for the Max(Runs) value.

Creating Column Of Unique Values In Order To Have A Primary Key
I have a flat file that has no columns with unique values.In other words, there is no column available that I can define as the PRIMARY KEY, and thus, cannot incorporate the data into a table.

So, my question is this: How do I modify the table so that I can insert a column of unique values for each record? A column of unique values would be something as simple as a letter followed by the row number of the record (e.g, T1, T2983, etc.).

Field Values Shifting During Load Infile W/column List
I'm loading a text file into a MySQL database using the following (specifics obfuscated out of confidentiality purposes)

CODELOAD DATA INFILE 'file.txt'
INTO TABLE test_temp
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
IGNORE 5 LINES
(col3, col5, col8, col10);