Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2010-11-09
5
313 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

839 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