August 12, 2009 at 11:20 pm
I have a simple table tbl_members as follows
srno. - memid - sid
7 959 958
8 960 958
9 961 958
10 962 958
11 963 958
I want to find the row number of a record using T-SQL query where memid=961.
The result should be 3 as the record is in third row.
Please help?
August 12, 2009 at 11:48 pm
Hi,
I do not see any reason to find that, but here is thequery that can help you with what you want.
remember that for this query i am assuming that srno column is the primary key of the table and it will always be ordered by this srno column.
THe query is
select * , (select count(*) from tbl_members t2 where t2.srno <= t.srno) as RowNumber From tbl_members where memid = 961
So the LAst Column of the query result will be your answer.
:w00t:
August 13, 2009 at 8:21 am
SQL Server does not ensure ordering in storage so the third row could be any of the rows unless you provide an order by. In SQL Server 2005 and later you can use a CTE and the Row_Number() function like this:
PRINT 'Insert Data'
INSERT INTO @table
SELECT
7, 959, 958
UNION ALL
SELECT
8, 960, 958
UNION ALL
SELECT
9, 961, 958
UNION ALL
SELECT
10, 962, 958
UNION ALL
SELECT
11, 963, 958
PRINT ' Using CTE'
;WITH cteRows AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY srno) AS row_id,
*
FROM
@table
)
SELECT
*
FROM
cteRows
WHERE
memid = 961
-- vishal's solution
PRINT 'Using Correlated Sub-Query'
select
*,
(
select
count(*)
from
@table t2
where
t2.srno <= t.srno
) as RowNumber
From
@table t
where
memid = 961
If you use the SET STATISTICS IO ON command you will see that in this simple example without any indexes there are twice as many scans and reads using Vishal's solution which is an indication that it will not scale as well as the CTE/Row_Number() solution I have proposed. So you'll want to test the solutions on your system with your data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2009 at 8:34 am
Yes, I completely agree, my solution is not efficiant, and just migrated to 2005 so , i have just heard about the row_number function, Can you give me some info on how to use this function.
Thanks.
:w00t:
August 13, 2009 at 8:47 am
If you want to learn about ROW_NUMBER(), read the documentation in BOL. No sens in having someone explain it to you if you haven't tried.
ROW_NUMBER works for a result set. Not a table. There is no row number for a table, as Jack noted. Only if there is an ORDER BY can you guarantee the order of a result set, and then be sure about the answer you'll get.
However, maybe you ought to back up and think about what you are trying to accomplish. Why do you care about which record is stored in a slot? Chances are you're approaching the problem incorrectly.
August 13, 2009 at 8:48 am
http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2009 at 8:54 am
Steve,
Sorry , if it looks to you that i know nothing, but do you mean to say that row_number returns only the row number. does it do anything else also like replacing the sub queries?
:w00t:
August 13, 2009 at 9:07 am
vishal sharda (8/13/2009)
but do you mean to say that row_number returns only the row number.
It doesn't return the row number because SQL doesn't have any concept of a row's position in a table. What rows number does is assign sequential numbers to records in a query, based on the order that you specify.
As an example, say I have a query that returns 3 rows:
Name
Joe
Bob
Dave
If I add a second column, row_number over (Order by Name), then Bob will have value 1, Dave 2 and Joe 3. If instead I added the second column as row_number over (Order by Name DESC), then Joe has value 1, Dave 2 and Bob 3.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2009 at 9:11 am
But my question is i cannot find a scenario where we need to use this function, can you suggest me some case in which i can use ROW_number?
:w00t:
August 13, 2009 at 9:22 am
Exactly the scenario that the OP asked about. Finding the 3rd row ordered by some criteria.
Anther use is assigning unique values to rows in a resultset, perhaps for paging.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2009 at 9:24 am
You could use it for answering things like:
- give me the top 5 invoices for each client order by descending invoice amount
- every 10th invoice by customer, order by invoice date.
Again - the row_number() results are specific to each query, and are virtual (i.e. reorder when new data is introduced).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 13, 2009 at 9:49 am
The point is that the row number is relative to how the data is returned. There is no absolute row number.
I'm not saying you don't know anything, but rather that perhaps you're not thinking through what the need for the row number is. If you don't have a need, then that's fine. however there are scenarios, like paging, or numbering for a report, that this helps with.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply