Exclude specific column from result in SQL Server

Question

Possible Duplicate:
SQL exclude a column using SELECT * [except columnA] FROM tableA?

I have following query and I want to exclude the column RowNum from the result, how can I do it ?

SELECT  *
FROM    
   (SELECT    
       ROW_NUMBER() OVER ( ORDER BY [Report].[dbo].[Reflow].ReflowID ) AS RowNum, *
    FROM      
       [Report].[dbo].[Reflow]
    WHERE     
       [Report].[dbo].[Reflow].ReflowProcessID = 2) AS RowConstrainedResult
WHERE   
   RowNum >= 100 AND RowNum < 120
ORDER BY 
   RowNum

Thanks.

score 6 · Accepted Answer · answered Feb 20 '12 at 08:59

It's considered bad practice to not specify column names in your query.

You could push the data into a #temp table, then ALTER the columns in that #temp to DROP a COLUMN, then SELECT * FROM #temp.

This would be inefficent, but it will get you the result you are asking for. By default though, it's best to get into the way of specifying all the columns you require. If someone ALTERs your initial table, even using the push #temp method above, you'll end up with different columns.

score 2 · Answer 2 · answered Feb 20 '12 at 08:58

2

Do not use * but give the field lsit you are interested in. That simple. Using a "*" is bad practice anyawy as the order is not defined.

answered Feb 20 '12 at 08:58

TomTom

61,059
10
88
148

score 0 · Answer 3 · answered Feb 20 '12 at 09:14

Because you want to order the results based on RowNum's values, you can not exclude this column from your results. You can save the result of your query in a temp table and then make another query on temp table and mention the columns that you want to show in the results(instead of select *). Such an approach will show all columns except RowNum which are ordered based on RowNum's values.

score 0 · Answer 4 · answered Feb 20 '12 at 09:49

This should work, I dont know the names of your columns so used generic names. Try not to use * its considered bad practice, makes it difficult for people to read your code.

SELECT [column1],
       [column2],
       [etcetc]

FROM (    SELECT ROW_NUMBER() OVER(ORDER BY RowConstrainedResult.RowNum) [RN],
          *

          FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY [Report].[dbo].[Reflow].ReflowID ) AS RowNum, *
                    FROM      [Report].[dbo].[Reflow]
                    WHERE     [Report].[dbo].[Reflow].ReflowProcessID = 2
                  ) AS RowConstrainedResult
          WHERE   RowNum >= 100
          AND RowNum < 120

Exclude specific column from result in SQL Server

4 Answers4