January 18, 2005 at 2:32 pm
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
January 18, 2005 at 2:42 pm
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
January 18, 2005 at 2:51 pm
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
January 18, 2005 at 3:07 pm
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
January 18, 2005 at 3:17 pm
Thanks Steve but unfortunately this does not work. It returns "1" for every record.
Regards,
Andreas
January 18, 2005 at 3:20 pm
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
January 18, 2005 at 3:26 pm
It looks like I cannot avoid using a temp table. Therefore, I will go ahead with the proposed solutions. Thanks guys.
Regards,
Andreas
January 19, 2005 at 6:51 am
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
January 19, 2005 at 7:14 am
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]
January 19, 2005 at 7:16 am
Just found another link:
http://support.microsoft.com/support/kb/articles/q186/1/33.asp&NoWebContent=1
Wrong 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]
January 19, 2005 at 11:01 am
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
January 19, 2005 at 11:14 am
You can use the function COUNT_BIG() instead which returns a bigint.
It uses the same parameters as the count() function.
January 19, 2005 at 2:11 pm
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]
January 19, 2005 at 2:45 pm
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.
January 21, 2005 at 5:24 am
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