Solved

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

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

Suggested Solutions

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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…

920 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

16 Experts available now in Live!

Get 1:1 Help Now