Select All columns except one - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select All columns except one

Author

Topic

byle
Starting Member

6 Posts

Posted - 2011-07-21 : 10:06:12

Hi everyone!

I'm trying to do something which I think it can't be done, but I need a real trusted opinion to convince myself...

I'm doing the next query:

SLECT * FROM 
(
SELECT *, seq = row_number() over (partition by COL1 order by COL2)
FROM TABLE1
) T
WHERE seq = 1

And I would like to do something like

SELECT * - column(seq)  (this is pseudo code) FROM
(
SELECT *, seq = row_number() over (partition by COL1 order by COL2)
FROM TABLE1
) T
WHERE seq = 1

Any ideas to do that without getting a really complex query? I mean, the seq column doesn't really bothers me at all, but if there's an easy way to hide it would be great!

Thankyou very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 10:09:45

why not repeat other column names in select list?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

byle
Starting Member

6 Posts

Posted - 2011-07-21 : 10:13:50

Because I would like to use it for a view and have the same structure than TABLE1 even if it grows or gets any dropped column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 10:16:54

sorry didnt get that. for that you can use * itself right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

byle
Starting Member

6 Posts

Posted - 2011-07-21 : 10:22:48

This view should be just like TABLE1 filtered by the query I show you before.
I mean, if TABLE1 has 8 columns my view should have the same 8 columns plus the seq column. But if my TABLE1 gets two new columns for any reason, my view should have all TABLE1 columns plus the seq one (10+1). That means that this view will evolve the same way MYTABLE evolves without having to maintain it.
As I said the seq column doesn't really annoys me, but If I could get it out it would be better.....

byle
Starting Member

6 Posts

Posted - 2011-07-21 : 10:24:44

My question is only to know I can get my seq column out.
For example in pseudocode
SELECT ALL COLUMNS EXCEPT THE LAST
or
SELECT ALL COLUMNS EXCEPT seq COLUMN

Always with the *

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources