Solved

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

Posted on 2010-11-09
5
314 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
[X]
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
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

740 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