On Wed, Jul 17, 2002 at 18:54:28 +0000,
User <User@Anonymous.USA> wrote:
> Hi everyone,
>
> I've been racking my brain against this problem for a few days now and
> figured I'd turn it over to the experts. I have a table with three columns.
> Lets call them:
>
> User ID Text
> Department Numeric
> Percent Numeric
>
> * No primary key - sorry
>
> A User's time is broken up amongst different departments
> I need to show a single record for each user id with the department they
> belong to that has the greatest percentage. I've been able to do this with
> the max(percent) and group by approach. However, I run into issues when the
> breakdown between two departments is an exact 50%. So, to make a long story
> short, I need to translate the following logic into SQL:
>
> Display results with the columns User ID, Department, Percent but only show
> the department with the highest percentage for each user id and if the
> percentage is 50% between two departments, show the department with the
> highest numberic value as a differentiating factor.
If you want the whole table then you can use:
select distinct on (userid) userid, department, percent from tablename
order by userid, percent, department desc;
If you are going use this as a view, this may not be the best way to
do things.