如何将特定行保留为查询的第一个结果(T-SQL)?

时间:2022-09-23 01:41:51

I'm writing a SQL query to get a list of parameters for a report in Report Builder 3.0. I needed to add an extra row with the value 'All' to the results like this:

我正在编写SQL查询以获取Report Builder 3.0中报表的参数列表。我需要在结果中添加一个值为'All'的额外行,如下所示:

SELECT 'All'
UNION
SELECT DISTINCT    Manager
FROM               IS_Projects

This works fine, but the query returns the rows to me sorted in alphabetical order, where I actually want 'All' to appear at the top at all times (ie. come back as the first row). The rest of the results can be sorted alphabetically.

这工作正常,但查询返回按字母顺序排序的行,其中我实际上希望“全部”始终显示在顶部(即,作为第一行返回)。其余结果可按字母顺序排序。

I've seen suggestions on adding a sort-order column to the table, but I'm pretty new to SQL, and don't know how to do this.

我已经看到有关向表中添加排序顺序列的建议,但我对SQL很新,并且不知道如何执行此操作。

Thanks for any suggestions!

谢谢你的任何建议!

2 个解决方案

#1


21  

One way;

单程;

SELECT Name FROM (
    SELECT 'All'       as Name
    UNION 
    SELECT DISTINCT    Manager
    FROM               IS_Projects
) T
ORDER BY CASE Name WHEN 'All' THEN 0 ELSE 1 END, Name

#2


3  

This is one way:

这是一种方式:

SELECT Manager
FROM (SELECT 'All' Manager, 1 Order
      UNION ALL
      SELECT DISTINCT Manager, 2 Order
      FROM IS_Projects) A
ORDER BY Order, Manager

#1


21  

One way;

单程;

SELECT Name FROM (
    SELECT 'All'       as Name
    UNION 
    SELECT DISTINCT    Manager
    FROM               IS_Projects
) T
ORDER BY CASE Name WHEN 'All' THEN 0 ELSE 1 END, Name

#2


3  

This is one way:

这是一种方式:

SELECT Manager
FROM (SELECT 'All' Manager, 1 Order
      UNION ALL
      SELECT DISTINCT Manager, 2 Order
      FROM IS_Projects) A
ORDER BY Order, Manager