Numbering Rows
Numbering Rows
Numbering Rows
Numbering Rows
Numbering Rows
Numbering Rows Numbering Rows Numbering Rows Numbering Rows Numbering Rows Numbering Rows Numbering Rows Numbering Rows
Numbering Rows Numbering Rows
Numbering Rows
Go Back  Xtreme Visual Basic Talk > > > Numbering Rows


Reply
 
Thread Tools Display Modes
  #1  
Old 04-16-2004, 02:55 PM
Mikecrosoft's Avatar
Mikecrosoft Mikecrosoft is offline
Mexican Coder
 
Join Date: Jun 2002
Location: Monterrey, N.L., Mexico
Posts: 2,793
Default Numbering Rows


Hi,

I have just a question, I'm using an Access database (2002), so I need to know if there is a command (SQL) to add a field in the table that numbering each row 1,2,3,4,5,6,7,8,9...... (RowCount).


Thanks

Mike
__________________
Mikecrosoft.NET
* If I stop to ask I will stop to learn
* Just I know that I don't know nothing
Reply With Quote
  #2  
Old 04-16-2004, 05:52 PM
MKoslof's Avatar
MKoslofNumbering Rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Not quite sure I understand. You want to add a field that automatically numbers your records? Well, for Access, an Autonumber field comes to mind .
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 04-19-2004, 08:02 AM
Mikecrosoft's Avatar
Mikecrosoft Mikecrosoft is offline
Mexican Coder
 
Join Date: Jun 2002
Location: Monterrey, N.L., Mexico
Posts: 2,793
Default

I just want to number the position of the Row, not its a phisical field on the DB, I'm asking for one command that returns a field that numbering the row position in the Table. Is there some command ??

Thanks
__________________
Mikecrosoft.NET
* If I stop to ask I will stop to learn
* Just I know that I don't know nothing
Reply With Quote
  #4  
Old 04-19-2004, 08:11 AM
MKoslof's Avatar
MKoslofNumbering Rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

This is ACCESS correct. What you are asking for is not really SQL related. Well it is, but Oracle has some row position properties and there are some properties you can get out of SQL Server as well. For Access, I don't know of any way to return the current row in the recordset. Now, in VB 6 you could have looked at the .AbsolutePosition property of the recordset object. This probably carries into .Net as well. Many of the old ADO legacy commands are available in .Net.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #5  
Old 04-19-2004, 08:19 AM
Mikecrosoft's Avatar
Mikecrosoft Mikecrosoft is offline
Mexican Coder
 
Join Date: Jun 2002
Location: Monterrey, N.L., Mexico
Posts: 2,793
Default

So, theres not a way to get this position as a Field in the Result of the SQL string, becuase I need it of that way , well, I'll need to change my report completly, this question it was to avoid to change the report logic. Ok, no problem.

Thank you very much, thank for your time MKoslof


Mike
__________________
Mikecrosoft.NET
* If I stop to ask I will stop to learn
* Just I know that I don't know nothing
Reply With Quote
  #6  
Old 04-19-2004, 08:30 AM
MKoslof's Avatar
MKoslofNumbering Rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

So, theres not a way to get this position as a Field in the Result of the SQL string

To Clarify, if I have this query:

Code:
"SELECT field1, field2 FROM myTable WHERE field1 = 'mkoslof'"

You want to know the row position of the returned values (field1, field2) on myTable? Is that correct?

May I ask why you need this? So if I have a table of 50,000 records and this query returns one record, you want to know its position? (such as row number 12,546?).

Again, with MS Access I don't think you can get away with this..if I understand correctly.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #7  
Old 04-19-2004, 09:13 AM
Mikecrosoft's Avatar
Mikecrosoft Mikecrosoft is offline
Mexican Coder
 
Join Date: Jun 2002
Location: Monterrey, N.L., Mexico
Posts: 2,793
Default

I want something like this:

1 Mike 23
2 Elisa 27
3 Alicia 23
4 Josh 18
5 Chris 21

^
This field I need, where the row position is there, but this is not a field on the DB, i want as an "agregate" function.

Thanks.
__________________
Mikecrosoft.NET
* If I stop to ask I will stop to learn
* Just I know that I don't know nothing
Reply With Quote
  #8  
Old 04-19-2004, 09:43 AM
Mikecrosoft's Avatar
Mikecrosoft Mikecrosoft is offline
Mexican Coder
 
