Solved

Get real row number of specific row in database table

Posted on 2014-02-20
15
642 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 12

Expert Comment

by:Gary Dewrell
ID: 39873765
0
 
LVL 108

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
 

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 41

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 108

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 108

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 500 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 82

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 108

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 32

Expert Comment

by:Stefan Hoffmann
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 108

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now