Rowcount Returned on Row with data??? - 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

Other Forums

MS Access

Rowcount Returned on Row with data???

Author

Topic

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-03-10 : 16:41:13

I'm not even sure what to search for on the Web... This is what I want to do:

I want to take the TOP 50 records in my query and union them with the TOP 50 records in another query. It is required that the 2 have thier RANKING.

Example:

Customer|MonthSales|MonthRANK|YeartoDateSales|YeartoDateRANK
JoeScmoe|8000000.00| 1|850000000000.00|4
JimScatz|7000000.00| 2|860000000000.00|3
JackSpra|6800000.00| 3| 6800000.00|50

So basically I want to return the rownumber that the record was in the TOP 50 Query.

Select TOP 50 Customer, YTDSALES, ROWNUMBER as RANK from TABLE

Anyone know how to return the Rownumber??

Daniel
SQL Server DBA

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-10 : 21:04:35

quote:

I'm not even sure what to search for on the Web...

Unless I'm missing something...you're going to kick yourself

Select TOP 50 Customer, YTDSALES
into #a
from TABLE
order by YTDSales DESC

alter table #a add rank int identity

select * from #a

drop table #a

Edited by - rrb on 03/10/2002 21:09:03

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-03-11 : 08:44:53

quote:

quote:

I'm not even sure what to search for on the Web...

Unless I'm missing something...you're going to kick yourself

Select TOP 50 Customer, YTDSALES
into #a
from TABLE
order by YTDSales DESC

alter table #a add rank int identity

select * from #a

drop table #a

Edited by - rrb on 03/10/2002 21:09:03

Hmmm... I was afraid there wasnt a function to give the rownum. So basically I will need to create 2 tables add an identity col and then union them together. Then toss this process into code...

Why is it that MS makes you do everything the hard way? Another example of this would be SQL Server... When you bulk load data and there is a primary key violation SQL Server quits. Access makes it easier by asking if you want to skip the 59 records or however many records that violated the key because they were duplicates.

Anyway I was hoping for an easy function and not having to create tables and go all crazy with this project but I guess thats what I'll have to do.

Thanks for the help.

Daniel
SQL Server DBA

Nazim
A custom title

1408 Posts

Posted - 2002-03-11 : 09:02:29

If you are using Sql 2000, you can do away with temp tables. by using table variables.

You can check for the Readers challenge for rank operations
http://www.sqlteam.com/item.asp?ItemID=6398.

quote:

Anyway I was hoping for an easy function and not having to create tables and go all crazy with this project but I guess thats what I'll have to do.

--------------------------------------------------------------

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2002-03-12 : 10:19:58

quote:

If you are using Sql 2000, you can do away with temp tables. by using table variables.

You can check for the Readers challenge for rank operations
http://www.sqlteam.com/item.asp?ItemID=6398.
quote:

Anyway I was hoping for an easy function and not having to create tables and go all crazy with this project but I guess thats what I'll have to do.

--------------------------------------------------------------

Thanks for the link. I'll be to rewrite the process to use that code when I get this stuff moved out of Access97.

Daniel
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources