If that ID is the primary key, then don't bother with a DISTINCT(). Just
select everything from the table. Otherwise you're going to make the
backend select everything , then waste time doing the distinct.
A quick way to confirm would be to just run these 2 commands:
SELECT COUNT(item_id) FROM table;
SELECT COUNT(item_id) FROM (SELECT DISTINCT item_id FROM TABLE)
q_distinct;
If they both return the same count, there are no dupes.
On Wednesday, March 9, 2016 at 5:29:50 PM UTC-5, Alex Hall wrote:
>
> That makes sense. Part of my problem is that, as I've mentioned in the
> past, I was recently hired. I didn't set anything up, and I still
> don't know for sure what I can trust to be unique, or 6 versus 8
> characters, or a lot of other small details. That said, SSMS shows the
> item ID as a primary key, which means it is unique. I think I'm safe
> to just apply distinct() to my entire query, since there's no way the
> ID can ever be repeated. I've been looking at a bunch of tables today,
> and I had it in my head that the id in this one was only *part of* the
> PK and thus could be duplicated. At least I learned something from all
> this. Thanks again for the help, guys.
>
> On 3/9/16, Jonathan Vanasco <[email protected] <javascript:>> wrote:
> >
> > On Wednesday, March 9, 2016 at 3:02:05 PM UTC-5, Alex Hall wrote:
> >>
> >> Fair enough, thanks. I didn't realize it was such a complex task; I
> >> figured it was just a matter of passing an argument to distinct() or
> >> something equally easy.
> >>
> >
> >
> > Yeah PostgreSQL is the only db that supports "DISTINCT ON"... but it can
> be
> >
> > very awkward.
> >
> > Let me try to explain this better, because I was you a few years ago --
> and
> >
> > thought / believed the same things. (and this still annoys me!)
> >
> > Here's a table to represent an imaginary situation where `id` is the
> > primary key (it is unique) but the other columns aren't.
> >
> > id | product_id | name
> > ===+============+=====
> > 1 | 1 | foo
> > 2 | 1 | bar
> > 3 | 2 | biz
> > 4 | 2 | bang
> > 5 | 3 | foo
> > 6 | 1 | bar
> >
> > The distinct column values are:
> >
> > id - 1,2,3,4,5
> > product_id - 1, 2, 3
> > name - foo, bar, biz, bang
> >
> > If you want to get distinct data from the table though, you need to
> think
> > in rows. (unless you're querying for column data)
> >
> > If you want "distinct" rows based on the product id, how should these 3
> > rows be handled?
> >
> > 1 | 1 | foo
> > 2 | 1 | bar
> > 6 | 1 | bar
> >
> > They all have 1 for the product_id.
> >
> > The rows are all distinct if we think of the primary id key being an
> > attribute.
> > If we limit the distinction to the product_id and the name, we can drop
> the
> >
> > 3 down to 2 combinations:
> >
> > 1 | foo
> > 1 | bar
> >
> > But this probably won't work for your needs.
> >
> > The (1, foo) row corresponds to id 1;
> > but the (1, bar) row could correspond to (2,1,bar) or (6,1,bar) rows in
> the
> >
> > table.
> >
> > So when you say only want rows "where the item number is distinct.", you
> > should try asking "What should I do with rows where the item_number
> isn't
> > distinct?"
> >
> > That should raise some red flags for you, and help you realize that you
> > probably don't really want rows where the item number is distinct.
> You
> > probably want to do some other query and approach some other goal.
> >
> > "DISTINCT" is (usually) a really complex situation because people often
> > think it will do one thing, but it does something very different... and
> to
> > accomplish the task they want, it's a totally different query.
> >
> > --
> > You received this message because you are subscribed to the Google
> Groups
> > "sqlalchemy" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an
> > email to [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
> >
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
