Solved

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

Posted on 2006-11-08
19
254 Views
Last Modified: 2008-02-01
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
Comment
Question by:sarniscool
  • 10
  • 7
  • 2
19 Comments
 

Author Comment

by:sarniscool
ID: 17902148
Anyone?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17902754
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17902782
Is this what you mean:

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

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17902786
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
 
LVL 1

Expert Comment

by:Yogeshup
ID: 17904536
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
 

Author Comment

by:sarniscool
ID: 17907061
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
 

Author Comment

by:sarniscool
ID: 17907100
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17910440
Which version of SQL Server are you using?
0
 

Author Comment

by:sarniscool
ID: 17910544
MS SQL Server Express 2005 Express
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17910698
Than you may be able to use the ROW_NUMBER() function.  See here:
http://msdn2.microsoft.com/en-us/library/ms186734.aspx
0
 

Author Comment

by:sarniscool
ID: 17910853
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17910927
>>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
 
LVL 1

Expert Comment

by:Yogeshup
ID: 17912738
I think acperkins is right. His solution is what you should be following. Sorry, I was thinking in terms of SQL 2000....
0
 

Author Comment

by:sarniscool
ID: 17914490
Is there a sample query you can come up with that?  I'm not good with complex query.
0
 

Author Comment

by:sarniscool
ID: 17917215
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
 

Author Comment

by:sarniscool
ID: 17918463
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
 

Author Comment

by:sarniscool
ID: 17918469
sorry

select @name = name from tblTest where type = 'class a'
0
 

Author Comment

by:sarniscool
ID: 17950366
update?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 17952944
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now