View Full Version : Is There A Way To Remove Similar Results From Query?

01-06-2009, 03:52 PM
For example, if I want to select all bands from my "band" table that begin with the letter 'N' - several of these bands have multiple albums in the table. To see these albums, so you know where I'm going with this, we'll do a simple SELECT:

SELECT bandName,bandAlbum FROM bands WHERE bandName LIKE 'N%' ORDER BY bandName ASC;

What I get is:

Null Factor - Calculating The Human Form
Null Factor - Human Analog
Null Factor - Purity

I want to just display the bands that begin with 'N' without the album names (just to get a list of bands in this section). To do this, I do a MySQL query:

SELECT bandName FROM bands WHERE bandName LIKE 'N%' ORDER BY bandName ASC;

Since my result "Null Factor" has 3 albums in the database, I get the result:

Null Factor
Null Factor
Null Factor

What I am aiming to do is to display each band name ONCE, even if they have multiple albums listed in the table. Is this possible? If so, how do I go about doing so?

Thanks in advance!

01-06-2009, 04:06 PM
Handle it in your front end application, see this article (http://guelphdad.wefixtech.co.uk/sqlhelp/catsubcat.shtml) for clarification.

01-06-2009, 04:10 PM
Thanks for the reply, GUELPHDAD, but I found a MUCH easier solution after about an hour of thumbing through Google results... Simply add a GROUP BY in my query:

SELECT bandName FROM bands WHERE bandName LIKE 'N%' GROUP BY bandName ORDER BY bandName ASC;

Apparently, the GROUP BY will eliminate the repeating band names... just what I was hoping for!

Thanks anyway.

01-06-2009, 06:17 PM
the reason I didn't tell you to use a GROUP BY is you seemed to be looking at getting more than one column out of your SELECT statement. I realize in the one example you only asked for band names, but if you expand on that and ask for band names and then albums then using the GROUP BY clause is incorrect.

you don't even need the GROUP BY if you are only selecting one column, use DISTINCT instead, GROUP BY is used when you want to perform an aggregate function on a group of like data (all your bands you added in March for example).

If you use the GROUP BY when selecting more than one column you can run into incorrect results.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum