April 17, 2008 at 6:22 am
Hi,
I had a Following simple Select Query in Sql Seevr 2000.
Select City_Name From City_Master
It return RowSet Like
CityName
-------------
Ahmedabad
Delhi
Mumbai
Banglore
but , i want like below rowset
CityName Sr. No
-----------------------------
Ahmedabad 1
Delhi 2
Mumbai 3
Banglore 4
Means Ahmedabad is at first row so i want 1, same for Delhi ,Mumbai ,Banglore Are at Second, third,four row so i want 2,3,4 respectively..
i don't want to use SELECT statement with INTO clause or tempory table.
Thanks in Advance...
April 17, 2008 at 6:33 am
April 17, 2008 at 6:40 am
Ripal,
I would suggest that you need to make a compromise in your constraints. There several major obstacles here including (1) you are using SQL 2000 instead of SQL 2005, (2) you want an unordered result set, (3) you don't want to use an INTO clause, (4) you don't want to use a temp table.
Of all of these the biggest obstacle to observing these constraints is #2 -- you want an unordered result set. Now, if you have an identity column that you have not given and in fact you are wanting to return the data in the order of the identity column then then constraint #2 can be relaxed and a "triangular join" can be used to fetch your sequence numbers. However, if your source data does not have something that you can use for ordering your data as you indicated and you really do want "unordered data" then the best solution is to relax constraint #3 and constraint #4 and use a temp table with an identity column.
There is also a distinct possibility that even if you do have a column so that you can select ordered data rather than unordered data that relaxing constraint #3 and constraint #4 and using a temp table with an identity column will provide for a more efficient solution than a "triangular join". Under conditions in which the sequence numbers can get large a "triangular join" can become a very slow running solution.
April 17, 2008 at 6:53 am
As Kent said, you could use identity to do this if you can use temporary tables. Using identity would be significantly faster on 2000 than using triangular joins. An example for how to use it:
SELECT IDENTITY( int, 1,1 ) AS SrNo,
City_Name
INTO #foo
FROM City_Master
ORDER BY City_Name
SELECT *
FROM #foo
Regards,
Andras
April 17, 2008 at 7:05 am
Given that a common mantra in SQL Server you might hear is "physical order has no meaning during inserts", I'm not sure that you will EVER be able to maintain the ordering you have unless you put the identity column in your actual table. As has been pointed out above, your options are using a temp table, creating some kind of triangular join, or adding an identity column to your existing table. The temp table will generate sequential ID's, but since the insert order is not guaranteed and you don't have a specific order, there's no assurance that the numbers will in any way match your current record order. As to the triangular join, besides being incredibly inefficient on large datasets, it also requires a specific order to work, so that also it not an option.
So - either we need to impose an order, and use the temp table, or - consider adding the Identity field to your existing table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 17, 2008 at 7:09 am
Well said. Thanks to both of you for picking me up on this. 🙂
April 21, 2008 at 12:27 am
Dear ,All
City_Master table having Primary key City_ID,
still how to get Serial No.
April 21, 2008 at 1:13 am
[font="Verdana"]
Ripal Patel (4/21/2008)
Dear ,AllCity_Master table having Primary key City_ID,
still how to get Serial No.
I think you haven't been through the URL given to you. You would have been got idea with an example how people had come over the problem.
SELECT City_Id
,(SELECT COUNT(*)
FROM City As C2
WHERE C2.City_ID <= C.City_Id) AS [Sr No]
FROM City_Master As C
ORDER BY City_ID
Try it.
Mahesh
[/font]
MH-09-AM-8694
April 21, 2008 at 2:53 am
Thanks Mahesh.
April 21, 2008 at 4:47 am
Mahesh Bote (4/21/2008)
[font="Verdana"]Ripal Patel (4/21/2008)
Dear ,AllCity_Master table having Primary key City_ID,
still how to get Serial No.
I think you haven't been through the URL given to you. You would have been got idea with an example how people had come over the problem.
SELECT City_Id
,(SELECT COUNT(*)
FROM City As C2
WHERE C2.City_ID <= C.City_Id) AS [Sr No]
FROM City_Master As C
ORDER BY City_ID
Try it.
Mahesh
[/font]
This is a perfect example for triangular joins 🙂 While this is a solution many would use, do check if it is acceptable for you for your dataset. If you do not have many rows in your table it is OK, but if you expect the number of cities to grow, you can very easily run into performance issues.
Regards,
Andras
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply