Numbering of Records
-
Dear All,
How can I get my returned records numbered from a simple SQL statement?
Table A:
Field 1 Field 2 Field 3
===== ===== =====
Val 1a Val 2a Val 3a
Val 1b Val 2b Val 3b
Val 1c Val 2c Val 3c
What is the query that will give me the following result - without sending this data in a temp table?
1. Val 1a Val 2a Val 3a
2. Val 1b Val 2b Val 3b
3. Val 1c Val 2c Val 3c
Thanks & Regards
Andreas
-
Can you live with a table variable???
DECLARE @tempTableVariable TABLE (IDENT int IDENTITY(1,1), col1 varchar(10), col2 varchar(10), col3 varchar(10))
INSERT INTO @tempTableVariable
SELECT * FROM SourceTable
select * from @tempTableVariable
Yields:
1 Val 1a Val 2a Val 3a
2 Val 1b Val 2b Val 3b
3 Val 1c Val 2c Val 3c
Ryan
-
Dear Ryan,
Thanks for your reply but this does not really help me as I am trying to get the mentioned result from a single statement.
Regards,
Andreas
-
Try this...
SELECT count(*) RecNum,
a.LastName
FROM Northwind.dbo.Employees a join
Northwind.dbo.Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName
Steve
-
Thanks Steve but unfortunately this does not work. It returns "1" for every record.
Regards,
Andreas
-
Andreas, Steve's solution is correct (and clever):
Based on your example:
Create TABLE temptable (col1 varchar(10), col2 varchar(10), col3 varchar(10))
GO
INSERT INTO temptable values('Val 1a', 'Val 2a', 'Val 3a')
INSERT INTO temptable values('Val 1b', 'Val 2b', 'Val 3b')
INSERT INTO temptable values('Val 1c', 'Val 2c', 'Val 3c')
GO
________________________________________________
Your SQL would be:
SELECT count(*) RecNum, a.*
FROM tempTable a
inner join tempTable b on a.col1 >= b.col1
group by a.col1, a.col2, a.col3
order by a.col1, a.col2, a.col3
Good Job Steve.
Ryan
-
It looks like I cannot avoid using a temp table. Therefore, I will go ahead with the proposed solutions. Thanks guys.
Regards,
Andreas
-
Andreas, you can avoid a temp table - that is what Steve's solution was for. Post your table name and properties if need be and we can post the exact SQL.
Ryan
-
If I understand your requirements right, Steve's solution surely works. However, I think on larger tables the use of a temp table might yield better performance.
Actually in the end this is presentational stuff, which should be handled at the client.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Just found another link:
http://support.microsoft.com/support/kb/articles/q186/1/33.asp&NoWebContent=1Wrong link. Sorry:
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This may be moot because using a temp table for a large count set is desirable, but what if the record count exceeded 2,147,483,647 (the count function returns and integer), do you have any other option?
Ryan
-
You can use the function COUNT_BIG() instead which returns a bigint.
It uses the same parameters as the count() function.
-
If the row count of *any* query exceeds the range of an INT, you'll have other more serious problems than row numbering. I would start with adding a WHERE clause.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hehe... that too
.One might also consider buying more ram and cpu power to handle such a query without a where clause to avoid those unspoken problems.
-
Hi,
Use this
select
rank() over (order by empname) as Indexnumber,* from emp
this works fine in YUKON
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply