?
Solved

Get real row number of specific row in database table

Posted on 2014-02-20
15
Medium Priority
?
684 Views
Last Modified: 2014-02-21
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?
0
Comment
Question by:peps03
  • 5
  • 4
  • 2
  • +4
15 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39873776
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
 
LVL 24

Expert Comment

by:mankowitz
ID: 39873798
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 

Author Comment

by:peps03
ID: 39873819
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
 
LVL 44

Expert Comment

by:pcelba
ID: 39873820
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39873892
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
 

Author Comment

by:peps03
ID: 39873982
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39874215
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
 
LVL 24

Accepted Solution

by:
mankowitz earned 2000 total points
ID: 39874324
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
 
LVL 84

Expert Comment

by:Dave Baldwin
ID: 39874418
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
 

Author Comment

by:peps03
ID: 39874584
@mankowitz thanks!
I'll try your method. I have a time added column in the table.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39875244
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
 
LVL 38

Expert Comment

by:ste5an
ID: 39876236
As the others already wrote, your descriptions are pretty ambiguous. But maybe you are simply looking for the limit clause.
0
 

Author Closing Comment

by:peps03
ID: 39876923
@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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39877000
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

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I recently worked on a Wordpress site that utilized the popular ContactForm7 (https://contactform7.com/) plug-in that only sends an email and does not save data. The client wanted the data saved to a custom CRM database. This is my solution.
Laravel is the most sought after web development framework. It comes with ample amount of features that make it easy for developers to work around it. Know about its features in detail.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

589 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