How to use Distinct for multiple columns?

Question

$q=mysql_query("SELECT DISTINCT zone, location FROM service_info WHERE city='".$_REQUEST['city']."' and sr_no=1");

i want distinct for zone as well as location

You could try that solution : [here](http://stackoverflow.com/questions/546804/select-distinct-from-multiple-fields-using-sql) — Aleks, Apr 03 '12 at 06:29
do you need `zone` and `location` in same column or in two different columns — Vikram, Apr 03 '12 at 06:35

score 1 · Answer 1 · answered Apr 04 '12 at 05:23

If you just want to retrieve a list of distinct zones and a list of distinct locations, both of them matching the condition, you should probably issue two separate queries for each, because the two lists would be unrelated to each other and thus retrieving them in one query would make no sense. It seems likely that you simply want to display the two lists side by side, and that should be done in the presentation layer, not in the database.

As an alternative, though, you might consider something like this:

SELECT
  (
    SELECT GROUP_CONCAT(DISTINCT zone)
    FROM service_info
    WHERE city='".$_REQUEST['city']."' AND sr_no=1
  ) AS zones
,
  (
    SELECT GROUP_CONCAT(DISTINCT location)
    FROM service_info
    WHERE city='".$_REQUEST['city']."' AND sr_no=1
  ) AS locations

The above query would return one row of two columns each containing a list of comma-separated items. If you would prefer the lists to look as columns instead, you could explicitly specify the line break symbol as the separator, like this:

GROUP_CONCAT(DISTINCT zone SEPARATOR '<br />')

score 0 · Answer 2 · answered Apr 03 '12 at 06:37

If you do not need the columns separately then you can do:

SELECT
    zone
FROM 
    service_info 
WHERE 
    city='".$_REQUEST['city']."' and sr_no=1
UNION
SELECT
    location 
FROM 
    service_info
WHERE
    city='".$_REQUEST['city']."' and sr_no=1

How to use Distinct for multiple columns?

2 Answers2