Join Date: Jun 2002
Location: Monterrey, N.L., Mexico
Posts: 2,793
Default

Ohh !, one more thing you asked me:


I need this because I'm using the Datasource of a SpreadSheet to generating a report, where the user choose the fields that will be added to the DataTable, but one field is not really a field of the database, its a row counter (its Consecutive number), but, the user can choose the place (column index) where add it, even add one more than 1 time. thats the use of this.
__________________
Mikecrosoft.NET
* If I stop to ask I will stop to learn
* Just I know that I don't know nothing
Reply With Quote
  #9  
Old 04-19-2004, 01:42 PM
MKoslof's Avatar
MKoslofNumbering Rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

OK, well the *easiest* way is to add an autoNumber field to your table..then you can just bring in this autonumber field, so your query would return this incrementing number for you.

Now, this query will work ONLY if you are sorting by a column(s) that is
guaranteed to be unique and non-null. And, it may be a little bit slow:

Code:
SELECT t.field1, t.field2, (select count(*) as RowNo from myTable where field1 <=T.field1)
FROM myTable AS t
ORDER BY field1
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #10  
Old 04-19-2004, 02:04 PM
Mikecrosoft's Avatar
Mikecrosoft Mikecrosoft is offline
Mexican Coder
 
Join Date: Jun 2002
Location: Monterrey, N.L., Mexico
Posts: 2,793
Default

Thats a good string, but will not works to me, because the users can sort the data as they like, and the number ID field will not be sorted Ascending or Descending, because other fields can do a different sort (even randomly) to this field. , However I appreciated yuor help, really thank for taking time with my problem


Mike
__________________
Mikecrosoft.NET
* If I stop to ask I will stop to learn
* Just I know that I don't know nothing
Reply With Quote
  #11  
Old 04-19-2004, 02:25 PM
MKoslof's Avatar
MKoslofNumbering Rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Yeah, well, I can't think of any other way to do it. . If this is going to be numbered and sorted on the fly. I'd say you could get away with this in Oracle...but Access, nothing else is coming to mind
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #12  
Old 04-19-2004, 02:49 PM
reboot's Avatar
rebootNumbering Rows reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

Get your initial recordset from the db, then build another recordset by hand, and loop through the initial recordset and number the items as you add them.
Reply With Quote
  #13  
Old 04-19-2004, 03:23 PM
MKoslof's Avatar
MKoslofNumbering Rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Actually, let me take back my comments. The main issue is, there is no rownum property in Access. However, let me play with this concept. I bet with a sub query I can get this (I need a challenge for today). Basically, with a sub query you need to count the record prior and increment. Do you have any sort of primary key on your table.

If I don't get back to you by the end of the night (I will try this strategy at home) you can always try to build a new table and loop as Reboot has suggested
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #14  
Old 04-19-2004, 03:31 PM
Mikecrosoft's Avatar
Mikecrosoft Mikecrosoft is offline
Mexican Coder
 
Join Date: Jun 2002
Location: Monterrey, N.L., Mexico
Posts: 2,793
Default

I saw my program, but due the logic of my program I cant do a new datatable, I need to create it at one, Really this is turning complicated for me, I'm trying to take a new ways, but also this way in particular its intrigating for me.

Thanks for your answers.
__________________
Mikecrosoft.NET
* If I stop to ask I will stop to learn
* Just I know that I don't know nothing
Reply With Quote
  #15  
Old 04-19-2004, 03:54 PM
reboot's Avatar
rebootNumbering Rows reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

Here's what I'm talking about
Code:
'assuming you have your recordset from the db, we'll call it rsDb, now 'we'll make another rs for you to display with the number Dim recordNumber As Long Dim rsRpt As ADODB.Recordset Set rsRpt As New ADODB.Recordset With rsRpt .Fields.Append "Number", adBigInt .Fields.Append "Name", adBSTR 'or whatever the fields in rsDb really are .Fields.Append "Age", adInteger .Open End With recordNumber = 1 Do Until rsDb.EOF rsRpt.AddNew rsRpt!Number = recordNumber rsRpt!Name = rsDb!Name rsRpt!Age = rsDb!Age rsDb.MoveNext recordNumber = recordNumber + 1 Loop 'now you have rsRpt, which is identical to rsDb, except numbered sequentially
Edit: OH SHOOT... I just realized I'm in .Net forum.... sorry... you could use the same principle with a Dataset. I don't have time right now to spend revising this code though. About to go home. Sorry. Might do it later tonight if you need it.

