Home   Best Sellers   Blogging   Coding & Design   Technology   SEO   Travel & living   Career   Videos   Tips   Calculators     
Home  »     »     »     »  MySQL query to retrive only one record for a repeating column or rows of data

MySQL query to retrive only one record for a repeating column or rows of data

Tuesday, December 17, 2013

MySQL query to retrive only one record for a repeating column or duplicate rows of data

Suppose there is a table tbl_BOOKS which stores records of various books. some books comes under different categories. so there may be multiple records with same BookReferenceNumber. But when user searches for a particular reference number, the search result should display only one record eventhough there are multiple entries for same BookReferenceNumber.

Let us illustrate this with a sample table given below:
Table Name: tbl_BOOKS

BookId BookReferenceNumber BookName BookCategory BookDetails BookStatus
1 5546 Let us C C Language C tutorial book Available
2 2311 Java Complete reference Java Programming Java quick reference text Unavailable
3 5546 Let us C Programming C tutorial book for dummies Available
4 8900 PHP & PERL Web Programming Complete guide for PHP and Perl programming Available

In the above table, the book "Let us C" is repeating, this is to show the record under multiple categories. ie "C Language" and "Programming" , but user should view only one of this 2 records in the search results

Here is the sample query for accomplishing the above mentioned task.


SELECT
BookId, BookReferenceNumber,
BookName, BookCategory,
COUNT( BookReferenceNumber ) AS HowManyRecs,
BookDetails, BookStatus

FROM tbl_BOOKS

WHERE (
BookReferenceNumber LIKE '42324%'
OR BookName LIKE '%42324%'
)
AND (
BookStatus = 'Available'
)
GROUP BY BookReferenceNumber
HAVING (
HowManyRecs > 0
)



The result of the above query is given below. Please note there is only one record corresponding to book "Let us C"

Book
Id
BookReference
Number
Book
Name
Book
Category
HowMany
Recs
Book
Details
Book
Status
3 5546 Let us C Programming 2 C tutorial book
for dummies
Available
4 8900 PHP & PERL Web Programming 1 Complete guide for
PHP and Perl
programming
Available

Try it and post your valuable comments  in the comment section below :)



Sample query to find records having duplicates

SELECT BookReferenceNumber, COUNT(BookReferenceNumber) AS HowMany FROM tbl_BOOKS
GROUP BY BookReferenceNumber
HAVING ( COUNT(BookReferenceNumber) > 1 )


SELECT BookReferenceNumber, COUNT(BookReferenceNumber) AS HowMany FROM tbl_BOOKS
GROUP BY BookReferenceNumber
HAVING ( HowMany > 1 )



Read how to Delete duplicate records from a mysql table by keeping only one record with highest or lowest id value.


Related post:



How to link to this page?
If you wish to link to this page from your website, simply Copy and paste the above HTML code to your web page. It will appear on your page as:
MySQL query to retrive only one record for a repeating column or rows of data.




Share this!




comments powered by Disqus

This Weeks 7 Popular Posts


Subscribe to Recent Posts by Email
Stay connected to CROZOOM with regular Email notices of new Techie articles and IT Jobs. Updates will be delivered to your Inbox as soon as they are posted online.

Enter Your Email Address:  

Delivered by FeedBurner   RSS Feed

Search this Blog   



Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »