Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL If-Then Logic in Query

SQL If-Then Logic in Query

From
"User"
Date:
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 this is not the correct group to post this question, sorry in advance.
If you can point me to a web site that may have a tutorial with an example
similar to this, I'd appreciate it...

Thanks!

Re: SQL If-Then Logic in Query

From
Bruno Wolff III
Date:
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.

Re: SQL If-Then Logic in Query

From
Holger Klawitter
Date:
Hi Mr. or Mrs. X,

I understand concerns regarding spam, but posts without a real name and
invalid return adresses are generally considered 'rude'. But I'll try to help
anyway :-)

Probably you will be fine using the distinct feature

SELECT
 DISTINCT ON (id)
 id,dept,perc
FROM
 your_table
ORDER BY
 id, perc desc, dept desc
;

You will receive only the first row with the given id. The sorting order
on perc and dept will make the row you need coming first.

> If this is not the correct group to post this question, sorry in advance.
> If you can point me to a web site that may have a tutorial with an example
> similar to this, I'd appreciate it...

DISTINCT is explained in the SELECT section of the Reference manual.

With kind regards / mit freundlichem Gruß
    Holger Klawitter
--
Holger Klawitter                          http://www.klawitter.de
lists@klawitter.de