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
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"
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:
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 find duplicate records in a table. - MySQL or SQL Query to find duplicates in a table
- How to delete duplicate records from a mysql table by keeping only one record with highest or lowest id value
- MySQL Query for getting subcategory name and parent category name in a single row
- MySQL Query for getting nested items in a single query
- For MySQL related articles, Click here
