Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2007
    Posts
    14

    Unanswered: How to find a perticular row?

    Hi all,

    I want to select last but one row in a table. I shouldn't use pk_id of the table ,because the entries in a table are jumbled.

    I want the last but one row that as entered.

    Thanks in advance
    venkat

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi vencat

    This gets right to the nub of relational theory. Tables in an RDBMS are sets of data. Think of a Venn diagram - there is no order right? Just some "clouds" to represent sets. As such there is no such thing as last or first or last but one in a relational table. Order of insertion and physical order on disk is irrelevent. So - unless you have used some sort of timestamp for when you inserted the data (say a column with a default value of GETDATE()) or some other method of determining a logical order of the data then you cannot satisfy your requirement

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    select top 1 * from yourtable order by newid()


  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by jezemine
    select top 1 * from yourtable order by newid()

    doesn't that just grab a random row?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    May 2007
    Posts
    49
    Originally Posted by jezemine
    select top 1 * from yourtable order by newid()
    this is definitely not a correct way to select last but one row. I don't think it is not possbile if you don't have timestamp or identity or smiliar column.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    doesn't that just grab a random row?
    Quote Originally Posted by mihirclarion
    this is definitely not a correct way to select last but one row. I don't think it is not possbile if you don't have timestamp or identity or smiliar column.
    Did you guys miss the wink? And Jesse's increasing ratio of sarcy to serious posts?

    Anyway - everyone knows it is:
    Code:
    select top 1 * from yourtable order by newid() DESC
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select top 1 *
      from ( select t.*, newid() as random_number
                 from yourtable as t ) as inline_view
    order by random_number desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  TOP 1 A_SQL99_compliant_derived_table.*
    FROM --A SQL99 compliant derived table
     (SELECT *
      , rn = row_number() OVER (ORDER BY newid() DESC)
     FROM meTable) AS A_SQL99_compliant_derived_table
    WHERE rn = 1
    My - we are a witty bunch.

    I said witty
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    very nice, and very elegant, too

    but shouldn't it be An_SQL99_compliant_derived_table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    very nice, and very elegant, too
    Thanks I like to write tight, elegent code to meet the requirements.

    Quote Originally Posted by r937
    but shouldn't it be An_SQL99_compliant_derived_table
    I'm an old skooler Rudy - it is pronounced SEQUEL in the flump household.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ok, ok, i feel bad now.

    this will give you the second to last row, where "last" is determined by ordering the "item" column alphabetically.

    Code:
    declare @t table (item varchar(10))
    
    insert into @t
    select 'aaa' union all
    select 'bbb' union all
    select 'ccc' union all
    select 'ddd' union all
    select 'eee' union all
    select 'fff'
    
    select top 1 a.* from
    (select top 2 * from @t order by item desc) a
    order by item asc

  12. #12
    Join Date
    Jul 2007
    Posts
    14

    This query not fulfill my request

    Quote Originally Posted by jezemine
    select top 1 * from yourtable order by newid()


    -It is retrieving only row 1 based on the primary key of the table.I am not willing to use primary key.

    Thanks

  13. #13
    Join Date
    Jul 2007
    Posts
    14

    Thanks Guys..But.....

    Ok..Guys I tested all your posts.but no one retrieving the last but row.But some of them returning last row depending on primary key of the table ,that i can achieve easily .but i don't want to use primary key....

    when i "select * " it is retrieving always a some order (but this order is same for all times) and where the rows are jumbled.i.e showing in an order they were entered.

    I want that last but one row showing in "select * from mytable" result set..


    Thanks for great help..!!

  14. #14
    Join Date
    Jul 2007
    Posts
    14
    Quote Originally Posted by jezemine
    ok, ok, i feel bad now.

    this will give you the second to last row, where "last" is determined by ordering the "item" column alphabetically.

    Code:
    declare @t table (item varchar(10))
    
    insert into @t
    select 'aaa' union all
    select 'bbb' union all
    select 'ccc' union all
    select 'ddd' union all
    select 'eee' union all
    select 'fff'
    
    select top 1 a.* from
    (select top 2 * from @t order by item desc) a
    order by item asc
    Your query is working fine...i am getting what i want..But i can create a table variable for a existing table..and how can run your query on a existing table without insertion of new data?

    Thanks

  15. #15
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it's just an example. do you understand what the example is doing?

    just alter the query to target your table instead of @t, and alter "item" to whatever column you want to order by.

Posting Permissions

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