Can't get MAX DISTINCT to work the way I want it to

I have 3 numeric fields in a table that need to be returned by a query:
SupervisorLineID (autoincrementing ordinal), SupervisorID and LineID.
SupervisorLineID is the only definite unique value; there are cases where
one supervisor can manage more than one production line, and thus be
represented many times in the table. There are also cases where more than
one supervisor can manage a line, complicating things further.
What I need to return is one unique record for each LineID, with the highest
SupervisorLineID number as the selecting factor, along with the matching
SupervisorID. I tried using MAX DISTINCT on the SupervisorLineID, but oddly
enough it caused the opposite of what I expected: all supervisors were
returned EXCEPT the one represented by the max SupervisorLineID per line!
This caused lines to appear more than once in many cases.
I've tried various approaches and none produce the desired results. I know
this has to be possible-- any ideas?
Thanks,
Randall ArnoldRandall Arnold wrote:

> I've tried various approaches and none produce the desired results.
> I know this has to be possible-- any ideas?
Please provide DDL and some testdata, you are talking about more than 1
table I presume? We can't guess all the tablestructures.
Kind regards,
Stijn Verrept|||Hi
Since you have not posted a table stucture + sample data I did some testing
on Northwind database and Orders table
SELECT * FROM Orders
WHERE OrderDate=(SELECT MAX(OrderDate) from Orders O
WHERE O.CustomerId=Orders.CustomerId)
Getting highest OrderDate for each Customer
"Randall Arnold" <randall.arnold@.nokia.com> wrote in message
news:OwClf.16116$Nb2.285732@.news1.nokia.com...
>I have 3 numeric fields in a table that need to be returned by a query:
>SupervisorLineID (autoincrementing ordinal), SupervisorID and LineID.
>SupervisorLineID is the only definite unique value; there are cases where
>one supervisor can manage more than one production line, and thus be
>represented many times in the table. There are also cases where more than
>one supervisor can manage a line, complicating things further.
> What I need to return is one unique record for each LineID, with the
> highest SupervisorLineID number as the selecting factor, along with the
> matching SupervisorID. I tried using MAX DISTINCT on the
> SupervisorLineID, but oddly enough it caused the opposite of what I
> expected: all supervisors were returned EXCEPT the one represented by the
> max SupervisorLineID per line! This caused lines to appear more than once
> in many cases.
> I've tried various approaches and none produce the desired results. I
> know this has to be possible-- any ideas?
> Thanks,
> Randall Arnold
>|||Randall wrote on Wed, 07 Dec 2005 14:41:50 GMT:

