CODEWITHSUNDEEP
sqlsql-serversql-server-2008
J_Coder
J_Coder

Reputation: 737

How to ignore column in SQL Server

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?

Upvotes: 0

Views: 2913

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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;

Upvotes: 3

Maxim Kasyanov
Maxim Kasyanov

Reputation: 1058

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

Upvotes: 2

Ryan Wilson
Ryan Wilson

Reputation: 10765

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

Upvotes: 2

Related Questions