Results 1 to 6 of 6

Thread: Query that finds rows that aren't duplicates

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  2. #2
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    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;

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  4. #4
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    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.

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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
  •  



Click Here to Expand Forum to Full Width