-
Apr 1st, 2014, 10:03 AM
#1
Thread Starter
PowerPoster
Query that finds rows that aren't duplicates
Can you help me modify this query to return rows that don't have the same RetailPct column?
Here is the query:
Code:
select distinct rl.RetailPct, rm.MenuID
from RestaurantLoc rl
join RestaurantMeal rm on rm.RestLocId = rl.RestLocID
where MenuID = 2657
If I run it, it returns
RetailPct...MenuID
0.00........2657
0.20........2657
This is a MenuID I want to know about.
However, if the query returned
RetailPct...MenuID
0.5..........2657
0.5..........2657
I would not want to know about it, because the RetailPct is the same.
So...a query that returns rows telling me that for a given MenuID, there is more than one RetailPct value.
Thanks.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 1st, 2014, 11:19 AM
#2
Addicted Member
Re: Query that finds rows that aren't duplicates
give this a try
Code:
select rl.RetailPct, rm.MenuID
from RestaurantLoc rl
join RestaurantMeal rm on rm.RestLocId = rl.RestLocID
where MenuID = 2657
group by rl.retailPct, rm.MenuID
having count(*) <= 1;
-
Apr 1st, 2014, 12:06 PM
#3
Thread Starter
PowerPoster
Re: Query that finds rows that aren't duplicates
That didn't return any rows. Not sure, but I don't think you can throw in "having count(*)" as you did. I usually have the count in the select, like this:
select (RetailPct), count(rl.RetailPct) as dups
from RestaurantLoc rl
join RestaurantMeal rm on rm.RestLocId = rl.RestLocID
GROUP BY rl.RetailPct
HAVING (COUNT(rl.RetailPct) > 1)
But that only tells me how often I have the RetailPct.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 1st, 2014, 12:28 PM
#4
Addicted Member
Re: Query that finds rows that aren't duplicates
You can use having and count() like the way I did. I was looking to show a count of the results only display the ones whom have a count of one.
take a look here http://sqlfiddle.com/#!2/b654cd/1
can you show us the rest of the table schema and some sample data.
-
Apr 2nd, 2014, 02:53 AM
#5
Re: Query that finds rows that aren't duplicates
Yep, the Having Count is perfectly normal. The whole point of Having is that it's like a Where clause that works on aggregate functions. About the only thing I'd have picked up on SM's code was that I think you'd actually want Having Count(*) = 1 rather than <=. I think the < is redundant but I've got an uneasy feeling I'm missing something there (it's first thing in teh morning and I'm not really awake yet) and I'm certainly being overly pedantic.
Just to be blear, are you sure you should be using Where MenuID = 2657 in the query. That means you're only checking whether 2657 has more than one RetailPct. you're not checking for any menu ID that has more than one RetailPct. If you want to check for any menu id I think you'd actually want this (typed staight in so be wary of syntax errors):-
Select MenuID
From RestaurantLoc rl
join RestaurantMeal rm on rm.RestLocId = rl.RestLocID
Group By MenuID
Having Count(distinct RetailPct) = 1
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Apr 2nd, 2014, 06:40 AM
#6
Thread Starter
PowerPoster
Re: Query that finds rows that aren't duplicates
Well, I was looking for something wrong with SM's query because it returned no rows. And FD, yours returns too many (therefore, I know it is returning MenuID's that either have one RetailPct row or have multiple RetailPct rows that are equal). I'll get back to this in a bit. It's first thing in the morning for me, and I have a bug to look at. And in the meantime, I "solved" this issue by looking at the resultset (it was small enough for me to find what I wanted). Thanks for your input, and I will explain in more detail what's wrong.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|