How to ignore column in SQL Server

Question

I have this query:

Select * 
from 
    (Select 
         * 
         ROW_NUMBER() OVER (PARTITION BY TID ORDER BY TID) AS RowNumber 
     from 
         MyTable 
     where 
         Eid = 'C1') as a 
where 
    a.RowNumber = 1

and it displays these results:

Column1  Column2    RowNumber  
------------------------------
 Value1   value2         1

I want to ignore the RowNumber column in the select statement and I don't want to list all columns in select query (100+ columns and given is just an example).

How to do this in SQL Server?

Simple, don't use select *. You should avoid that anyway and ONLY select the columns you want. — Sean Lange, Jul 12 '18 at 16:26
Try changing the `Select * from` to `Select Col1, Col2, from` — Aaron, Jul 12 '18 at 16:26
It doesn't matter that you have 100 columns, if you don't want one of them returned you simply can't use select *. There is no work around for this. But 100+ columns in a query is a bit questionable in its own. You can find the columns folder under the table in object explorer. If you drag that folder to your query window it will give you all the columns nicely formatted. — Sean Lange, Jul 12 '18 at 16:31
If you are concerned about selecting all but that column, I suppose you could work something out with Dynamic SQL to get all your column names, and append them to a varchar, ommitting the RowNumber column header and then do a select using that. — Ryan Wilson, Jul 12 '18 at 16:34
@J_Coder Since you have so many columns you want to keep and ommit only one, I believe this SO post has a few good ways to do it (https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) — Ryan Wilson, Jul 12 '18 at 16:40

Gordon Linoff · Answer 1 · 2018-07-12T16:44:44.860

3

Well, you would have to list all the columns in the outer select, if you use a subquery and row_number() to get a unique row.

An alternative method uses a correlated subquery, but requires having some unique column in the table. If you have one:

select t.*
from mytable t
where t.col = (select max(t2.col) from mytable t2 where t2.tid = t.tid and t2.eid = 'C1');

With the right indexes, this can have better performance than the row_number() version.

If you don't have a unique column, you can do:

select t.*
from (select distinct tid from mytable where eid = 'C1') tc cross apply
     (select top 1 t.*
      from mytable t
      where t.tid = tc.tid and t.eid = 'C1'
     ) t;

edited Jul 12 '18 at 16:44

answered Jul 12 '18 at 16:26

Gordon Linoff

1,242,037
58
646
786

i have more than 100 columns and edited the same in question – J_Coder Jul 12 '18 at 16:29
1

@J_Coder . . . That is why I offer solutions where you don't have to list all the columns. – Gordon Linoff Jul 12 '18 at 16:38

Ryan Wilson · Answer 2 · 2018-07-12T16:36:57.843

2

Wrap your query as a subquery and select specific columns from it like so:

SELECT x.Column1, x.Column2
FROM
(
    Select * from (Select * ROW_NUMBER() OVER (PARTITION BY TID ORDER BY TID) 
    AS RowNumber from MyTable where Eid="C1") as a where a.RowNumber=1
) AS x

OR Change your original Select to:

    Select a.[Column1], a.[Column2] 
    from 
    (
      Select * ROW_NUMBER() OVER (PARTITION BY TID ORDER BY TID) 
      AS RowNumber from MyTable where Eid="C1"
    ) as a 
    Where a.RowNumber=1

edited Jul 12 '18 at 16:36

answered Jul 12 '18 at 16:26

Ryan Wilson

10,223
2
21
40

1

You really don't need yet another subquery. Just change the first query to include the desired columns. – Sean Lange Jul 12 '18 at 16:34
@SeanLange You are correct sir. Added to my answer. – Ryan Wilson Jul 12 '18 at 16:35

Maxim Kasyanov · Answer 3 · 2018-07-12T16:37:13.890

2

Replace * from your query in clarify exactly columnd which you whant

select x.Column1, x.Column2 FROM (
    Select * from (Select * ROW_NUMBER() OVER (PARTITION BY TID ORDER BY TID) 
    AS RowNumber from MyTable where Eid="C1") as a where a.RowNumber=1) AS x

edited Jul 12 '18 at 16:37

answered Jul 12 '18 at 16:28

Maxim Kasyanov

938
5
14

How to ignore column in SQL Server

3 Answers3