469,934 Members | 1,895 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Row number in SELECT

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
0 1267

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by johkar | last post: by
7 posts views Thread by Shuffs | last post: by
1 post views Thread by sunilkeswani | last post: by
5 posts views Thread by Jonathan Wood | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.