pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Please help: How to determine largest of two numbers in a query?
4+ messages / 3 participants
[nested] [flat]

* Please help: How to determine largest of two numbers in a query?
@ 1998-12-02 21:55  Greg Youngblood <YoungblG@houstoncellular.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Greg Youngblood @ 1998-12-02 21:55 UTC (permalink / raw)
  To: pgsql-sql

I have a table with the following structure:
	yyyymmdd	int4
	key		char(16)
	value1		int4
	value2		int4

with the following sample data:
	yyyymmdd	key	value1	value2
	19981201	hello	32	16
	19981201	bye	29	64
	19981202	hello	16	20
	19981202	bye	23	13

What I need is to select the greatest between value1 and value2, so the
answer would be:
	yyyymmdd	key	value
	19981201	hello	32
	19981201	bye	64
	19981202	hello	20
	19981202	bye	23

I can do this via ODBC using access by creating a column which is defined as
IF(value1>value2,value1,value2) but that doesn't work in psql.

How can I make this work in psql?

Thanks
Greg



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Please help: How to determine largest of two numbers in a query?
@ 1998-12-03 01:45  Adam Maddock <adam@maddock.com>
  parent: Greg Youngblood <YoungblG@houstoncellular.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Adam Maddock @ 1998-12-03 01:45 UTC (permalink / raw)
  To: Greg Youngblood <YoungblG@houstoncellular.com>; +Cc: pgsql-sql

Greg -

This may not be the slickest way to do it, but it works...

SELECT yyyymmdd, key, value1 AS value
FROM greg
WHERE value1 > value2
UNION SELECT yyyymmdd, key, value2 AS value
FROM greg
WHERE value2 > value1;

I hope this helps.  :-)

Blessings,
  Adam

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 Adam Maddock                                 http://Adam.Maddock.com
 Detroit, MI                                         adam@maddock.com
    "BE IMITATORS of God, therefore, as dearly loved children..."
                                                  (Ephesians 5:1)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

On Wed, 2 Dec 1998, Greg Youngblood wrote:

> I have a table with the following structure:
> 	yyyymmdd	int4
> 	key		char(16)
> 	value1		int4
> 	value2		int4
> 
> with the following sample data:
> 	yyyymmdd	key	value1	value2
> 	19981201	hello	32	16
> 	19981201	bye	29	64
> 	19981202	hello	16	20
> 	19981202	bye	23	13
> 
> What I need is to select the greatest between value1 and value2, so the
> answer would be:
> 	yyyymmdd	key	value
> 	19981201	hello	32
> 	19981201	bye	64
> 	19981202	hello	20
> 	19981202	bye	23
> 
> I can do this via ODBC using access by creating a column which is defined as
> IF(value1>value2,value1,value2) but that doesn't work in psql.
> 
> How can I make this work in psql?
> 
> Thanks
> Greg
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Please help: How to determine largest of two numbers in a query?
@ 1998-12-03 02:24  David Hartwig <daybee@bellatlantic.net>
  parent: Greg Youngblood <YoungblG@houstoncellular.com>
  1 sibling, 0 replies; 4+ messages in thread

From: David Hartwig @ 1998-12-03 02:24 UTC (permalink / raw)
  To: Greg Youngblood <YoungblG@houstoncellular.com>; +Cc: pgsql-sql

My $.03 worth, without really knowing what these values represent:

There is no such "standard" query that can conditionally select between two
columns.   Fortunately PostgreSQL will allow you to create function - of one
does not already exist.

The function would be something like:
    greater_int(integer a, integer b)
It takes two integers arguments and returns the greater integer.

Then you do something like:

CREATE VIEW  myview AS
    SELECT yyymmdd, key, greater_int(value1, value2) FROM mytable;

Or just do the SELECTs directly.

This may not be the most efficient way to accomplish your goal.   I would also
take a good look at may table design.   Value1 and value2 MAY be a repeating
group and you may want to normalize a little more.  Or not.


Greg Youngblood wrote:

> I have a table with the following structure:
>         yyyymmdd        int4
>         key             char(16)
>         value1          int4
>         value2          int4
>
> with the following sample data:
>         yyyymmdd        key     value1  value2
>         19981201        hello   32      16
>         19981201        bye     29      64
>         19981202        hello   16      20
>         19981202        bye     23      13
>
> What I need is to select the greatest between value1 and value2, so the
> answer would be:
>         yyyymmdd        key     value
>         19981201        hello   32
>         19981201        bye     64
>         19981202        hello   20
>         19981202        bye     23
>
> I can do this via ODBC using access by creating a column which is defined as
> IF(value1>value2,value1,value2) but that doesn't work in psql.
>
> How can I make this work in psql?
>
> Thanks
> Greg




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: [SQL] Please help: How to determine largest of two numbers in a  query?
@ 1998-12-03 19:19  Greg Youngblood <YoungblG@houstoncellular.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Greg Youngblood @ 1998-12-03 19:19 UTC (permalink / raw)
  To: 'David Hartwig' <daybee@bellatlantic.net>; Greg Youngblood <YoungblG@houstoncellular.com>; +Cc: pgsql-sql

Thank you for the advice..

Ideally I would like to write a function, but I am just learning Postgres
and not familiar with creating functions like "greatest_int". 

In this case, the two values represent to different data streams. For the
information I need, I only need the greatest of the two. 

To work around this, I've started using two separate SELECT statements with
the comparison as part of the WHERE command. I also just tried the SELECT ..
UNION SELECT .. command, and it appears to have worked.

Thanks,
Greg

-----Original Message-----
From: David Hartwig [mailto:daybee@bellatlantic.net]
Sent: Wednesday, December 02, 1998 8:25 PM
To: Greg Youngblood
Cc: 'PostgreSQL SQL List'
Subject: Re: [SQL] Please help: How to determine largest of two numbers
in a query?


My $.03 worth, without really knowing what these values represent:

There is no such "standard" query that can conditionally select between two
columns.   Fortunately PostgreSQL will allow you to create function - of one
does not already exist.

The function would be something like:
    greater_int(integer a, integer b)
It takes two integers arguments and returns the greater integer.

Then you do something like:

CREATE VIEW  myview AS
    SELECT yyymmdd, key, greater_int(value1, value2) FROM mytable;

Or just do the SELECTs directly.

This may not be the most efficient way to accomplish your goal.   I would
also
take a good look at may table design.   Value1 and value2 MAY be a repeating
group and you may want to normalize a little more.  Or not.





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1998-12-03 19:19 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-12-02 21:55 Please help: How to determine largest of two numbers in a query? Greg Youngblood <YoungblG@houstoncellular.com>
1998-12-03 01:45 ` Adam Maddock <adam@maddock.com>
1998-12-03 02:24 ` David Hartwig <daybee@bellatlantic.net>
1998-12-03 19:19 RE: [SQL] Please help: How to determine largest of two numbers in a  query? Greg Youngblood <YoungblG@houstoncellular.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox