Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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

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
tia_kamakshi
Asked:
tia_kamakshi
  • 2
  • 2
2 Solutions
 
cyberkiwiCommented:
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
 
dmlyo150Commented:
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
 
tia_kamakshiAuthor Commented:
Many thanks.

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

Kind Regards
0
 
tia_kamakshiAuthor Commented:
Many Thanks for your help
0
 
dmlyo150Commented:

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now