MYSQL :: Distinct Result Rows

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?

How Do I Add Simular Rows In Result?
I am new to SQL but I heard that much is possible, so I dare ask if there is a solution to this:

I have a db with rows like this:

species: aa count 10 areacode 11
species: bb count 2 areacode 6
species: aa count 6 areacode 11
species: c count 7 areacode 11

now is there a query that returns this to me?

species: aa count 16 areacode 11
species: bb count 2 areacode 6
species: c count 7 areacode 11

Join Rows Into One Result
I'm trying to select letters in a word from a table with the ascii representations. I am selecting the letters successfully, in the correct order. The rows returned give me a letter in each row. I would like it to return one row with the joined word.

aka. It is returning
a
r
d
v
a
r
k

I would like
ardvark

This probably has to do with GROUP BY, but I don't know any functions to join characters into a string.

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

Here is the query:

Wanting Distinct Rows (by Only One Column), Not Sure How To Do It
I have a table `logins` with id, name, ip, mac, date

ip and mac are both tracking information for name and I'm trying to write a query that, when given a name, selects all names that person has logged in with by cross-checking their ip and mac with every record we have for that name

I have this query so far but it gives duplicates in the name field. What I'm trying to do is get it to give me each name only once and the most recent date associated with that name.

SELECT *
FROM `login_history`
WHERE `ip`
IN (

SELECT `ip`
FROM `login_history`
WHERE `name` = 'Name'
)
OR `mac`
IN (

SELECT `mac`
FROM `login_history`
WHERE `name` = 'Name'
)

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`

Distinct Semi-repeated Rows?
I've been thinking about how to make mysql distinct repeated rows when users send same data several times. The problem is that if users go back in the browser and send the data again, the unique ID and the date (using hours, mins & secs) will be different than the one before. So the magic question is... Could I distinct between repeated rows in a single query avoiding the unique ID and the DATE?

Is it clear? Example: you see two or three identical replies in this forums. Can we automatically get rid of the semi-clones?

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Show Rows With Result 0 For Aggregate Functions
have a query like this:
SELECT C.idc, C.name, count(CM.idm)
from C, CM
where C.IDC=CM.IDC and CM.idci is null and C.type='class' GROUP BY C.IDC

The result table only contains the elements of table C where the count() is >0.

How can I obtain a result table that contains all elements of table C with their count (either 0 or >0) ?

How Can I Use The DISTINCT (or Another Argument) To Choose Duplicate Rows In My DB?
I have a form where users submit their information. Unfortunately, some people tend to submit the form twice, sometimes 3 times. Is there a way I can select duplicate rows in my table?

Subquery Result Returning Many Rows As Single String
Is it possible to return multiple rows from a select statement as a single string?

Example:

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

How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set?

I would like to pass the calculated offset into the limit half of and ordered select statement.

E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23.

To do that I need to find the offset of photo with id=23 in

select id, datetime, caption from photos order by datetime;

Then I could get the result I want by doing....

select id, datetime, caption from photos order by datetime limit $offset, 5;

I've spent several hours scouring around and found some people with similar problems, but no solutions yet.

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?

Searching For Rows That Depend On Other Rows In Some Fashion...
Maybe this is a very stupid question. I'll try anayway. I'm using
MySQL 3.23.52 on RedHat 8.0.

I have a very big table (several millions of rows). Each entry
actually constitute a word in a big text. Among other fields we have
an ID for each word which represents the postition in the text. Now I
want to search for short phrases.

For example "welcome to sweden". This means i want to find all
occurences of the word "welcome" with ID x, where we have the word
"to" with ID x+1 and the word "sweden" with ID x+2. I've tried doing
this recursively with temporary tables - but a find myself hitting a
wall as i'm not allowed to refer to 2 different temporary tables in
the same query...

Resorting to another type of data strucutre, full text index etc.. is
unfortunately not an option. We only need this as an add on feature if
we can do it. We believe that indexes, data structures etc.. are near
optimal as is.

Please use my email for further conversion since I'm not a frequent
usenet reader.

Zero Rows Or One Rows Returned, Same Data And Same Query
I have a query that produces a single row (as I expect) when I run it from the mysql client (mysql 4.0.18-Max/linux, also 5.0.19-standard/OSX-intel), or from sqlgrinder (osx, uses jdbc).

When I run it inside my application (a Java app connecting via jdbc), I get zero rows from this query.

I tried it under phpmyadmin, and once again I get zero rows.

Why do I get inconsistent results? Here's the query:

Insert New Rows With Qty Values From Existing Rows
I am trying to make all my products unique in my db.
Lets say I have a row with an id, it also has all relevant details about the product and it has a quantity value of 4.
What I would like to do is take the entire row and duplicate it by the number of the quantity field.
This would result in the same product 4 times instead of one product with qty of 4.
Reason, I am going to serialise all the products so that they each have unique barcode from the id field.
I will encounter the reverse issue when running an insert statement for inputting new data, i.e. insert a new row for each item depending on its qty value??

Getting Rows That Are Related To Other Rows In The Same Table
I use a table to save a map using the following structure:

id, x, y, owner

Every occupied map filed has an owner id != 0. The owner id is = 0 for vacant fields.

now the problem:

New registered users need a vacant field on the map. Moreover the mapfields around this field need to be vacant as well! (sqrt((t1.x-t2.x)*(t1.x-t2.x)+(t1.y-t2.y)*(t1.y-t2.y)) <= 5.25)

What I need is a query that gets those fields that have vacant fields around them.

So far, all my tries to solve this problem with Joins/Suvqueries failed.

Getting The Last Result
I have following table:
product| ordered_by | date
n1 | A | 2006-01-01
n1 | A | 2006-02-01
n1 | D | 2006-03-01
n1 | B | 2006-05-01
n2 | B | 2006-01-01
n2 | C | 2006-04-01
....

As a result I only want one set per product with the latest "ordered_by".
In this example:
n1, B and
n2, C

So far I solved it within the executing program but I was wondering if there
is an efficient query to do the same.

Result Set
I have a SELECT that returns a set of data

Code:

SELECT stats_d.link_id
FROM stats_d
LEFT JOIN links ON stats_d.link_id = links.id
WHERE links.id IS NULL AND stats_d.link_id IS NOT NULL

Now, i want to update stats_d.link_id (same value for all) in the result set.

Result Set Of Ids
I'm selecting data where the id is in a list (ie. SELECT * FROM x_table WHERE id IN (3,4,7,8,9)).
Is there a way to maintain the order of the result set based on the order of the IN list?
For example, if I constructed my IN list to be (3,2,7,6,4), my result set will be returned in that order instead of (2,3,4,6,7).

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.

First Query Result Used For Second?
Is this possible / Practical?

Function1
I have a form that takes a users single field input and queries the database returning two values as result of what was entered, valueA and valueB. It then calls function2 passing it valueA and valueB.

Function2
takes valueA & valueB and queries the database again using these values within the query.

Im still learning mysql so the code is a real mess and not working.

Could someone give a simplified example of how this could / should be done?

Invert Result Set
SELECT `table1_id` FROM `table1` INNER JOIN `table2` ON `table1_id` = `table2_id` GROUP BY `table1_id`
This will return a unique list of table1_id's where there is relational data from table2.

What I want to do is invert this entire result set aka get a list of table1_id's that have NO current relation to table2.

I've done this years ago in mysql 3.23 but i'm currently having a metal blank...

Result From Insert
I want to insert a record and read the index value created in a single query.

It is not sufficient to insert a record with one query then read the last record with another query as a different user running the same application has a chance of inserting another record before the 2nd query (to find info on the latest record) has a chance to run.

Location Of A Row In Result
here's the problem let's say that I have the following table (msgs):

id, cid, text
1, 1, text1
2, 1, text2
3, 2, text3
4, 1, text4
5, 2, text5
6, 2, text6
7, 1, text7

now if i executed the following query:

SELECT id, text FROM msgs WHERE cid='1';

here's what I'll get

id, text
1, text1
2, text2
4, text4
7, text7

but here's what i want to get

counter, id, text
1, 1, text1
2, 2, text2
3, 4, text4
4, 7, text7

and then I want to know the location of the row that holds the id 4, which is 3 in this example.

1st: I have mysql 3.23 on my pc, and the new version of mysql is about 17-36 MB so it's hard for me to download it on a slow intenet connection, plus I don't know whats the version of mysql on my site server, so please suggest a solution that works for version 3.23.

2nd: I don't want to create temporary tables to find the results as many users at the same time could execute the same query, so the code might create thousends of tables at the same time.

3rd: I don't wanna use PHP code for this, as the result could return thousends of results, and I don't wanna go through loop for thousends of records.

please any ideas? oh btw I want to display only 5 records but I want to know what 5 records to display depending on the location the certain row that i'm looking for.

No Result For Zero COUNT
The following query

SELECT COUNT(Id) FROM References
WHERE refereeId='3'
GROUP BY targetId;

returns the desired result if there are several rows that contain the
specified refereeId, which is great.

But if there are no matching rows, nothing is returned, and I was
expecting a return value of zero.

Am I using COUNT wrong?

Get The Row Number From A Result Set
How can I know the row number of a result?

For example i have a query like this:
"SELECT id FROM table WHERE id = 3"

So is there a way to get the row number where id = 3? I tried using 'mysql_fetch_row()' and 'mysql_affected_rows()' but just cant get what i want.

Large Result Set
I have a question regarding working w/ an extremely large result set - or maybe better yet how I could possibly avoid it. I have a table w/ several million records and I need to loop thru and check each record against an entry in another table and if a match is found write most of the current row's fields out to a file. My problem is when I query for the first set the result set is too large and I blow my memory.

Empty Result Set
I'm running a perl script that accesses a mysql database. I'm trying to run a simple select statement that gives me an empty result set. The wierd thing is that if I simply replace the table name with another table name - the statement returns stuff. Could the one table have some sort of issue with it?

Display More Than One Result From DB
why this:

mysql_select_db($database_MARTIN, $MARTIN);
$query_CallsAssignedCount = "SELECT StaffID, count(CallID) FROM callinfo WHERE callinfo.StaffID = '$row_Leader1[StaffID]' GROUP BY StaffID ORDER BY CallID ASC";
$CallsAssignedCount = mysql_query($query_CallsAssignedCount, $MARTIN) or die(mysql_error());
$row_CallsAssignedCount = mysql_fetch_assoc($CallsAssignedCount);
$totalRows_CallsAssignedCount = mysql_num_rows($CallsAssignedCount);

only displays one result and not all StaffIDs? Its meant to list all StaffIDs and the number of calls closed by that staff member. '$row_Leader1[StaffID]' is the coloumn that lists all the client IDs on another form. This lists all StaffIDs fine, however, this SQL command (above)only displays ONE StaffIDs result.

Sql Alternate Result Set
how to do a sql select that will alternate a result set.
I have a table called account and in account I have two different seller types 'I' and 'D'. After I run a sql query I want an 'I' record to show up and then a 'D' record and then 'I', etc....

Result From Two Tables
When I do a

SELECT * FROM table1, table2 WHERE table1.id = table2.id AND table1.id = 'value' AND table2.id = 'value'

I only get results back from one table.

Join Result Set
select table1.*, table2.* from table1 left join table2 on table1.field1=table2.field1
In php i receve a result: Array ( [field1] => 1 [field2] => something else )
I want to receive result: Array ( [table1.field1] => 1 [table1.field2] => something [table2.field1] => 1 [table2.field2] => something else )
What i need to do to get the result set i want?

Trimming Result
how may i trim the string retutned by quey?
for instance, i'd like to have three first words of a sentance.

Return Result Set In SPs
how can we have a select satement in an SP?(i want to return a result set from the SP). i want to migrate from SQL Server to MySQL and i don't know what to do with these SPs.
it can be done in MySQL client, but it gives the error:"procedure foo can't return a result set in the given context MySQL"
in the application.
can i set a cursor out put parameter and return it?(just like Oracle) or it has another solution?

and another question: is MyODBC 3.51 tested with MySQL 5.x? can we call an SP from Delphi via MyODBC? (i tried to do so (using ADO an a data provider for OLEDB) but i think somewhere in passing the call statement it had put {} around "call sp()" statement so that mysql returned an error about the syntax.)

Distinct Result Rows

but I could not find it.how can I get the following query to return just one record rather than all of them...i.e., just list each 'category' and the corresponding 'cat' # rather then all the records PHP Code: SELECT category, cat FROM bus_list

but I could not find it.how can I get the following query to return just one record rather than all of them...i.e., just list each 'category' and the corresponding 'cat' # rather then all the records

PHP Code:

SELECT category, cat FROM bus_list