how to get row number? 1

[pnandrus]

now, this is probably easy, but i am just a beginner with SQL. I already searched around on mysql.com, but i found nothing. I want to know if there is a way to get the row number for a certain entry. For example, say you have this database

ID Name

234 Joe
345 Bob
678 Susan

I want to be able to find out what row number the ID = '345' falls on. Is this possible?

 

[sleipnir214]

A row-number is not an intrinsic property of a row in MySQL. Unless there is an auto-increment column in the table, there is no such thing as a row number.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[pnandrus]

thanks, i will have to add that column most likely!

 

[cLFlaVA]

You could also select all rows, then loop through to find this particular row, keeping a count of rows along the way. This may not be 'convenient', but you'll get the result. This also depends on how you're accessing your MySQL database.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[pnandrus]

Well, that would certainly work. However, i have in this case 77,000+ entries, and in some cases i will have more. I am trying to code a faster search of this data. We were doing it sequentially before, but i just got this project and now i think we will use mySQL with just an extra column to number them. I can just search for it using mySQL and return the row number. I think it would be faster that way, unless mySQL is just sequential as well(which i doubt), then the overhead of executing queries with JDBC (the connection would already be opened at that point) might make it much slower than a sequential search....

 

[Bastien]

Make the auto_number the primary key if you can, otherwise index it and it will make the search faster. PKs are indexed automatically.

Bastien

Cat, the other other white meat

 

[r937]

this may be a silly question, but what good does having the "row number" actually do?

what is the row number used for?

if you have 77,000 rows, and you search for ID=345, and you find out that it's number 45,678 in the table, then what?



rudy

http://r937.com/

SQL Consulting

 

[elck]

Well, suppose I want to edit it, or delete it, do you suggest I should search for it again with a lot of clauses, or do you think this is faster?

DELETE from DB where ID=45678

(Yes I'm an oldfashioned programmer)

 

[r937]

you should *never* delete a row based on its purported position ("row number") in a sequence of selected rows using ORDER BY

for one thing, that position may have changed since the SELECT, and for another, the syntax to do it based on position is next to impossible

far better to delete based on content

i.e. DELETE FROM tablename WHERE id=345

the row with id=345 will continue to have that value no matter how many rows come "before" or "after" it




rudy

http://r937.com/

SQL Consulting

 

[pnandrus]

the reason i want the row number is that i have a JTable which reads all 77000 entries from this table into it. I need to be able to search it. I assume doing a MySQL search would be faster than running sequentially down the line one by one in the JTable. Hence, if i return the row number i can get the space in the JTable (or at least be close to it) faster than doing a sequential search....

 

[r937]

returning the row number is doing a search

and an ORDER BY as well, because you need to sort the entire table in order to find the "row number"

remember, there is no such thing as position within a table

a relational table is not like a file

the rows can go anywhere, and they are not numbered

i advise you to give up the idea of the row number

(or else explain the other tables better, because i just don't see the need)

rudy

http://r937.com/

SQL Consulting

 

[pnandrus]

the table i am using consists of 2 columns, 'ID', and 'Description'. The data is sorted by ID. When i pass the data into the JTable it is read in from the MySQL table in ordered sequence. Therefore, if i get the row number from the mySQL table it will correspond to the row number for the JTable, right?

 

[sleipnir214]

Does your SELECT query have a ORDER BY clause which explicitly sets the order of the records to be returned? If not, you have no guaranty of the order of the rows.


As r937 has stated, there is no intrinsic ordering of records in an RDBMS table. Relational databases treat a table as a mathematical set of data, which supposes no ordering on the data. Only when fetching the data, and only when using the ORDER BY clause, can the data be considered ordered.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[ingresman]

If you use a database such as Oracle or Ingres and I would think sqlserver each row has a "hardware" key which is the unique number of the row in the table. In Oracle it is called the RID (rowid) and Ingres it's the tuple Id or TID.
The db usees it internally for example Ingres has fixed size pages (represented by the top 24 bits of the TID
) and a row number (the bottom 8 nits). Once the page is got the line number is used as a key into a table at the end of page which points to the actual data on the page.
I don't think that mysql has such a facility.
I remember using record number years ago , they were called relative keys in COBOL speak.
However time moves on and the record should be identified by key not by a surogate (unless of course the key is meant to be surogate).
I could talk for years on key design....

 

[pnandrus]

by saying the rows have no intrinsic value, i assume you mean that they can change whenever with no notification, right? the thing is that i read the data into the JTable from top to bottom and the original order is preserved. I will not make any changes to the data unless the changes are made all at once, at which time i could then readjust the line numbers. Wouldn't that work even if the line numbers are not fixed? by work, i mean wouldn't that preserve the correct line numbers and be correct?

 

[ingresman]

To be honest i'd forget the line number idea - you must be able to do it with identity columns or the keys

 

[TonyGroves]

Pnandrus -

Several responders have said here that there is no such thing as record numbers in MySQL, or any other truly relational database, and they are right. Data is not stored in tables in any particular order. It's only by specifying an ORDER BY clause in your queries that you can guarantee that your records will be sorted into some particular order before they are returned to you. If you percieve any ordering in the results from a query without an ORDER BY clause, then that is coincidental, and should not be relied on.

And if you want to update or delete one particular record, then the only way to do that is to use a WHERE condition that is restrictive enough to only match the record you want.


-----
ALTER world DROP injustice, ADD peace;

 

[r937]

and don't forget, pnandrus, since you seem to be quite concerned about sequential searches, that you should declare some indexes on the columns that you intend to search on, so that the database will do indexed searches instead

rudy

http://r937.com/

SQL Consulting

 

[pnandrus]

OK, thanks for all the help. i will reread all this stuff again and try to find a better way to do this! i appreciate your time.