sqlquery
-
Hi Folks,
I have a small doubt.
suppose in a table there are 100 rows.
I want to retrieve only 40 th row.Could any one help me out.
Thanks,
Sandhya
-
you may want to read Jeff Modens "Tally table" article.
In sql2005 you can use ranking functions. Check BOL.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
sandhyarao49 (9/6/2008)
Hi Folks,I have a small doubt.
suppose in a table there are 100 rows.
I want to retrieve only 40 th row.Could any one help me out.
Thanks,
Sandhya
Just how do you identify the order of the rows? Basically, data stored in tables is without regard to any order... do you have an IDENTITY column or a date column to go by? Do you want them in order by Primary Key? If so, what is that primary key?
Basically, no one will be able to help you without more information. Please see and use the link in my signature line to get a better answer quicker.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Check out this blog, I believe it will give you what you are looking for:
http://cf-bill.blogspot.com/2007/01/select-specific-row-in-sql-server-2005.html
_______________________________
[font="Tahoma"]Jody Claggett
SQL Server Reporting Analyst[/font][/size] -
Yep... that'll work... just as soon as you identify what you want to order by, like I said.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Once you know what order you want the data in, you can use the Row_Number function to get the 40th row (or any other) pretty easily.
For example:
;with CTE (ID, Row) as
(select ID, row_number() over (order by Date)
from dbo.Table1)
select Table1.*
from dbo.Table1
inner join CTE
on CTE.ID = Table1.ID
where CTE.Row = 40;
That will select the 40th row based on the Date column. Modify to suit your needs and your table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Lets simplify this a little. If you only want the top 40 records... just use the following...
SELECT TOP 40 {fieldlist}
FROM Foo
Gary Johnson
Sr Database Engineer -
Heh... if only it were that simple, Gary. 🙂 Op wants the 40th row... not the top 40. Of course, this is SQL Server 2005 and TOP is programmable. An inner SELECT of the TOP x rows to skip encapsulated in a TOP 1 using the inner SELECT as a "not in" would easily do the trick... with the correct "Order By's", of course.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
That's what I get for not reading it 2 or three times I guess.
This is certainly where your tally table would work great. 🙂
Gary Johnson
Sr Database Engineer -
Heh... thanks for the confidence in the Tally table, Gary, but I don't believe it can be made to number rows for what's essentially a 1 row "paging" system. In 2k5, I believe the ROW_NUMBER over solution might be one of the quickest, but I've not tested it for this specific purpose.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
What u can do is:
select top 40 * from table_name
except
select top 39 * from table_name order by column_name desc
Remember the order by clause determines your ans...
-
...is there an issue with the "except" operator?
select top 40 * from table_nameexcept
select top 39 * from table_name order by column_name desc
The source query for me is sorting Asc, not desc so my resultset is wrong. When I add an order by to the source it throws an error.
The only way I can get this to work is with a temp table presorted.
select top 40 * into #temp from table_name order by column_name descselect top 40 * from #temp
except
select top 39 * from #temp order by column_name desc
Is this a limitation with this feature or am I missing something?
-
Order By on Except, like Union, applies to the final result, not the sub-queries.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Sandhya,
Implied in Jeff's response is the SQL "truism" that the order of rows DOES NOT MATTER. In fact, this is a fundamental principle of relational database design. So your question about retrieving the 40th row from a table is not meaningful, which is why there are a lot of comments about providing more information and about making assumptions. If you do not understand this fundamental principle, you'll need to learn more about database design before you can define the problem you're trying to solve. On the other hand, if you're just taking this principle for granted, then I apologize for the lecture - there are several solutions provided for pulling out the nth record from an ordered query result.
-Eric
[font="Tahoma"]Eric Flamm, Flamm Consulting[/font]
-
Hi SSC!
If the row you need is at number 40 when the result is in ascending order, u don't need to sort the rows in descending order 🙂
Also, using a temp table will slow down the process.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply