Eliminate duplicates in SQL query

Question

i have a table with 6 fields. the columns are ID, new_id price,title,Img,Active. I have datawhich is duplicated 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?

Answer 1

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

Answer 2

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.