> on a particular column, I want to use the value from the first row in
> the set. (I would make sure that rows get inserted in the order I of
> priority.) I thought of using TOP 1 somehow but cannot figure out how

A table has no inherent ordering so you will have to add a column to
identify the sequence. Here's an example using Seq_No as a sequence number:

CREATE TABLE Stocks (symbol CHAR(4) NOT NULL, xidentity CHAR(9) NULL,
idsource CHAR(5) NULL, exchange CHAR(4) NULL, type CHAR(5) NULL, subtype
CHAR(6) NULL, xname CHAR(20) NULL, seq_no INTEGER NOT NULL UNIQUE /* PRIMARY
KEY ??? */)

I guess that you actually want the to take the value from the first row
which has a *populated* value for the column:

SELECT S.symbol,
COALESCE(T.xidentity,S.xidentity), COALESCE(T.idsource,S.idsource),
COALESCE(T.exchange,S.exchange), M.type,
COALESCE(T.subtype,S.subtype), COALESCE(T.xname,S.xname)
FROM Stocks AS S
JOIN
(SELECT symbol,
CASE COUNT(DISTINCT NULLIF(xidentity,''))
WHEN 1 THEN MAX(xidentity) END,
CASE COUNT(DISTINCT NULLIF(idsource,''))
WHEN 1 THEN MAX(idsource) END,
CASE COUNT(DISTINCT NULLIF(exchange,''))
WHEN 1 THEN MAX(exchange) END,
CASE COUNT(DISTINCT NULLIF(subtype,''))
WHEN 1 THEN MAX(subtype) END,
CASE COUNT(DISTINCT NULLIF(xname,''))
WHEN 1 THEN MAX(xname) END,
MIN(CASE WHEN type>'' THEN seq_no END)
FROM Stocks
GROUP BY symbol) AS T
(symbol, xidentity, idsource, exchange, subtype, xname, seq_no)
ON S.symbol = T.symbol
LEFT JOIN Stocks AS M
ON T.seq_no = M.seq_no

--
David Portas
------------
Please reply only to the newsgroup
--