Last edited by reboot; 04-19-2004 at 04:00 PM.
Reply With Quote
  #16  
Old 04-19-2004, 04:27 PM
Mikecrosoft's Avatar
Mikecrosoft Mikecrosoft is offline
Mexican Coder
 
Join Date: Jun 2002
Location: Monterrey, N.L., Mexico
Posts: 2,793
Default

Good one, Thanks, no problem, I can convert this.
__________________
Mikecrosoft.NET
* If I stop to ask I will stop to learn
* Just I know that I don't know nothing
Reply With Quote
  #17  
Old 04-19-2004, 07:13 PM
MKoslof's Avatar
MKoslofNumbering Rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Well, if you have any unique field value...I can get it to work. However, if you have no primary key or unique value..this won't be exact. But with a table that has 200 values, all with a unique key, I get 1-200 as my aggregrate rownum:

SELECT t.Field1, t.Field2, (SELECT COUNT(*) FROM myTable AS t2
WHERE t2.field1 <= t.field1) AS RowNum
FROM myTable AS t
ORDER BY t.field1

Now, if you don't have any unique field to go by, I can get this to work with two queries..one that sorts the data, and another that creates the rowNum field.

Looks like building a new table is your best route..unless you want to use two queries for this.....
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown

Last edited by MKoslof; 04-19-2004 at 07:20 PM.
Reply With Quote
  #18  
Old 04-19-2004, 08:14 PM
reboot's Avatar
rebootNumbering Rows reboot is offline
Keeper of foo

Retired Moderator
* Guru *
 
Join Date: Nov 2001
Location: Graceland
Posts: 15,614
Default

Oh... I like that better than my solution.
Reply With Quote
  #19  
Old 04-20-2004, 08:24 AM
Mikecrosoft's Avatar
Mikecrosoft Mikecrosoft is offline
Mexican Coder
 
Join Date: Jun 2002
Location: Monterrey, N.L., Mexico
Posts: 2,793
Default

This works great, but, this procedure assigns a unique number to a row depending of the ID field, but I don't want that, that I need is to number in which position is the row when I do a query, I mean, the number of a specified row in different queries will be different. there is a very complex challenge

Thanks for the ideas its really useful to know these ones because we don't know when we can use it

Mike
__________________
Mikecrosoft.NET
* If I stop to ask I will stop to learn
* Just I know that I don't know nothing
Reply With Quote
  #20  
Old 04-20-2004, 08:28 AM
MKoslof's Avatar
MKoslofNumbering Rows MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Huh? You've lost me again. So you don't want to number your results such as:


1 Blah Blah Blah
2 Blah Blah Blah
3 Blah Blah Blah

If you are not numbering the results (such as my query returns 400 records, I get 1-400 as a rownum aggregate) how do you want to number this? You need some systematic numbering. You can't just randomly throw numbers in here.

Again, this goes back to my initial thought. Is THIS what you want. Say I have two tables. My query returns 6 records, three from each table. Now, I want to get the rownum of each record on its parents table..like so:

14 Blah Blah Blah (record 14 on Table 2)
23 Blah Blah Blah (record 23 on Table 1)
37 Blah blah blah (record 37 on Table 2)
44 Blah Blah Blah (record 44 on Table 1)
55 Blah Blah Blah (record 55 on Table 1)
57 Blah Blah Blah (record 57 on Table 2)

Is this what you want? (In a perfect world, I must add )
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Select and delete last 15 rows zreclay Excel 1 03-18-2004 09:16 PM
How do I highlight multiple rows in flexgrid randomly berdy General 4 09-09-2003 03:58 AM
Indefinite Rows bobba buoy Excel 6 07-07-2003 02:24 PM
MSHFLEXGRID adding rows + FOCUSRECT Maximus General 2 02-05-2003 07:44 AM
Setting # of rows in a MSFlexgrid during runtime Wargasm General 4 08-23-2001 02:29 PM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Numbering Rows
Numbering Rows
Numbering Rows Numbering Rows
Numbering Rows
Numbering Rows
Numbering Rows Numbering Rows Numbering Rows Numbering Rows Numbering Rows Numbering Rows Numbering Rows
Numbering Rows
Numbering Rows
 
Numbering Rows
Numbering Rows
 
-->