Please post the structure of your orders table. (SHOW CREATE TABLE orders). I need to know what you are using as a primary key in order to help you to uniquely identify each duplicated row.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Paul Fine" <[EMAIL PROTECTED]> wrote on 09/30/2004 02:22:44 PM: > I am trying to come up with a query that shows duplicate last names and the > "order numbers" for each occurance of. > > I can get as far as determining the duplicates but my query result only > outputs one order for each. > > Here is my current query, an example of the results and an example of the > results I want. > > P.S. This is just an example, looking at duplicate last names is seldom of > any practical value! > > Thanks for any help! > > customer_last_name order_number > +-------------------+-------------+ > +smith + 1 + > +smith + 2 + > +smith + 3 + > +---------------------------------+ > > > > SELECT customer_last_name, order_number, COUNT(customer_last_name) AS > duplicate_customer_last_names > > FROM orders > > GROUP BY customer_last_name HAVING (duplicate_customer_last_names > 1) > > > > Result: > > customer_last_name order_number duplicate_customer_last_names > +-------------------+-------------+---------------------------------+ > +smith + 1 + 3 + > +---------------------------------+---------------------------------+ > > > > > Desired Result: > > customer_last_name order_number duplicate_customer_last_names > +-------------------+-------------+---------------------------------+ > +smith + 1 + 3 + > +smith + 2 + 3 + > +smith + 3 + 3 + > +---------------------------------+---------------------------------+ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >