distinct Multi Union help

[EddySR]

I've got a multi union sql statement however union distinct doesn't work the way I want it to because I just use distinct on 1 column only (link_id).

I've several ideas from the internet but I just can't get it to work.

Below is the statement.

SELECT TOP (500) link_id, link_city, link_state, link_zip, searchorder, searchtype
FROM         ((SELECT     link_id, link_city, link_state, link_zip, '0' AS searchorder, '0' AS searchtype
                         FROM         links AS S
                         WHERE     (LINK_STATE = 'NSW') AND (LINK_CITY LIKE '%surry hills%') AND (link_country = 'AU'))
                      UNION
                      (SELECT     link_id, link_city, link_state, link_zip, '1' AS searchorder, '1' AS searchtype
                       FROM         links AS S
                       WHERE     ((LINK_ZIP = '2000') OR
                                              (LINK_ZIP = '2006') OR
                                              (LINK_ZIP = '2007') OR
                                              (LINK_ZIP = '2008') OR
                                              (LINK_ZIP = '2009') OR
                                              (LINK_ZIP = '2010')))) AS searchresults
ORDER BY searchorder ASC

[EddySR]

bump... anyone?

[dickersonka]

What way are you wanting it to work, and what is it giving you now?

[EddySR]

link_id | searchorder
1 | 0
2 | 0
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1

The order is correct however Link_id should be distinct only.

So it should loo like this instead...

link_id | searchorder
1 | 0
2 | 0
3 | 1
4 | 1
5 | 1