By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,103 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

Row number in SELECT

P: n/a
Hi,

I currently have a fairly complex query that returns a variable number of
rows, sorted by various criteria. I am trying to insert the result of this
query into an additional table, along with a row number. Ideally, I would
like to be able to do this with an INSERT SELECT statement, although I'm not
sure quite how to achieve this. An example of what I'm trying to achieve
might be:

INSERT INTO tblfinishingpositions (raceid, personid, position)
SELECT raceid, personid, XXXXXXXXX
FROM tblfinishingtimes WHERE raceid = XYZ

At present, I'm just reading the rows one at a time, and issueing multiple
insert statements, but I would rather have something all server side if
possible. I have considered usign a temporary HEAP table, with an
autonumber field, but this seems like a bit of a kludge, and I would prefer
a single statement to do the whole lot.
Another problem that I have now overcome by use of a tempory table, but
would prefer to tidy is the following:

Say one had a table of cars: Manufacturer, Model, Price

How would one answer the question, what is the model of the cheapesest car
for each manufacturer. Obtaining the price is obviously very
straightforward:

SELECT MIN(price) FROM cars GROUP BY manufacturer

However, I couldn't find an easy way to find the corresponding model. In
the end, I had to insert the results into a temporary table, and join back
on manufacturer, and price (or their equivalents, which can be assumed to be
unique)
Any advice on either of the above problem would be much appreciated,

Chris Key
Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.