Fog Creek Software
Discussion Board

[DB Question]Row count value when doing a select?

Let's say I'm doing a select like:

select band from cd_collection

Let's say the results are:

* Coldplay
* Foo Fighters
* John Mayer

Now, let's say I wanted to have a number associated with it (a count), so the results look like:

*1, Coldplay
*2, Foo Fighters
*3, John Mayer

There are 3 results and "Coldplay" is result 1, "Foo Fighters" is result 2, etc.

Yes, I could just keep a count in my code.  But, I was just wondering if there was a way to do this easily in the SQL, like:

select row_count, band from cd_collection

I am using SQL Server if that helps (I did look into @@rowcount, but that just returns 3 with each record).

William Campbell
Tuesday, July 13, 2004

DECLARE @Counter int
SELECT Counter = Counter + 1 AS BandNo, band FROM cd_collection

Tuesday, July 13, 2004;EN-US;q186133

Code Monkey
Tuesday, July 13, 2004

Hi, that's really neat!, .. but smells hacky.

Just out of curiosity, does anyone know if there is any other simple way of achieving that?

.NET Developer
Tuesday, July 13, 2004

Thanks, Code Monkey read my mind.

Or coincidently posted just before me.

.NET Developer
Tuesday, July 13, 2004


It looks hacky because it is inherently hacky to return a row number with the record.  Getting the position of your record within the result set is trivially easy through code. As such it makes a lot more sense to do it there, rather than in SQL.  Also, I would expect some potentially interesting results if your statement were to contain an order by clause that wasn't supported by an index (which you shouldn't do, but which does happen).

It's a neat trick, but I would recommend keeping what belongs in code in code.

Clay Dowling
Tuesday, July 13, 2004

Uhh, Hi ... I don't think you can do that.  You would get this error message:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

William Campbell
Tuesday, July 13, 2004

Code Monkey,
now that's the type of article I was looking for.  That looks great!

The query performed is going to be on a small set of records (on average, 6-10 at a time on the Handheld ... this would be a query performed on a SQL Server CE database).  So, it's fine to use that, especially when the article says:

Because of the cross join, this is not designed for working with a large number of rows

Being that I'm working with 6-10 at a time, and no duplicates, this is the perfrect little hack for me.

William Campbell
Tuesday, July 13, 2004

Getting the position of your record within the result set is trivially easy through code. As such it makes a lot more sense to do it there, rather than in SQL

Normally, I would totally agree with you.  But, here is the reasons I was thinking of doing a little bit of hacking in SQL Server:

We have a main view we call the "AgendaView" (it's a HH app).  This lists the work orders for that day (thus, the avg. of 6-10 records).  They are displayed in an owner drawn list control.

We have a class that takes a SQL statement and creates some AgendaItem objects.  These objects are then inserted into the AgendaView's list.  The AgendaView knows nothing about what is in an AgendaItem, it just knows there's a certain interface that it expects, and knows how to draw the AgendaItem (an item could be 2 rows of data, 3 rows, 4 rows, etc.)

The class that does the AgendaItem creation, calls into a virtual function.  That virtual function is where the actual AgendaItem object is created.  It's virtual, because the AgendaItem that is created can be different depending on the project.  So, really, AgendaView ends up taking ProectAgendaItem objects.

We got a request, as a visual to the users, to number the work orders (1st one of the day gets 1, 2nd one gets 2, etc).

I didn't want to:
* have to change my virtual function to pass a number, for the count I was keeping.

*change the constructor (or add a set method) to the AgendaItem or ProjectAgendaItem class to take that number.

Just so I could add that count value.

Now, all I'd have to do is change the sql statement, then change some of the code inside the ProjectAgendaItem to work with the new field it's getting...and whoola! ... I don't have to change any interfaces to any of the current architecture.

So, there's a valid reason here! :)

William Campbell
Tuesday, July 13, 2004

Is that # of times the phrase appears in the table?

Try this:

SELECT band,

---> that's pretty standard SQL. (Formerly Matt H.)
Wednesday, July 14, 2004

No, it's not the # of times that phrase appears.

That would obviously be standard SQL.  Re-read the posts, you might have missed something.

William Campbell
Wednesday, July 14, 2004

*  Recent Topics

*  Fog Creek Home