MYSQL :: Using Distinct In Sum Function

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?

IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere!

The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over Â£300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....

DISTINCT Again...
Table `A` has rows `id` (primary key) and `name` both integers.

I want to return all rows from table `A` with all fields, BUT with distinct `name`.

So the query is something like that:

SELECT * FROM `A` WHERE `name` NOT IN(previous results of this query)

or something like that:

SELECT DISTINCT(`name`) * FROM `A`

DISTINCT
I have table `A` with columns `id`, `id2`, `time`, `news`.

`id` is primary key.
`id2` is key but not unique.

I want to retrieve 15 latest rows (ORDER BY time DESC LIMIT 15)
but all of 15 rows must have DISTINCT `id` (and only `id`)

So the query is something like this (it is not working of course)

SELECT
DISTINCT ROW(id) # This is my invention, I want something like that.
id2, time, news FROM news ORDER BY time DESC LIMIT 15

Distinct Row
I have table A:
============================
lang | id | sentence
--------------------
'de' | 1 | 'german word 1'
'de' | 2 | 'german word 2'
'en' | 1 | 'english word 1'
'en' | 2 | 'english word 2'
'en' | 3 | 'english word 3'
============================

I want with a query return
============================
lang | id | sentence
--------------------
'de' | 1 | 'german word 1'
'de' | 2 | 'german word 2'
'en' | 3 | 'english word 3'
============================

The meaning: For each id, if german sentence exists, return it, else return english, BUT NOT BOTH.

So, return DISTINCT but ONLY for id.
The query, until now:

SELECT * FROM A WHERE lang='en' OR lang='de' ORDER BY IF(lang='en',1,0);

Distinct
How can I perform a query where multiple elements have to DISTINCT?

I have this query:

SELECT DISTINCT title, item.id, item.type
FROM comment, item
WHERE comment.item_id = item.id
ORDER BY comment.date

This still gives me the same title multiple times. I'd like to have only one title mentioned as a result...

(The idea is to have a list of the latest comments posted to an article, but ofcourse if that article has been commented upon 3 times lately, it's a bit silly to have it 3 times in the "Latest comments:" tab...)

DISTINCT Keep
I have an SQL problem that has been bugging me for a while. I have a DB with members and groups. Members can be part of more than one group. For each group that a member is in, they can have a role and can be defined as a leader of that group.

I have the following table:

CREATE TABLE member_groups (
id_member INTEGER(10) UNSIGNED NOT NULL,
id_group INTEGER(10) UNSIGNED NOT NULL,
role TEXT NULL,
is_leader TINYINT UNSIGNED NULL DEFAULT 0,
PRIMARY KEY(id_member, id_group)
)

So here's my problem: Imagine I have 3 groups; cars, american_cars and blue_american_cars. I want to select all the members that are in any of these groups, plus their roles in the cars group (I don't want the roles in the other two groups). Finally, I want to order the results according to whether the members are leaders of the cars group or not.

I could do a left join and select role and is_leader only if they are in the cars group, but I could get a situation like the following:

id_member, id_group, role, is_leader
'14', 'cars', 'salesman', '1'
'14', 'american_cars', (not present), (not present)

In this case I will have to use the distinct keyword. But which row will MySQL keep and which one will it throw away? Is there any way of knowing?

DISTINCT Value
I have simple table like,
create table fileinfo (
filename varchar(512) not null,
offset integer not null,
length integer not null,
fingerprint varchar(2000) not null
);

All I want to is to find the DISTINCT fingerprints and add the sum all the corresponding length of those fingerprints. I can find the unique fingerprints and I can sum up the lengths but can not come up with anything that will solve my issues.

So for example if I have a table like
xx 0 512 abdsflkdsfier
yy 0 1024 ldkfjjdfljflf
zz 0 512 abdsflkdsfier

Then I want to find out the two unique fingerprints and sum up the length corresponding to that (512+1024).

Using Distinct
So I have a table that has repeated item numbers. I want to be able to select on them, but have no repeats show up in the resultset. I also need to get the ID for each, which will of course always be a unique number.

My problem is how to do this. I've looked at using DISTINCT, but that seems to only work on all columns you want to select. So if I do:

SELECT DISTINCT itemNum, ID FROM mytable.

Get Distinct
I have to present some redundant data in a non-redundant table. Take the following rows:

GO:0004872, Molecular function receptor activity, AutoFACT, IEA
GO:0004872, Molecular function receptor activity, uniref90, IEA
GO:0004872, Molecular function receptor activity, nr, IEA

They are identical except for having 'AutoFACT' or 'uniref90' or 'nr' as a field value. Sometimes all rows will exist and sometimes only one or more of the rows that don't have 'AutoFACT' will exist.

I need to present only the 'AutoFACT' row if it exists. If it doesn't exist, there is a hierarchy of other field values: uniref90, if not uniref90 then nr, if not nr then.

DISTINCT
I use DISTINCT to select distinct values of one column... but i can not use distinct selecting more values of that row.

select DISTINCT(field1) from table works
select DISTINCT(field1), field2, field3 from table does not work

why ?

Using DISTINCT
I'm trying to use DISTINCT as a solution to avoid displaying duplicates. I found this in another thread here, but I don't know how to make it work like I want.

select distinct time_stamp, comment from table where id='whatever'

The above is close, but I only want distinct on the comment column - something like:

select time_stamp distinct comment from table where id='whatever'

But obviuosly that doesn't work. Is it possible to accomplish what I'm trying to do here? Haven't found many examples on using distinct.

DISTINCT
I know that SELECT DISTINCT(artist) from albums will give me all the unique artists from the albums table, but what I am trying to do is select all columns from the albums table where only artist is distinct. I know this is probably a simple thing, but I can not seem to find an answer.

Problem Using Distinct
I'm trying to get the first elemet of each category, this is my query:

Select id,category,image FROM catego_maps,image_maps WHERE id = id_category ORDER BY id

id-----category---image
-------------------------------
1-----fruit--------galery/maps/banana.gif
1-----fruit--------galery/maps/orange.gif
1-----fruit--------galery/maps/apple.gif
1-----fruit--------galery/maps/pear.gif
2-----vegetable---galery/maps/letuce.gif
2-----vegetable---galery/maps/carrot.gif
3-----Seed-------galery/maps/bean.gif
3-----Seed-------galery/maps/rice.gif

I tried

Select DISTINCT id,categoria,imagen FROM catego_mapas,imagen_mapas WHERE id = id_categoria ORDER BY id

but it is not what i want, how can i get:

1-----fruit--------galery/maps/banana.gif
2-----vegetable---galery/maps/letuce.gif
3-----Seed-------galery/maps/bean.gif

DISTINCT Vs GROUP BY
Is there any performance difference between DISTINCT and GROUP BY?

I have a table articles and I want to get unique users who posted the articles. userid has an index. What should I use DISTINCT or GROUP BY? Is there any difference between their performance? I am concerned about the performance.

SQL
SELECT userid FROM articles WHERE posted_date BETWEEN &#55614;&#57159;-01-01' AND CURDATE();

DISTINCT, GROUP BY, Or Something Else?
I have a situation where I need just a single row in place of multiple rows where the values of TWO specific fields are the same. To illustrate, suppose I have the following fields in a table:

ID, Date, Time, UID, Description

And further suppose that I have the following 3 rows in the database, with values obviously in the same place as the fields listed above:

1) 1, 2008-08-12,, 10845, Some description (yes, Time is NULL)
2) 2, 2008-08-13, 11:45:00, 10845, Some description
3) 3, 2008-08-12, 11:15:00, 10845, (here, Description is NULL)

I want to display rows 1 and 2 ONLY, based on wanting only one row per Date/UID, and based on the existence of a description in row 1. In other words, the mere presence of a value in the Description field is the sole determining factor in which row is selected for a given Date/UID pair.

Just as an added wrinkle, UID corresponds to the ID of a Users table, so I'm selecting from two tables.

I couldn't get DISTINCT to work, since I'm not dealing with duplicate rows, and I couldn't get GROUP BY to work, since the Description field is outside the GROUP BY clause by virtue of being variable (it was hit or miss as to whether or not I got the desired value for Description).

