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
精彩评论