0

In this below query, I don't want to take the temporary column (RowNum) details.

SELECT * 
FROM   (SELECT Row_number() 
                 OVER ( 
                   ORDER BY column_name) AS RowNum, 
               * 
        FROM   table_name) Temp 
WHERE  ( rownum%2 ) = 0

Help me getting the query..

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
Gaurav Kumar
  • 1
  • 1

2 Answers2

2

Would have been much handy if there was an actual syntax to do it, but there is an alternative way to do it, it involves a TEMP table, the solution looks something like--

// Copy all (*) into temp table
SELECT * INTO TEMP_TABLE
FROM TARGET_TABLE

// Drop columns to be excluded
ALTER TABLE TEMP_TABLE
DROP C1,C2,C3 TARGET_TABLE

// Retrieve your intended data 
SELECT * FROM TEMP_TABLE

Its inefficient but might prove to be useful if you have large number of columns and very few to eliminate.

RicoRicochet
  • 2,249
  • 9
  • 28
  • 53
  • You would create another table just to prevent writing the column names? Just drag the columns folder to the query window and remove unwanted columns: http://sqlblog.com/files/folders/17507/download.aspx – Tim Schmelter Dec 17 '14 at 11:55
  • 1
    i told you, its inefficient. but if the situation mandates one can have small custom built widgets ready to do the handy work. creating a replica of a table is easy enough, then one can use it as a temporary template and do the eliminations there. and what if someone does not have access to gui tools, like if they are using an sqlite ??? – RicoRicochet Dec 17 '14 at 11:57
  • It's not only inefficient, if the table is large it's useless. – Tim Schmelter Dec 17 '14 at 11:59
  • indeed, it is. a similar debate in a same question like this is found here, http://stackoverflow.com/questions/3650689/sql-exclude-a-column-from-select-query rather interesting outlooks to the situation. – RicoRicochet Dec 17 '14 at 12:00
  • Actual Table has these there **Columns** _MobileNumber, EmpId, EmpName_. To arrange these data serially, have used Row_Number function by this query SELECT ROW_NUMBER() OVER (ORDER BY MobileNumber) AS RowNum, * FROM Employee. The Output would be having _MobileNumber, EmpId, EmpName, **RowNum** (newly temp column)_. Goal is getting the alternate row (Even), hence using this query Select * from (SELECT ROW_NUMBER() OVER (ORDER BY MobileNumber) AS RowNum, * FROM Employee) Temp Where (RowNum%2) = 0 Now, in the final OutPut I don't want to print the 'RowNum' Column. – Gaurav Kumar Dec 17 '14 at 12:14
1

Then don't use * but specify all columns explicitly which is best practices anyway:

SELECT Col1, Col2, Col3, ColXYZ ... 
FROM   (SELECT Row_number() 
                 OVER ( 
                   ORDER BY column_name) AS RowNum, 
               * 
        FROM   table_name) Temp 
WHERE  ( rownum%2 ) = 0

Bad habits to kick : using SELECT * / omitting the column list

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • If the Table contains many columns(50) or more, then using specific columns in the select clause will be quite tedious. Thereby, thought of getting some help, if there is some other way rather than mentioning all desired column in the 'Select' clause.. – Gaurav Kumar Dec 17 '14 at 11:51
  • @GauravKumar: read the article i've linked. There are ways to make it easier like using third party tools or drag&drop the columns from SSMS. Then you only need to remove the columns from the columns list that you don't want. http://sqlblog.com/files/folders/17507/download.aspx – Tim Schmelter Dec 17 '14 at 11:53
  • I would have used this drag and drop as explained in the link that you have referred, however the query I have posted, there **RowNum** seems to be a temporary column which can't be drag and drop from the table schema and then omitted the desired one. – Gaurav Kumar Dec 17 '14 at 12:00
  • @GauravKumar: then drag the columns from the table with the most columns. On the other answer you mention that the table has only `MobileNumber, EmpId, EmpName` (or you only want to return these columns). Then i don't understand the problem. – Tim Schmelter Dec 17 '14 at 12:31