Hello,
I'm querying 2 fields in 1 table. Both fields have got duplicate records. I want the end result to display in 1 temporary(temp) field with no duplicates. How do I achieve this?

My code that does 90% of the work is below. The part that I don't know how to do is how to remove the duplicates from the temporary(temp) field?

Thanks for your help.

SELECT DISTINCT ap_expiry as temp FROM developer1 UNION SELECT DISTINCT licence_expiry as temp FROM developer1 ORDER BY temp DESC
    (SELECT
	`field1` as `temp`
FROM `table`)
UNION DISTINCT 
(SELECT
	`field2` as `temp`
FROM `table`)

      If I'm understanding you correctly, there shouldn't be any need for UNIONs...

      SELECT `field1`, `field2`
FROM `table`
GROUP BY `field1`, `field2`
        bogu wrote:
        (SELECT
	`field1` as `temp`
FROM `table`)
UNION DISTINCT 
(SELECT
	`field2` as `temp`
FROM `table`)

        Hi Bogu,
        I tried this but the temp table still has got duplicates in it.

        (SELECT 
 `ap_expiry` as `temp` 
FROM `developer1`) 
UNION DISTINCT 
(SELECT 
 `licence_expiry` as `temp` 
FROM `developer1`) ORDER BY `temp` DESC

        Alternative explanation in case you did not understand my problem.
        The end result of what I want to achieve is to have only 1 field called temp. In that field, I want the values from both ap_expiry and licence_expiry mixed together inside. The temp field must not contain any duplicates. The temp field must be sorted decending.

        Thanks for your kind assistance.

          I've tested and it doesn't select the duplicate values, anyway try it like this:

          (SELECT 
    `field1` as `temp` 
FROM `table`
GROUP BY `field1`) 
UNION DISTINCT 
(SELECT 
    `field2` as `temp` 
FROM `table`
GROUP BY `field2`)

          If it still get duplicate values, please attach an sql file to this thread so I can test it on your table or an equivalent table ...

          My testing table is like this:

          CREATE TABLE `table` (
  `field1` int(11) NOT NULL default '0',
  `field2` int(11) NOT NULL default '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 
-- Salvarea datelor din tabel `table`
-- 

INSERT INTO `table` VALUES (1, 2);
INSERT INTO `table` VALUES (3, 4);
INSERT INTO `table` VALUES (4, 3);
INSERT INTO `table` VALUES (2, 1);
INSERT INTO `table` VALUES (1, 5);
INSERT INTO `table` VALUES (2, 5);
INSERT INTO `table` VALUES (3, 6);

          One question, does your fields the same type?

          bradgrafelman wrote:

          If I'm understanding you correctly, there shouldn't be any need for UNIONs...

          SELECT `field1`, `field2`
FROM `table`
GROUP BY `field1`, `field2`

          This was my first answer too, but the client has the last words ... 😃

            If it still get duplicate values, please attach an sql file to this thread so I can test it on your table or an equivalent table ...

            Thank you for asking this question, bogu. I changed the field type from TEXT to VARCHAR then I used your code.

            Now everything is working fine. Problem solved thanks to you :-)

              You shouldn't have to do ANY of this. The definition of a union is that it automatically removes duplicates.

              Pro ejemplo:

              mysql> create table a (i text);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into a values ('abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into a values ('def');
Query OK, 1 row affected (0.00 sec)

mysql> create table b (i text);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into b values ('def');
Query OK, 1 row affected (0.00 sec)

mysql> select i from a union select i from b;
+------+
| i    |
+------+
| abc  |
| def  |
+------+

              I also did it this way:

              mysql> create table a (i text, j text);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into a values ('abc','def');
Query OK, 1 row affected (0.00 sec)

mysql> insert into a values ('abc','xyz');
Query OK, 1 row affected (0.00 sec)

mysql> select i from a union select j from a;
+------+
| i    |
+------+
| abc  |
| def  |
| xyz  |
+------+

                Note that if you WANT the duplicates, you use union all:

                mysql> select i from a union all select j from a;
+------+
| i    |
+------+
| abc  |
| abc  |
| def  |
| xyz  |
+------+
                  Write a Reply...