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

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,
except exceltransactiondetail.ExcelTransactionDetailID which is primary key
Please guide
Many Thanks
	exceltransactiondetail.SecCode, exceltransactionmaster.TransactionDate, 
FROM exceltransactiondetail 
inner join exceltransactionmaster
	on exceltransactiondetail.fk_ExcelTransactionID = exceltransactionmaster.ExcelTransactionID
inner join transaction
	on transaction.TransactionDate = exceltransactionmaster.TransactionDate
	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

Who is Participating?
cyberkiwiConnect With a Mentor Commented:
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 ....
GROUP BY f1,f2,f3,f4,f5,f6

remove the limit clause
dmlyo150Connect With a Mentor Commented:
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
tia_kamakshiAuthor Commented:
Many thanks.

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

Kind Regards
tia_kamakshiAuthor Commented:
Many Thanks for your help

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.