RE: Select distinct field won't return distinct value

["Blanton, Bob" <BlantonB@xxxxxxxxxxxxx>]

I'm just learning MySQL so don't know all the syntax.  There is a "LIST"
function in Sybase Adaptive Server Anywhere which would do that.  Is
there an equivalent function in MySQL?

Query:
SELECT distinct niin, list(serial_number) FROM
fmds.maintenance_equipment
group by niin
order by niin

Output:
niin		list(serial_number)
000213909	B71-11649,B71-11657,B71-11650
000473750	BAF-3750-0001,BAF-3750-0002,BAF-3750-0003
000929062	2341
001139768	2207




-----Original Message-----
From: tg-php@xxxxxxxxxxxxxxxxxxxxxx
[mailto:tg-php@xxxxxxxxxxxxxxxxxxxxxx] 
Sent: Tuesday, June 06, 2006 7:48 PM
To: php-db@xxxxxxxxxxxxx
Cc: myainab@xxxxxxxxx
Subject: Re:  Select distinct field won't return distinct value

Correct me if I'm wrong, but it sounds like you have something like
this:

123  Joe
124  Joe
125  Sue
126  John
127  Joe
128  Frank
129  Sue

And you want to output something like:

Joe  123, 124, 127
Sue  125, 129
John 126
Frank 128

But what you're getting is:

Joe 123
Joe 124
..etc

You have two ways you can solve this:

1. Do two SQL queries:

SELECT DISTINCT cus_name FROM customers

while ($result) {  // forgive the pseudo-code
  SELECT cus_id FROM customers WHERE cus_name = $result['cus_name']
  while ($result2) {
    echo $output;
  }
}

Or..

2. Collect data into an array and process 'distinctness' on output

SELECT cus_name, cus_id FROM customers

while ($result) {
  $cus_arr[$cus_name][] = $cus_id;
}

foreach ($cus_arr as $cus_name => $cus_idarr) {
  echo "$cus_name as ids:" . implode(", ", $cusidarr) . "<br>\n";
}

There may be some tricky ways in SQL to get the data the way you want
it, but ultimately it's not worth the bending over backwards for (do I
remember right that you can do it with crosstab queries?  don't even
know if MySQL will do those properly).   Easier just to do it with one
of the methods above.

Good luck!

-TG

= = = Original message = = =

I want select distinct field and return value of that field, but I have
problem which is: select distinct returns duplicate value. eg, I wan
select
distinct customer name and id from the customer table. one customer may
have
different cus_ids since cus_ids are auto increment and depend on the
purchased items. so what I want is to select distinct customer name so
that
I can print customer name and customer id once.

here is algorithm;

select distinct cus_name, cus_id from customers order by cus_name asc
While row is not empty do
echo"<a href=\"page?cus=cus_id\">costomer name</a><br />";



___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php