Solved

Getting top 1 record for each duplicate entry in the table in mysql

Posted on 2010-11-09
5
309 Views
Last Modified: 2012-05-10
Hi,
 
I am writing a query in mysql.
 
I have duplicate records in my mysql table 'exceltransactiondetail'
 
I wanted to have only first record or top 1 record for each duplicate record.
 
I cannot use distinct as there are other fields which is not unique such as primary key 'exceltransactiondetail.ExcelTransactionDetailID'
 
and I cannot ignore this field as this is required in my select query. Can you please help me in fixing my query
 
 
If I use Limit 0,1 in query then I only get first record, that is also not required.
 
 
Duplicate entry will be of following fields
exceltransactiondetail.SecCode, exceltransactionmaster.TransactionDate,
                                                exceltransactiondetail.BuySell,
                                                exceltransactiondetail.tranTime,exceltransactiondetail.Price
                                               
except exceltransactiondetail.ExcelTransactionDetailID which is primary key
 
Please guide
 
Many Thanks
SELECT 

	exceltransactiondetail.SecCode, exceltransactionmaster.TransactionDate, 

	exceltransactiondetail.ExcelTransactionDetailID,

	exceltransactiondetail.BuySell,

	exceltransactiondetail.tranTime,exceltransactiondetail.Price

FROM exceltransactiondetail 

inner join exceltransactionmaster

	on exceltransactiondetail.fk_ExcelTransactionID = exceltransactionmaster.ExcelTransactionID

inner join transaction

	on transaction.TransactionDate = exceltransactionmaster.TransactionDate

WHERE                                                 

	isnull(exceltransactiondetail.SecCode)=0

	and transaction.ProductISINCode = exceltransactiondetail.SecCode 

	and exceltransactionmaster.ExcelTransactionID = p_ExcelMasterTransactionID

	and Year(exceltransactionmaster.TransactionDate) = Year(transaction.TransactionDate) 

	and Month(exceltransactionmaster.TransactionDate) = Month(transaction.TransactionDate) 

	and Day(exceltransactionmaster.TransactionDate) = Day(transaction.TransactionDate) 

	and transaction.ProductISINCode = exceltransactiondetail.SecCode 

	and transaction.BuySell=exceltransactiondetail.BuySell

	and transaction.TradeTime=exceltransactiondetail.tranTime

	and transaction.PriceTraded=exceltransactiondetail.Price

	and transaction.isdeleted=0

	and isnull(transaction.fk_ExcelTransactionID) = 1

	Limit 0,1;

Open in new window

0
Comment
Question by:tia_kamakshi
  • 2
  • 2
5 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 34098887
Since only one field is not distinct, you can use this easy change

select f1,f2,f3,f4,MAX(detail_id) as detail_id,f5,f6
..... your entire query ....
WHERE...
GROUP BY f1,f2,f3,f4,f5,f6

remove the limit clause
0
 
LVL 2

Assisted Solution

by:dmlyo150
dmlyo150 earned 250 total points
ID: 34098909
select a.SecCode, a.TransactionDate, max(a.ExcelTransactionDetailID), a.BuySell, a.TranTime, a.Price
from exceltransactiondetail a
group by a.SecCode, a.TransactionDate, a.BuySell, a.TranTime, a.Price
0
 

Author Comment

by:tia_kamakshi
ID: 34098962
Many thanks.

I will check and come back to you in next 2 hours

Kind Regards
0
 

Author Closing Comment

by:tia_kamakshi
ID: 34103494
Many Thanks for your help
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 34103639

cyberkiwi and I had similar solutions -please understand I cannot see any solutions when I add mine cause I am not a paying member -I just answer for my own edification. I am unable to see anything but the question until I provide a (possible) solution. Thanx for the Points –every Point helps. I will soon have a free month.
 
best regards
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now