sql server - sql union remove "semi-duplicates" -

i'm doing union so

select name, price products project = 10  // prio 1 union select name, price products customer = 5  // prio 2 union select name, price products standard = 9  // prio 3 

edit: changed where-clause make bit more complicated

this typically give me back

+-----+------+-----------+--------------+ |(no) | name |  price    |  (prio)      | +-----+------+-----------+--------------+ |  1  |    |    10     |  (1)         | |  2  |  b   |     5     |  (1)         |  |  3  |    |    13     |  (2)         | |  4  |  b   |     2     |  (2)         |  |  5  |    |     1     |  (3)         | |  6  |  b   |     5     |  (3)         | |  7  |  c   |     3     |  (3)         | +-----+------+-----------+--------------+ 

i understand e.g. row no 1 , 3 not duplicates , not removed union statement. however, want do. is, if name (e.g. "a") gets returned first select statement (prio 1) don't want other "a":s result set select statements of higher priority.

i.e, want this:

+-----+------+-----------+--------------+ |(no) | name |  price    |  (prio) | +-----+------+-----------+--------------+ |  1  |    |    10     |  (1)         | |  2  |  b   |     5     |  (1)         |  |  7  |  c   |     3     |  (3)         | +-----+------+-----------+--------------+ 

is possible?

i tried using group by requires me use min, max, avg etc. on price don't want do, i.e:

select name, avg(price) (...original query...) group name // not ok since donnot want avg price, want "first" price 

i using ms sql 2000. can use first(..) aggregate function in group by? when trying this, error:

select name, first(price) (...original query...) group name // error: 'first' not recognized built-in function name. 

thanks!

for sql server 2005+:

with records (     select  name, price, prio,             row_number() on (partition name                                 order prio asc) rn        products ) select  name, price    records   rn = 1 

try sql server 2000:

select  a.*    products         inner join         (             select  name, min(prio) min_prio                products                prio in (1,2,3)             group    name         ) b on a.name = b.name ,                 a.prio = b.min_prio 

for better performance, add compound index on column (name, prio).