January 17, 2008 at 4:59 pm
I'm creating a #tmptbl table based on a query. I want to create a sort order column so I can join this temp table with another table and sort it based on the sort order of the temp table. I thought an identity column would give me this but I'm getting results for the sort_order column like the following and the data isn't being sorted in the proper order either. I was expecting to see 1,2,3,4, etc
30
308
693
694
Basically I want to create a temp table based on results from my query and I want a column in that table to tell me the sort order
SELECT IDENTITY(int,1,1) as sort_order,
a.data1,
a.data2,
a.data3,
a.data4,
b.data1,
c.data1
into #tmptbl
FROMtableA a with (nolock)
JOINtableB b with (nolock)
ON a.key = b.key
JOIN tableC c with (nolock)
on b.xx = c.xx
order by b.somecolumn
January 17, 2008 at 5:43 pm
lindasmail76 (1/17/2008)
I'm creating a #tmptbl table based on a query. I want to create a sort order column so I can join this temp table with another table and sort it based on the sort order of the temp table. I thought an identity column would give me this but I'm getting results for the sort_order column like the following and the data isn't being sorted in the proper order either. I was expecting to see 1,2,3,4, etc30
308
693
694
Data sorted in proper order.
Right according to ordering rules for the COLLATION assigned to your varchar column.
If what you really have in mind is numeric values then pass your meaning to the database: change datatype of the column to proper numeric one.
_____________
Code for TallyGenerator
January 17, 2008 at 7:10 pm
Sorry, but I don't understand your response. I have the identity column sort_order as a datatype int.
January 17, 2008 at 7:12 pm
You are sorting on a different column (b.somecolumn ) to the column that you are expecting to be ordered 1, 2, 3, 4 (which is sort_order).
Loosely, the way that SQL actually "constructs" the record set is
1. find all the records that satisfy the criteria for the query (join/where conditions)
2. build a temp version of the columns in the select list
3. Apply the sort
To achieve what you need by using a derived table within your query. Effectively, this will apply the above logic first to the derived table and then to the outer table.
SELECT IDENTITY(int,1,1) as sort_order, *
into #tmptbl
FROM (SELECT
a.data1,
a.data2,
a.data3,
a.data4,
b.data1,
c.data1
FROM tableA a with (nolock)
JOIN tableB b with (nolock)
ON a.key = b.key
JOIN tableC c with (nolock)
on b.xx = c.xx
order by b.somecolumn )
AS X
January 17, 2008 at 7:36 pm
lindasmail76 (1/17/2008)
Sorry, but I don't understand your response. I have the identity column sort_order as a datatype int.
Identity column follows the order defined by
order by b.somecolumn
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply