CODEWITHSUNDEEP
sqlduplicates
ewdef
ewdef

Reputation: 21

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 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?

Upvotes: 2

Views: 1043

Answers (3)

Paresh
Paresh

Reputation: 16

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

Upvotes: 0

James K. Lowden
James K. Lowden

Reputation: 7837

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.

Upvotes: 0

araqnid
araqnid

Reputation: 133482

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

Upvotes: 2

Related Questions