Get real row number of specific row in database table

Hi,

Couldn't find much help on Google with this question.

I have a mysql table with many rows. Each row has an ID, but when rows get deleted and added, the ID doesn't match the 'real' row number anymore.

Is there a way to get a row's position in the table?

For example, row with id 101 is the 88th 'real' row in the table.

Hope i explained correctly...
How can this be achieved?
peps03Asked:
Who is Participating?
 
mankowitzConnect With a Mentor Commented:
ok, so suppose you have a bunch of rows which are added and deleted from time to time, and you want to know where the current row is in the list of rows. Start off with a very basic table:

CREATE TABLE t (
id int,
dateadded datetime )

if you want to know how many rows were added before the row with id=8

SELECT COUNT(*) FROM t WHERE dateadded<(SELECT dateadded where id=8)

if you want the total number of rows,

SELECT COUNT(*) FROM t
0
 
Gary DewrellSenior Network AdministratorCommented:
0
 
Ray PaseurCommented:
This question has a question smell -- similar to a code smell -- that indicates there may be more to the story than we can see in the question.  Why do you want to do this?  Not the technical reason, but just the business reason.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mankowitzCommented:
I agree with ray here, the actual position in the database is usually irrelevant as the database can store rows however it wants and only sorts them if you ask it to. Are you asking a question like "how many orders were placed before this one?"
0
 
peps03Author Commented:
Thanks for your reactions.

@gdewrell, yes, i saw that page. Didn't get it working / don't understand it.
Could you show me a working example?

@Ray Paseur well, each row stands for a page. Say i have 20 pages (rows), then i would like to show content A on the first 5 pages, content B on the seconds 5 pages (rows) and so on..
so i thought: if i know the total number of rows, and the 'real' row number, i know whether to display content A/b/c or d.
0
 
pcelbaCommented:
Simply said SQL data engines do not need the row number for data access optimization.

The physical record order is not important in properly designed data and you should use ORDER BY clause or ROW_NUMBER (MS SQL specific) in SQL commands to achieve the output order requested.  

xBase architecture (dBase, FoxPro) started without SQL and the physical record order was more important.

So your options are:

1) MS Visual FoxPro or other xBase clone. Each record of the fixed size has given the position in a DBF file. You may reorder records physically in the way you need. Each record is also accessible by its unique record number. Records do not need IDs but it is highly recommended to have some because data are easir to maintain...

2) Create clustered index based on the ID column. This column ensures the physical record order in a table. But I would guess the clustered index is also MS SQL specific feature.

3) Always use ORDER BY YourIDcolumn  in all queries.  This is the way SQL engine was designed for.
0
 
Ray PaseurCommented:
Hmm... Wouldn't your PHP script prepare one page at a time?  If so, you might just keep the page number in the session array.  That would let you keep track on a client-by-client basis.

Tell us a little more about your advertising strategy, please.
0
 
peps03Author Commented:
Wouldn't your PHP script prepare one page at a time?
> yes, it will.

I'm looking for a method to display content A on page 1-5, content B on page 6-10, content C on page 11-15 and so on.

Number ranges can be larger, or in percentages.

How could i achieve that? I thought getting the 'real' row number would be the easiest.
> 20 rows in table, page has id X which has real row number Y so display content C
0
 
Ray PaseurCommented:
Here's where I am confused.  Let's say you have something on "page 1" that I like, so I post a link to it on Facebook.  And lots of my friends visit "page 1" so they will see the "content A" displayed.  None of them will ever get to page 2, or page 6 or page 14, etc.

Is that OK with you?  Or would you like to have the first five visits to "page 1" show "content A" then the next five visits show "content B" etc?
0
 
Dave BaldwinFixer of ProblemsCommented:
SQL database tables do Not have a 'real row number'.  They are not organized like a spreadsheet with fixed rows.  If you delete a row, the next newly inserted row may take it's place.  The only 'guaranteed' order is by using an ORDER BY clause in your SELECT statement like this:

SELECT * FROM yourtable ORDER BY id

https://dev.mysql.com/doc/refman/5.0/en/select.html
0
 
peps03Author Commented:
@mankowitz thanks!
I'll try your method. I have a time added column in the table.
0
 
Ray PaseurCommented:
Can you please help us understand what your objective is here?  Is this an ad-rotator that is trying to distribute advertisements onto the pages?  If not, please explain your objective in plain, non-technical language -- nothing about "page numbers" or any of that -- just an explanation of the business goals for the web site.  I think if we understand the business goals we may be able to help you get a technical solution that will meet those goals.
0
 
ste5anSenior DeveloperCommented:
As the others already wrote, your descriptions are pretty ambiguous. But maybe you are simply looking for the limit clause.
0
 
peps03Author Commented:
@mankowitz thanks for the solution, it works great using the time column.

@Ray Paseur and others, yes, it is meant to show certain ads only on certain pages. Some adds need to be shown on 20% of the pages some on 60% and so on.
0
 
Ray PaseurCommented:
Ahh, now that we know this is an ad rotator, we have a better chance of helping.  Details like that greatly enhance your chances of getting a good answer.  Maybe you would want to think about posting a new question with all of the information.  This is a very well-understood process; it uses a data base because you not only have to serve the ads, you have to keep records of who saw them.
0
All Courses

From novice to tech pro — start learning today.