Member Avatar for 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
  • 2 Contributors
  • 3 Replies
  • 137 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by EddySR
Member Avatar for EddySR

bump... anyone?

Member Avatar for dickersonka

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

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.