SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to remove last record?

    Hi,

    I would like to know if there is any way to easily drop the last record retrieved by an sql query?
    i.e. if I had a table with 10 or 20 records in it and I wanted to get the ID numbers, how would I just get all of them except the last one?

    Any help would be really appreciated.

    Many thanks,

    theUKdude

  2. #2
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you know the ID number of you last record then:

    SELECT * FROM table WHERE id < 89

    where 89 is the ID of your last record .... guess that's a bit obvious though, so you probably can't do that. In VBS after connecting with the usual ADO stuff I'd go:

    Code:
    objRS.MoveLast
    objRS.MovePrevious
    
    Do Until objRS.BOF
        .... whatever you want to do here ....
    Loop
    Don't know if that helps. It's not SQL but it will do exactly what you're asking for.

    G

  3. #3
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your swift reply Greg.
    I am using PHP so the above doesn't really mean a lot to me.
    And, as you say, the ID number will not be known as the database will grow. I tried using a LIMIT ( ... LIMIT 0,-2) but that didn't seem to work.

    Going to go and pull what little hair I have left out now.

    Thanks for your input.
    Much appreciated.

    theUKdude

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you can do it in two steps
    Code:
    select ID as lastone
      from yourtable
    order by ID descending
    limit 0,1
    and then
    Code:
    select foo, bar
      from yourtable
    where ID < lastone
    but realize that by the time you run the second query, lastone might no longer be the last one...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you say "drop" do you mean delete from the table or simply not select it?

    Goof
    Nathan Rutman
    A slightly offbeat creative.

  6. #6
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My apologies - bad choice of terminology.
    I mean 'to not select it'

  7. #7
    SitePoint Wizard Goof's Avatar
    Join Date
    Feb 2001
    Location
    Pittsburgh, PA
    Posts
    1,154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know about PHP, but in ASP I would logically do this:
    1) Select the records (including the last one)
    2) Load the results into an array (I'm guessing you can do this in PHP)
    3) Simply copy the array into an array 1 element smaller leaving out the last item.

    No sweat! Now whether this will be faster than double-querying the database depends on how many items you're talking about and how hard your database is getting hit. I would guess that for a smaller site it would be faster to query the database twice, but if you're talking about a few records on a busy database and a script that is processed a lot, then I would deal with the data at the scripting level instead of doubling the load on the DB.

    Hope that helps,
    Goof
    Nathan Rutman
    A slightly offbeat creative.

  8. #8
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this should work to skip the last record in PHP.

    PHP Code:
    // Do your query...

    $stop_row mysql_num_rows($r) - 1;
    $curr_row 0;

    while (
    $curr_row++ < $stop_row && $row mysql_fetch_array($r))
    {
        
    // Do whatever

    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  9. #9
    SitePoint Member
    Join Date
    Feb 2002
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guys,

    I'll have a play around and see what I come up with.

    Much appreciated.

    theUKdude

  10. #10
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jeez!! There's no PHP equivalent to RecordSet.MoveLast?! That's shocking!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •