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