DISTINCT() Not Working
Does anyone have any idea why i can't get this to return distinct values?

PHP

SELECT DISTINCT(agi.userid), agi.imageid, agi.title, agi.dateline, agi.catid, agi.thumbname
    FROM a_gallerys_images agi
    WHERE agi.valid = 1 AND agi.open = 1 AND agi.catid = 2
    ORDER BY agi.dateline DESC
    LIMIT 4

Inswrting A Distinct Value
Is there a way to use INSERT INTO to add a distinct value into a column ? Basicly I have a column that allows for 2 records to be the same and i dont want it as a primary key but want to have the insert command make sure what it's inserting isnt the same as something already in the column.

Finding MAX Among DISTINCT
I'm pretty new to mysql, and I'm looking for some help.

I have a database for photos -- the columns are id, id_num, date, and photo. The id_num ties certain records to specific clubs in a different database.

The problem I'm having is I want to select DISTINCT(id_num) and then from within those results the MAX(date) available then ORDER BY date DESC.

It seems nomader how I tweak my query I either will get mulitple results of the same id_num or cannont get them to list by date DESC.

DISTINCT On Only One Column?
I would like to select two columns from a table and have MySQL use DISTINCT to filter only one of the columns.

So for example:

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?

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?

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

Insert Distinct
I want to do an insert only if it's unique, meaning I don't want to insert if the row with exactly the same data already exists.

The method I can think of is to check whether the row exists first, and if it doesn't, then I do the insert. Any idea how to do this in a more elegant way?

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

Distinct Letters
my task is to build a smart keyboard (graphically of course) which shall know the existing letters in advance.

for that task, let's take a very simple table INTERPRET:

Id | Name
1, Beck
2, Bluestones
3, Blues Brothers
4, AC/DC
etc.

Now I would like all the distinct letters from Name of the first letter. The result would look like:
-> ['b','a']

Or let's consider the second letters of Name:
-> ['e','l','c']

can anybody give me a hint how the sql-command should look like?

DISTINCT Question
I have three tables:

table_a (name TEXT,url TEXT,type INT,...)
table_b (num INT,url TEXT,...)
table_c (num INT,url TEXT,...)

I want to SELECT rows from table_a that don't have a matching 'url' in either table_b or table_c. I think that's a DISTINCT question but I'm not really sure.

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?

Distinct Elements
I have two tables. One contains a list of people, the other contains a list of things people do. I store the list of things that people do by the people id number. When I create a list of people to work on I only want to select those people from the people table that do not appear in the things that people do table. Here is the query I am using:
SELECT DISTINCT LOWER(P.txtFirstNames) AS txtFirstNames, LOWER(P.txtLastName) AS txtLastName, P.nId, P.txtAptNum, P.txtHouseNum, LOWER(P.txtStreetName) AS txtStreetName, LOWER(ST.txtStreetType) as txtStreetType, P.txtStreetNESW, LOWER(CL.txtCityName) as txtCityName, PV.nId, PL.txtProvAbbrv, P.txtPostalCode, P.txtPhoneDay FROM person AS P
LEFT JOIN persvolunteer as PV ON P.nId = PV.nId
LEFT JOIN citylist as CL ON P.nCityNum = CL.nCityNum
LEFT JOIN streettypes as ST ON P.nStreetTypeNum = ST.nStreetTypeNum
LEFT JOIN provincelist as PL ON P.nProvinceNum = PL.nProvNum
WHERE P.txtArea = '021'
ORDER BY P.txtHouseNum, P.txtStreetName, ST.txtStreetType;

Counting Distinct
I have a field that contains filenames. I'd like to count how many times a certain file name appears in that field.
So, for example if I have 3 unique filenames, I would like to be able to see something like this:

file1 = 120
file2 = 109
file3 = 76

The first part seems should be:
select count(file) from download
But how to specify returning a count for each distinct file?

Distinct Matches
I have a table with three Keys. riverID (Primary), river name, and state. I want to list all the states that have rivers in them. the following query returns states multiple times, becuase there are multiple rivers in each state.
SELECT `state`
FROM `rivers`What do I do to return the states that have rivers in them ony once?