> I have 3 numeric fields in a table that need to be returned by a query:
> SupervisorLineID (autoincrementing ordinal), SupervisorID and LineID.
> SupervisorLineID is the only definite unique value; there are cases where
> one supervisor can manage more than one production line, and thus be
> represented many times in the table. There are also cases where more than
> one supervisor can manage a line, complicating things further.
> What I need to return is one unique record for each LineID, with the
> highest SupervisorLineID number as the selecting factor, along with the
> matching SupervisorID. I tried using MAX DISTINCT on the
> SupervisorLineID, but oddly enough it caused the opposite of what I expect
ed:
> all supervisors were returned EXCEPT the one represented by the max
> SupervisorLineID per line! This caused lines to appear more than once in
> many cases.
> I've tried various approaches and none produce the desired results. I
> know this has to be possible-- any ideas?
> Thanks,
> Randall Arnold
DISTINCT applies to an entire row, not a single column.
Without DDL I've had to make some assumptions.
I think this might work ...
SELECT A.LineID, A.SLID, B.SupervisorID FROM
(SELECT LineID, MAX(SupervisorLineID) AS SLID FROM Table GROUP BY LineID) AS
A)
INNER JOIN Table B ON A.SLID = B.SupervisorLineID AND A.LineID = B.LineID
There's probably an easier way to write this.
Dan|||Actually it's only one table, and I provided the structure (field list) in t
he first post. That's really all there is to the table. Sorry if I didn't
make that clear, but I would have listed other tables if any were involved.
Test data would be as follows:
RecentLineSupervisor_View SupervisorID SupervisorLineID LineID
18 9804 1
108 9913 1
128 -->9964 1
7 9715 19
11 9752 19
118 -->9894 19
1 9831 20
24 9688 20
108 -->9927 20
6 9782 22
77 9771 22
78 -->9978 22
As I said, I just want to see each LineID represented once, with the highest
value for SupervisorLineID determining which record is returned. Desired re
cords marked with arrows.
Thanks,
Randall Arnold
"Stijn Verrept" <stjin@.entrysoft.com> wrote in message news:YLWdnelcNs6FZAveRVnyig@.scarlet.
biz...
> Randall Arnold wrote:
>
>
> Please provide DDL and some testdata, you are talking about more than 1
> table I presume? We can't guess all the tablestructures.
>
> --
>
> Kind regards,
>
> Stijn Verrept|||Randall Arnold wrote:
> I have 3 numeric fields in a table that need to be returned by a query:
> SupervisorLineID (autoincrementing ordinal), SupervisorID and LineID.
> SupervisorLineID is the only definite unique value; there are cases where
> one supervisor can manage more than one production line, and thus be
> represented many times in the table. There are also cases where more than
> one supervisor can manage a line, complicating things further.
>
If those are the only three columns then the combination of
(supervisorid, lineid) should be unique and declared as such -
otherwise your table is full of redundant garbage. Therefore the answer
would be:
SELECT supervisorid, lineid
FROM your_table AS T
WHERE supervisorlineid =
(SELECT MAX(supervisorlineid)
FROM your_table
WHERE lineid = T.lineid) ;
However, you then seem to be breaking the golden rule of using an
IDENTITY column - don't assign any business significance to it. In
future, please post DDL and sample data so that we don't have to guess
your requirements.
David Portas
SQL Server MVP
--|||Please don't assume I designed this table; rest assured I would NOT have
taken this approach! I'm stuck with trying to extract meaningful
information from a previous employee's cowboy database. In fact, the only
reason I have to create this query at all is to select a single
supervisor-to-line record for each line, and that ability *should* have been
designed into the structure. Had the former data manager done his job
properly, I wouldn't be dealing with this.
As for DDL and structure, it seemed to me I posted enough info. As I told
another gentleman, had there been another table involved I would have
indicated so. I also figured that listing the only 3 fields involved and
describing their type and role would cover it; note that everyone who
provided a suggestion based on a single table was on the right track so it
seems to me it wasn't TOO confusing. But I'll try to be more pedantic in
the future <g>.
Anyway, it looks like one of the methods listed here will work. Thanks all,
Randall Arnold
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1133968276.805046.224980@.o13g2000cwo.googlegroups.com...
> Randall Arnold wrote:
> If those are the only three columns then the combination of
> (supervisorid, lineid) should be unique and declared as such -
> otherwise your table is full of redundant garbage. Therefore the answer
> would be:
> SELECT supervisorid, lineid
> FROM your_table AS T
> WHERE supervisorlineid =
> (SELECT MAX(supervisorlineid)
> FROM your_table
> WHERE lineid = T.lineid) ;
> However, you then seem to be breaking the golden rule of using an
> IDENTITY column - don't assign any business significance to it. In
> future, please post DDL and sample data so that we don't have to guess
> your requirements.
> --
> David Portas
> SQL Server MVP
> --
>|||Randall Arnold wrote:
> Please don't assume I designed this table; rest assured I would NOT have
> taken this approach! I'm stuck with trying to extract meaningful
> information from a previous employee's cowboy database. In fact, the only
> reason I have to create this query at all is to select a single
> supervisor-to-line record for each line, and that ability *should* have be
en
> designed into the structure. Had the former data manager done his job
> properly, I wouldn't be dealing with this.
> As for DDL and structure, it seemed to me I posted enough info. As I told
> another gentleman, had there been another table involved I would have
> indicated so. I also figured that listing the only 3 fields involved and
> describing their type and role would cover it; note that everyone who
> provided a suggestion based on a single table was on the right track so it
> seems to me it wasn't TOO confusing. But I'll try to be more pedantic in
> the future <g>.
> Anyway, it looks like one of the methods listed here will work. Thanks al
l,
> Randall Arnold
>
In the absence of the DDL it's reasonable to assume that we lack the
information about the alternate key(s) because that information was so
conspicuously absent from your post. I didn't assume you designed it. I
assumed you'd want to fix it.
David Portas
SQL Server MVP
--|||> As for DDL and structure, it seemed to me I posted enough info.
That's all fine and good. However, in order for us to provide you with a
meaningful, accurate and testable solution, instead of guessing, we ask for
a bit more. I don't understand where the vehement objection to providing
proper specs comes from. The prevailing opinion seems to be that we are
lazy and are trying to be a pain in the ass. Nothing could be further from
the truth... please read http://www.aspfaq.com/5006 before assuming that
what you provide should be enough for anyone to solve the problem.|||There was no "vehement objection" to providing proper specs, Aaron. Just a
minor goof IMO on my part based on personal tendencies (ie, omitting
reference to what *isn't* there such as other tables). I don't understand
where this perception of a "vehement objection" comes from. Certainaly not
from anything I've posted! Personally, I'm all too eager to provide as much
detail as possible. Again, I *thought* I had. Mea culpa. But no need for
a hangin', please. The verbal beating has been enough.
This is a silly axle anyway. I gotta unwrap myself and get back to work
<g>.
Randall Arnold
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyRFaW0%23FHA.3676@.tk2msftngp13.phx.gbl...
> That's all fine and good. However, in order for us to provide you with a
> meaningful, accurate and testable solution, instead of guessing, we ask
> for a bit more. I don't understand where the vehement objection to
> providing proper specs comes from. The prevailing opinion seems to be
> that we are lazy and are trying to be a pain in the ass. Nothing could be
> further from the truth... please read http://www.aspfaq.com/5006 before
> assuming that what you provide should be enough for anyone to solve the
> problem.
>