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
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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
 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Upgrading to SQL Server 2015 Express 2 36
Applying Roles in Common Scenarios 3 20
SQL 2012 Instance Problem 3 62
Need to replicate a Log table 4 11
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…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

730 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