T-SQL Add text on first row of query

Question

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

Let say there are Rows like the following: 1, 1w 2, SS 3, TT — Frenchi In LA
– Frenchi In LA, Commented Dec 15, 2016 at 18:55

Vamsi Prabhala · Accepted Answer · 2016-12-15 19:10:46Z

2

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.

edited Dec 15, 2016 at 19:10

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

answered Dec 15, 2016 at 18:54

Chris

365 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vamsi Prabhala Over a year ago

i removed distinct which isn't necessary when using union. also the order by in the first query had to be order by 2,1

Gurwinder Singh · Accepted Answer · 2016-12-15 18:59:40Z

0

You can use union all to prevent ordering:

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

edited Dec 15, 2016 at 18:59

answered Dec 15, 2016 at 18:48

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

2 Comments

Frenchi In LA Over a year ago

I don't see why ALL would fix this problem. I'd like to have 'All' In the first Row, but in the table I have some value like 1, 2 etc,and the query is ordering by value and they're coming before the 'All' Value.

Gurwinder Singh Over a year ago

amended my answer

Collectives™ on Stack Overflow

T-SQL Add text on first row of query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related