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
venkatHi 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|||select top 1 * from yourtable order by newid()

;)|||select top 1 * from yourtable order by newid()

;)

doesn't that just grab a random row?|||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.|||doesn't that just grab a random row?
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:
select top 1 * from yourtable order by newid() DESC|||select top 1 *
from ( select t.*, newid() as random_number
from yourtable as t ) as inline_view
order by random_number desc|||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 :)|||very nice, and very elegant, too

but shouldn't it be An_SQL99_compliant_derived_table|||very nice, and very elegant, tooThanks :cool: I like to write tight, elegent code to meet the requirements.

but shouldn't it be An_SQL99_compliant_derived_tableI'm an old skooler Rudy - it is pronounced SEQUEL in the flump household.:)|||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.

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|||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|||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..!!|||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.

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|||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.|||-It is retrieving only row 1 based on the primary key of the table.I am not willing to use primary key.

Thanks

that was meant to be a little joke. you probably don't know much sql if you thought that might give you what you wanted. :)

you might want to spend some time learning SQL better.|||when i "select * " it is retrieving always a some order (but this order is same for all times) no it isn't

it only appears to be the same order every time

it might even actually be exactly the same order, many times in a row

but without an ORDER BY clause, there is no guarantee of any order in the results