If you don't understand my question, please ask for an example. If you do understand my question and know I'm not describing properly, please help me describe it.

Sort Of Like Distinct
Is there a way to retrieve a set number of duplicate results?
For example, I have a calendar application and I want to retrieve only 4 results per day.
The data is like this:

Woo, 6
Yes, 6
Foo, 6
Bar, 6
Yay, 6

where those 5 entries are from the 6th of the month, so basically I want to limit each distinct date to 4 results.

Distinct Count
I have a script automatically create a new table at the beginning of every month, for the current month. The tables all have the same exact columns, but a different name (i.e. stats_October_2006, stats_November_2006). I did this just to keep the amount of records in one table as minimal as possible.I would like to be able to perform a count query across both tables and have the values merge, so it would be like performing the query on one large table.

This is the query I am able to run on just one of the tables: SELECT DISTINCT domain, count( * ) AS count FROM stats_October_2006 GROUP BY domain ORDER BY count DESC

However, I would like to perform the same query on multiple tables as if they were all one table.

How would I go about doing this? I have looked into LEFT JOIN but don't quite understand it.

Distinct And Group By
I have an query that gives me the availability for some bookings,
the problem is that if I don´t use group by, the property with more than one booking is repeated (should show only once, but with all bookings)
And if I use group by, then only one of the bookings shows.

$result = mysql_query ("SELECT propiedad.id_propiedad, bookings.propiedad, llegada, salida from propiedad LEFT JOIN bookings ON propiedad.id_propiedad = bookings.propiedad AND ((llegada >= '2004-12-01' AND salida <= '2004-12-31') or (llegada <= '2004-12-01' AND salida >= '2004-12-31') or (llegada BETWEEN '2004-12-01' AND '2004-12-31') or (salida BETWEEN '2004-12-01' AND '2004-12-31'))", $dbh);

I need to select all properties from table propiedad and then check if these properties have bookings for december in table bookings. Then show all properties from table propiedad and with php the cell is marked as not available if there are occupancy,
need to show everything from table bookings at the same row as from table propiedad,
hope I explained myself,

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

Distinct Non-orders
I have a list of customers need to find out how many have not ordered, each customer has it's on record in one table in another I have all their encounters orders and qoute history and in the final table I have all the ordered items
here is the query I have

select
c.customer_id
from customer
left join encounter as e
on c.customer_id = e.customer_id
left join order_item as o
on e.encouncter_id = o.encounter_id

this is where I'm drawing a blank I don't know how to ask for encounter_id that aren't in the order_item table

COUNT DISTINCT
I have a table that stores information on visits to various business locations. In the table I have the user_id and the office_id. How can I count how many distinct users have visited all locations. I have tried the following but it groups it by office ID and I just want one total figure...

SELECT COUNT(DISTINCT(User_ID)) FROM My_Table GROUP BY Office_ID;

Distinct Query
Here is the problem, I have a database which may have data that looks like this:

keyword1 title1 desc1
keyword2 title1 desc1
keyword3 title1 desc1
keyword4 title2 desc2
keyword5 title2 desc2
keyword6 title3 desc3

In other words, I have one table with a bunch of listings. I am trying to get a distinct result set, cycle through those results, then get a set of the keywords which meets each distinct result.
However, when I wrote a script to do this, it obviously left out a lot of valid results.

Group By Vs. Distinct
which is faster?
using the group by clause or a distinct statement.

Distinct Field
How can I select a distinct category and choose whatever imagethumbnail that goes with the distinct category in that row?
PHP Code:

$query = "SELECT DISTINCT subcategory, image1thumb FROM products WHERE client_id = '$client_id'";

$result = mysql_query($query);

Stress... a man-made creature?

Using Distinct In Sum Function

i keep getting 1064 : SELECT userID, COUNT ( DISTINCT failreasonid ) FROM MyTable WHERE number like '923%' and date between 2005-08-10 and 2005-08-21 GROUP BY userID

i keep getting 1064 :

SELECT
userID, COUNT ( DISTINCT failreasonid )
FROM
MyTable
WHERE
number like '923%' and
date between 2005-08-10 and 2005-08-21
GROUP BY
userID