Eliminate Duplicates in SQL Query
I have a table with 6 fields. columns - ID, price new_id, title, Img, Active. I have data that is duplicated for a price column. When I make a selection, I only want to show individual rows where new_id is not the same. eg.-
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 is executed, only rows with ID (1,4,9,6) should be displayed. The reason for the new_ID with a higher price should appear. How can i do this?
a source to share
in a database that supports window aggregation (oracle, sql server 2005, postgresql 8.4), for example:
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
a source to share
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 check for existence, use exists
! Here you want these lines to have a maximum price based on new_id.
I only want to show individual lines
Often times, when someone wants the "great" lines, they really want the "last" lines, or the ones with the "most" something. It can almost always be expressed in a form similar to the one above.
a source to share