?
Solved

Get real row number of specific row in database table

Posted on 2014-02-20
15
Medium Priority
?
668 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
[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
  • 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

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 42

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 35

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

765 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