February 18, 2014 at 7:58 pm
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
Thanks!
February 18, 2014 at 8:42 pm
You could use a table variable or you could take advantage of row_number()
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2014 at 10:16 pm
I would recommend against using row_number() when you are trying to use a SELECT DISTINCT query, as the row_number is evaluated before the DISTINCT meaning that whatever the DISTINCT is doing will be prevented because row_number() will be unique. If however you move the fields that you are taking the distinct over into a group by clause and then apply the row_number() window function you should have what you desire. See below:
SELECT sd.NAME
,ic.TABLE_SCHEMA
,ic.TABLE_NAME
,ROW_NUMBER() OVER (ORDER BY ic.TABLE_SCHEMA,ic.TABLE_NAME) AS [id]
FROM sys.databases sd
CROSS JOIN INFORMATION_SCHEMA.COLUMNS ic
WHERE sd.NAME = 'ODS1stage'
GROUP BY sd.NAME
,ic.TABLE_SCHEMA
,ic.TABLE_NAME
ORDER BY TABLE_SCHEMA
,TABLE_NAME
February 19, 2014 at 6:26 am
An alternative way to using group by is simply to put the selection into a subquery:
select name, TABLE_SCHEMA, TABLE_NAME,
ROW_NUMBER() over (order by TABLE_SCHEMA,TABLE_NAME) as RN
FROM (Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
) rows
order by TABLE_SCHEMA,TABLE_NAME
Tom
February 19, 2014 at 6:52 am
Are you sure the initial query is what you want? The distinct isn't necessary if the query is changed.
Not sure why you're cross joining sys.databases (all databases) to INFORMATION_SCHEMA.COLUMNS (against the connected database) and then retrieving only table names. The filter's not really relevant to anything, if you change your connected database, it'd retrieve table names that didn't relate to ODS1Stage. The cross join is joining all columns in the current database to all databases.
How about just this:
SELECT ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA, TABLE_NAME) AS ID, TABLE_CATALOG AS name,TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
February 19, 2014 at 7:10 am
All these answers worked so I thank you all. As to why I was doing the cross join... I'm new at SQL and that was the approach I thought of... As I do this more and more I'll get better.
February 19, 2014 at 8:13 am
caippers (2/19/2014)
All these answers worked so I thank you all. As to why I was doing the cross join... I'm new at SQL and that was the approach I thought of... As I do this more and more I'll get better.
Good answer.
It's good to hear that the feedback was useful too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 19, 2014 at 4:14 pm
HowardW (2/19/2014)
Are you sure the initial query is what you want? The distinct isn't necessary if the query is changed.
Are yes, I should have actually paid attention to what the code was trying to do rather than just addressing the technical problem... I'm sure the issue of using SELECT DISTINCT and window functions like row_number() will come up again though.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply