Member Avatar for hwoarang69

I need some help writing a sql query. I have below 2 queries and I want to combine them together.

Now I already tried union but it doesn't work. I first want to print all result of query1 than I want to print all results for query2.

Ex:

--Query#1 results:

|----|------|
| id | obj  |
|----|------|
| 1  | car  |
| 2  | tree |
|----|------|

--Query#1 results:

|----|------|
| id | obj  |
|----|------|
| 1  | bob  |
|----|------|

Than I want to comibine by printing all result for query1 than print all result for query2 like this below:

--combine result:

|----|------|
| id | obj  |
|----|------|
| 1  | car  |
| 2  | tree |
| 1  | bob  |
|----|------|

--Query#1

SELECT DISTINCT a.[rank], b.Description
    FROM CONTAINSTABLE(myTable, *, '"ra*'") AS a
        INNER JOIN myTable AS b ON a[key] = b.ID
             ORDER BY a.rank desc;

--Query#2

SELECT DISTINCT a.[rank], b.Description
    FROM FREETEXTTABLE(myTable, *, '"ra'") AS a
        INNER JOIN myTable AS b ON a[key] = b.ID
             ORDER BY a.rank desc;
Edited by hwoarang69
  • 2 Contributors
  • 1 Reply
  • 247 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by rch1231

Dani AI

Generated

Short answer: add a small "source" column and use UNION ALL (not UNION), then put a single ORDER BY at the end so rows from query1 appear before query2. UNION removes duplicates and SQL Server only accepts ORDER BY for the final result set — putting ORDER BY inside each SELECT (as suggested) is not valid T‑SQL unless you use TOP/ROW_NUMBER tricks. , if you want the two sets shown one after the other (including duplicates like your example), UNION ALL is the right tool.

-- show all rows from the first fulltext match, then all rows from the second
SELECT ct.[RANK] AS Relevance, t.Description, 1 AS QuerySource
FROM CONTAINSTABLE(myTable, Description, 'ra*') AS ct
JOIN myTable AS t ON ct.[KEY] = t.ID

UNION ALL

SELECT ft.[RANK] AS Relevance, t.Description, 2 AS QuerySource
FROM FREETEXTTABLE(myTable, Description, 'ra') AS ft
JOIN myTable AS t ON ft.[KEY] = t.ID

ORDER BY QuerySource, Relevance DESC;

Notes and troubleshooting:

  • Use UNION ALL to preserve rows present in both sets; use UNION only if you want duplicates removed.
  • If you need to keep the exact per-query ordering beyond the ranking column, generate ROW_NUMBER() OVER(ORDER BY Relevance DESC) in each part and ORDER BY QuerySource, that_rownum.
  • Be careful with the names KEY and RANK (returned by fulltext helpers); alias them to readable names to avoid confusion.
  • If you want a limited top‑N from each query while preserving their internal ordering, use TOP N ... ORDER BY inside each SELECT (TOP permits ORDER BY), then UNION ALL and apply the final ORDER BY.
    This approach enforces the "query1 then query2" display and avoids the common UNION/ORDER BY pitfalls.
Member Avatar for rch1231

Hello,

I am guessing that you left the semicolons in place when you ran the union....

This should work:

(SELECT DISTINCT a.[rank], b.Description
    FROM CONTAINSTABLE(myTable, *, '"ra*'") AS a
        INNER JOIN myTable AS b ON a[key] = b.ID
             ORDER BY a.rank desc
)
UNION
(SELECT DISTINCT a.[rank], b.Description
    FROM FREETEXTTABLE(myTable, *, '"ra'") AS a
        INNER JOIN myTable AS b ON a[key] = b.ID
             ORDER BY a.rank desc
)
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.