• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Is there a System Row Count Variable? Need to get data from a row when the table has no rowcount

I need to get data from a table that doesn't have a row count number.  For instance, if this was the table...


PropertyID         COMMENT             DATE
4                         A                   1-1-2005
66                       B                   1-2-2005
67                        C                   1-3-2005

This is part of a stored procedure that I'm creating.  Here is the test script.  XXXX is where I'm planning to put the System Row Count Number.

declare @comment varchar (4000)
declare @commentTemp varchar (4000)
declare @commentCount int
declare @commentRowCount int

set @commentCount = 0
set @commentRowCount = 0

select @commentCount = count (*) from tblPropertyComments where propertyID = 26

while @commentRowCount <=  @commentCount
begin
      select @commentTemp = comment from tblPropertyComments where propertyid = 26
      XXXXXX and  rowid = @commentRowCount

      set @comment = @comment + @commentTemp
      set @commentRowCount = @commentRowCount + 1
end

print @comment

I mean this is a fix for an issue I was having early when I would want to store data from a table.  But if I had multiple returning rows, it would only store the data from the last row.
0
sarniscool
Asked:
sarniscool
  • 10
  • 7
  • 2
1 Solution
 
sarniscoolAuthor Commented:
Anyone?
0
 
Anthony PerkinsCommented:
Please post a message in Community Support to have the following very old question closed:
http://www.experts-exchange.com/Web/Web_Languages/JavaScript/Q_22006141.html

See here:
Nobody answered my question. What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi71
0
 
Anthony PerkinsCommented:
Is this what you mean:

Select      (Select COUNT(*) From @YourTable Where Comment <= t.Comment) RowNumber,
      PropertyID,
      Comment,
      [Date]
From @YourTable t

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Anthony PerkinsCommented:
I had a type, let's try that again:

Select      (Select COUNT(*) From YourTable Where Comment <= t.Comment) RowNumber,
      PropertyID,
      Comment,
      [Date]
From      YourTable t

0
 
YogeshupCommented:
What I have in some cases is created a table vaiable with the fields required plus an identity column, inserted the records in the table variable and then select from this table variable. Luckily, lot of times, I had to do some computation and did not have too many rows as my output so it was very easy to justofy the table variable. I think using a co-related sub-query is OK in case of a small table but might be very time consuming if the table is large or does nto have proper indexes. you will have check it with your load-testing database.
0
 
sarniscoolAuthor Commented:
I think I would use Yogeshup solutions.  So how do I create a query and then at the same time create a new column with a row number to be outputted?  Thanks
0
 
sarniscoolAuthor Commented:
Just want to clarify, so there is no way to access a specific row in a table using a system variable?  So I can't do something like...


select * from myTable where #system_row_number = 5;

?
0
 
Anthony PerkinsCommented:
Which version of SQL Server are you using?
0
 
sarniscoolAuthor Commented:
MS SQL Server Express 2005 Express
0
 
Anthony PerkinsCommented:
Than you may be able to use the ROW_NUMBER() function.  See here:
http://msdn2.microsoft.com/en-us/library/ms186734.aspx
0
 
sarniscoolAuthor Commented:
I can't create a table so I was thinking about doing a complex query where the inner query would grab the data and assign row numbers and then the outer query would grab the 2ND row so for example...

TABLE
------------------------------------
ID NAME xID
1 A 345
2 B 122
3 C 122
4 D 124
5 E 122

The inner query would grab the rows with xID of '122' would do result in something like this

TABLE
----------------------------
ROWNUM NAME
1 B
2 C
3 E

Then the outer query can grab the second one easily by just doing
select name from ....

So I guess my question is, how to create a query and insert a rownum.
0
 
Anthony PerkinsCommented:
>>so I was thinking about doing a complex query where the inner query would grab the data and assign row numbers <<
Did you not see my solution?  That is exactly what it does.
0
 
YogeshupCommented:
I think acperkins is right. His solution is what you should be following. Sorry, I was thinking in terms of SQL 2000....
0
 
sarniscoolAuthor Commented:
Is there a sample query you can come up with that?  I'm not good with complex query.
0
 
sarniscoolAuthor Commented:
select ROW_NUMBER() OVER (ORDER BY propertyid) as "rownum", @TempComment = comment from tblPropertyComments where propertyid = @PID and rownum = @CommentLoopCount

I get this error...
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

So I'm trying to do something like this where the inner query retrieves what I want with the row number, and then using that query selecting the row I want, but this isn't working, its probably a syntax problem.

select comment from
(select ROW_NUMBER() OVER (ORDER BY propertyid) as "rownum", comment
from tblPropertyComments where propertyid = 26)
where rownum = 2

I don't know what to google, what I want I thought was called a inner/outer complex query, but that's not what they call it. And I don't think its a union, join, intersect query.
0
 
sarniscoolAuthor Commented:
If I understand this correctly, what I'm trying to do is a work around to the mySQL "GROUP_CONCAT" function, since  MS SQL doesn't have that feature.

Because I beleive that GROUP_CONCAT would have solved the issue since the originated problem was...

ID          name      type
-----------------------------
1           a             class a
2           b            class a
3          c               class b

select @name = name where type = 'class a'

Only the last set row would be stored to @name so that means only 'b' and not 'ab'
0
 
sarniscoolAuthor Commented:
sorry

select @name = name from tblTest where type = 'class a'
0
 
sarniscoolAuthor Commented:
update?
0
 
Anthony PerkinsCommented:
Let me explain to you how it works here:  When someone goes to the trouble of trying to help you out and you totally ignore them, than you should not be surprised if people to likewise to you.

Good luck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 10
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now