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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
