Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Creating and Managing Databases with phpMyAdmin in cPanel.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

815 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

9 Experts available now in Live!

Get 1:1 Help Now