Eliminate duplicates in SQL query

i have a table with 6 fields. the columns are ID, new_id price,title,Img,Active. I have datawhich is d开发者_如何学JAVAuplicated for the price column. When I do a select i want to show only distinct rows where new_id is not the same. e.g.-

ID New_ID   Price    Title     Img    Active
1    1      20.00   PA-1      0X4...   1
2    1      10.00   PA-10     0X4...   1
3    3      20.00   PA-11     0X4...   1
4    4      30.00   PA-5      0X4...   1
5    9      20.00   PA-99A    0X4...   1
6    3      50.00   PA-55     0X4...   1

When the select statement runs, only rows with ID (1,4,9,6) should show. Reason being the new_ID with the higher price should show up. How can i do this?

---

in a database that supports window aggregation (oracle, sql server 2005, postgresql 8.4) something like:

select id, new_id, price, title, img, active
from (select id, new_id, price, title, img, active,
             row_number() over (partition by new_id order by price desc) as position
      from the_table
) where position = 1

---

select * 
from T as t
where exists ( select 1 from T where new_id = t.new_id
 group by new_id having max(price) = t.price )

To test for existence, use exists! Here, you want those rows having the maximum price based on the new_id.

i want to show only distinct rows

Often when someone wants "distinct rows", they really want the "latest" rows or those with "the most" something. It almost always can be expressed in a form similar to the above.

---

select  * from ABC where charge_amt in(
SELECT     charge_amt 
FROM         ABC
GROUP BY charge_amt 
HAVING      (COUNT(charge_amt) = 1) )
order by charge_amt asc