Automatically numbering items in 'groups' in a query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Automatically numbering items in 'groups' in a query?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-09-24 : 08:43:06

Eric writes "Because of the pre-existing architecture of a table I have to deal with, I have to put together a reporting tool that can select a large number of rows from a large table and, for each one, select the Nth row that matches specified criteria.

For example, we have the following columns:


LoanID     Firstname       LastName

There may be 2 to 4 people listed per loan, and the LoanIDs aren't unique because of this. I need a query that could, for example, return the 2nd row for each LoanID found in the table, or at least return an additional numbering column I can use in a where clause:


LoanID     FirsName    Number
4          Fred        1
4          Alice       2
5          Bob         1
5          Mary        2
5          Tina        3

So, selecting the 2nd row for each loanID would return:


LoanID     FirsName    Number
4          Alice       2
5          Mary        2

"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-24 : 08:47:40

Why is Alice number 2, and not Fred?

- Jeff

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-27 : 00:38:45

since you already have the number in order

select LoanID, FirstName,LastName from tablename
where number=2

slacker
Posting Yak Master

115 Posts

Posted - 2004-09-27 : 07:01:30

A datatable cannot determine row numbers for your result set because that information is not part of the table. sql server can only act on data that is already there. If you try to create a rownumber, without using existing data you will fail ( and lose some hair. ).

Since you can only have 2-4 as you said.. couldnt the rownumbers be implemented on the client? If not then Maybe a temp table?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-27 : 07:07:36

quote:

since you already have the number in order

select LoanID, FirstName,LastName from tablename
where number=2

"Number" is a column that this person wants to create in a query, not one that already exists.

- Jeff

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-27 : 07:33:13

ok, need to have my glasses fixed!

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-09-29 : 03:11:09

try

select t1.LoanID, t1.FirstName, count(t2.FirstName)
from
t t1 join t t2 on t2.FirstName<=t1.FirstName and t2.LoanId=t1.LoanId
group by t1.LoanId, t1.FirstName

I named your table as t

but

Why is Alice number 2, and not Fred?

How you counted them?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources