0

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
2
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by EddySR
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.