CODEWITHSUNDEEP
sqlt-sqlssrs-2012
Frenchi In LA
Frenchi In LA

Reputation: 3169

T-SQL Add text on first row of query

I have a table tag with 2 fields ID, Text. I'd like to make a query with distinct value of Text and add 'All' On first position (it's used in a SSRS parameter value) I made the following query:

SELECT 'All' [Text] Union
Select DISTINCT [text] from tag

the problem is there are some text starting with number and came before the All Text in the query. How can I make the query in order to get All in the first Row

Upvotes: 0

Views: 1934

Answers (2)

Chris
Chris

Reputation: 36

SELECT 'All' AS [Text], 0 AS Sorter
UNION
SELECT [text], 1 AS Sorter
FROM tag
ORDER BY 2,1

If you just want the text, try:

WITH alltext AS (
SELECT 'All' AS [Text], 0 AS Sorter
UNION
SELECT [text], 1 AS Sorter
FROM tag
)
SELECT text FROM alltext ORDER BY sorter, text

Hope that helps.

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use union all to prevent ordering:

SELECT 'All' [Text] UNION ALL
(Select DISTINCT [text] from tag order by [text])

Upvotes: 0

Related Questions