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
