help with DISTINCT keyword
This is a discussion on help with DISTINCT keyword within the MySQL forums, part of the Database Server Software category; --> Hi, I'm trying to select only unique values from 3 columns (filter1,filter2,filter3) and return them in their original columns. ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-28-2008, 11:32 AM
| ||||
| ||||
 help with DISTINCT keyword Hi, I'm trying to select only unique values from 3 columns (filter1,filter2,filter3) and return them in their original columns. I've got this so far but the UNION simply lists all results under the filter1 column. How can I correct this please? SELECT DISTINCT filter1 AS filter1 FROM `home_index` UNION SELECT DISTINCT filter2 AS filter2 FROM `home_index` UNION SELECT DISTINCT filter3 AS filter3 FROM `home_index` Thanks for any help! Ciarán      |
|
02-28-2008, 11:32 AM
| |||
| |||
| Re: help with DISTINCT keyword On Sun, 10 Feb 2008 06:18:44 -0800, Ciaran wrote: > Hi, > I'm trying to select only unique values from 3 columns > (filter1,filter2,filter3) and return them in their original columns. > I've got this so far but the UNION simply lists all results under the > filter1 column. How can I correct this please? > > SELECT DISTINCT filter1 AS filter1 > FROM `home_index` > UNION > SELECT DISTINCT filter2 AS filter2 > FROM `home_index` > UNION > SELECT DISTINCT filter3 AS filter3 > FROM `home_index` > > Thanks for any help! > Ciarán Are you looking for this? SELECT DISTINCT filter1 AS filter1, '' AS filter2, '' AS filter3 FROM home_index UNION SELECT DISTINCT '' AS filter1, filter2 AS filter2, '' AS filter3 FROM home_index UNION SELECT DISTINCT '' AS filter1, '' AS filter2, filter3 AS filter3 FROM home_index -- PleegWat Remove caps to reply |
|
02-28-2008, 11:32 AM
| |||
| |||
| Re: help with DISTINCT keyword On Feb 10, 2:41 pm, PleegWat <pleegwat.REM...@CAPS.leegwater-68.nl.INVALID> wrote: > On Sun, 10 Feb 2008 06:18:44 -0800, Ciaran wrote: > > Hi, > > I'm trying to select only unique values from 3 columns > > (filter1,filter2,filter3) and return them in their original columns. > > I've got this so far but the UNION simply lists all results under the > > filter1 column. How can I correct this please? > > > SELECT DISTINCT filter1 AS filter1 > > FROM `home_index` > > UNION > > SELECT DISTINCT filter2 AS filter2 > > FROM `home_index` > > UNION > > SELECT DISTINCT filter3 AS filter3 > > FROM `home_index` > > > Thanks for any help! > > Ciarán > > Are you looking for this? > > SELECT DISTINCT filter1 AS filter1, '' AS filter2, '' AS filter3 > FROM home_index > UNION > SELECT DISTINCT '' AS filter1, filter2 AS filter2, '' AS filter3 > FROM home_index > UNION > SELECT DISTINCT '' AS filter1, '' AS filter2, filter3 AS filter3 > FROM home_index > > -- > PleegWat > Remove caps to reply Yes, Thanks PleegWat - That will do nicely. It produces empty fields but I can just filter them out with php. Thanks for the help! Ciarán |
|
02-28-2008, 11:32 AM
| |||
| |||
| Re: help with DISTINCT keyword PleegWat wrote: > On Sun, 10 Feb 2008 06:18:44 -0800, Ciaran wrote: > >> Hi, >> I'm trying to select only unique values from 3 columns >> (filter1,filter2,filter3) and return them in their original columns. >> I've got this so far but the UNION simply lists all results under the >> filter1 column. How can I correct this please? >> >> SELECT DISTINCT filter1 AS filter1 >> FROM `home_index` >> UNION >> SELECT DISTINCT filter2 AS filter2 >> FROM `home_index` >> UNION >> SELECT DISTINCT filter3 AS filter3 >> FROM `home_index` >> >> Thanks for any help! >> Ciarán > > Are you looking for this? > > SELECT DISTINCT filter1 AS filter1, '' AS filter2, '' AS filter3 > FROM home_index > UNION > SELECT DISTINCT '' AS filter1, filter2 AS filter2, '' AS filter3 > FROM home_index > UNION > SELECT DISTINCT '' AS filter1, '' AS filter2, filter3 AS filter3 > FROM home_index A "UNION" implies DISTINCT, so the DISTINCTs should not be required. |
|
02-28-2008, 11:32 AM
| ||||
| ||||
| Re: help with DISTINCT keyword On Sun, 10 Feb 2008 07:06:40 -0800, Ciaran wrote: > On Feb 10, 2:41 pm, PleegWat > <pleegwat.REM...@CAPS.leegwater-68.nl.INVALID> wrote: >> On Sun, 10 Feb 2008 06:18:44 -0800, Ciaran wrote: >> > Hi, >> > I'm trying to select only unique values from 3 columns >> > (filter1,filter2,filter3) and return them in their original columns. >> > I've got this so far but the UNION simply lists all results under the >> > filter1 column. How can I correct this please? >> >> > SELECT DISTINCT filter1 AS filter1 >> > FROM `home_index` >> > UNION >> > SELECT DISTINCT filter2 AS filter2 >> > FROM `home_index` >> > UNION >> > SELECT DISTINCT filter3 AS filter3 >> > FROM `home_index` >> >> > Thanks for any help! >> > Ciarán >> >> Are you looking for this? >> >> SELECT DISTINCT filter1 AS filter1, '' AS filter2, '' AS filter3 FROM >> home_index >> UNION >> SELECT DISTINCT '' AS filter1, filter2 AS filter2, '' AS filter3 FROM >> home_index >> UNION >> SELECT DISTINCT '' AS filter1, '' AS filter2, filter3 AS filter3 FROM >> home_index >> >> -- >> PleegWat >> Remove caps to reply > > > Yes, Thanks PleegWat - That will do nicely. It produces empty fields but > I can just filter them out with php. Thanks for the help! > Ciarán The field names will be the same for all rows. That's how it's designed and there's no getting around that. If you prefer, you can use NULL instead of '', but that won't matter much for the php side. -- PleegWat Remove caps to reply |
Linear Mode
