?
Solved

MySQL: Selecting keys where column douplicate

Posted on 2014-01-27
5
Medium Priority
?
464 Views
Last Modified: 2014-02-10
This returns 3 columns:
SELECT bookkey, isbn, count(isbn) as cnt FROM elqBooks GROUP BY ISBN HAVING cnt > 1

Open in new window

I only want it to return 1 column: "bookkey".
0
Comment
Question by:hankknight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 15

Assisted Solution

by:Jagadishwor Dulal
Jagadishwor Dulal earned 200 total points
ID: 39814030
Try Using Nested Query Like:

SELECT bookkey FROM elqBooks WHERE( SELECT count(isbn) as cnt FROM elqBooks GROUP BY ISBN HAVING cnt > 1)
                

Open in new window

0
 
LVL 16

Author Comment

by:hankknight
ID: 39814032
That returns an error:
Error: Subquery returns more than 1 row
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1200 total points
ID: 39814278
Are you sure your original query is not giving any error?

You have bookkey in SELECT clause but not in GROUP BY clause.

Anyway, try this.
SELECT bookkey 
FROM elqBooks 
GROUP BY book key,ISBN 
HAVING count(isbn) > 1

Open in new window

0
 
LVL 8

Expert Comment

by:Surrano
ID: 39814295
Well first of all, select you can always the first field of your original query:
SELECT bookkey from 
(SELECT bookkey, isbn, count(isbn) as cnt FROM elqBooks GROUP BY ISBN HAVING cnt > 1);

Open in new window

A cleaned-up version:
SELECT bookkey FROM elqBooks GROUP BY ISBN HAVING count(*) > 1;

Open in new window

but it should not work (nor your original query; did you try it?) since you aggregate without grouping by bookkey. So what is it *exactly* that you'd like to achieve?

-- for all ISBNs that appear more than once in the table, list all bookkeys?
SELECT bookkey from elqBooks 
where isbn in (select isbn from elqBooks group by isbn having count(*)>1);

-- list all bookkeys that appear more than once in the table?
SELECT bookkey FROM elqBooks GROUP BY bookkey HAVING count(*) > 1;

-- for all (bookkey, ISBN) pairs that appear more than once, list the bookkey only but not the isbn?
SELECT bookkey FROM elqBooks GROUP BY bookkey, ISBN HAVING count(*) > 1;

Open in new window

0
 
LVL 8

Assisted Solution

by:Surrano
Surrano earned 600 total points
ID: 39814309
OMG your original query works. MySQL is soooo broken I can't believe it. I wonder if some other expert could explain me how it was a clever idea from the creators of this concept.

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY:

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

Open in new window


For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

To summarise:
- it is not standard SQL, so not portable
- you can't predict which value will be printed if there are separate values, unless you sort or do some other trick (which makes mysql's reasoning void on this concept)
- so don't use unaggregated columns.

If you want *one* bookkey for each group which is what mysql does, a *standard sql* way of doing this is:
select min(bookkey)  FROM elqBooks GROUP BY ISBN having count(*)>1;

Open in new window

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

770 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