Re: [SQL] Help Retrieving Latest Record

[Dorin Grunberg]

It would be something like:

select * from basket where Date=(SELECT max(Date) from basket);



At 09:41 AM 2/16/2001 -0500, Steve Meynell wrote:
>Hi,  I am not sure if this went through the first time so here it is again.
>
>
>Ok what I am trying to do is select out of the database the latest record 
>meeting a certain criteria.
>
>Example:
>
>Number |Fruit | Date
>15Apples  July 20, 1999
>20OrangesJune 7, 2000
>13 PearsJan 31, 2000
>17 Apples April 10, 1999
>Now what I need to do is select the oranges out because the date is the 
>latest one, something like:
>
>select * from basket where max(date);
>This would yield me:
>20OrangesJune 7, 2000
>
>I know this doesn't work but I need something like it.
>or something like
>
>select * from basket where max(date) and fruit='Apples';
>This would yield me:
>15Apples  July 20, 1999
>
>Thank you in advance,
>
>--
>Steve Meynell
>Candata Systems

Re: [SQL] use of arrow keys to traverse history

[Dorin Grunberg]

You need to install Readline library. I know I used readline 4.1 and it 
works great.

Dorin


At 12:42 PM 4/25/2001 -0600, Peter J. Schoenster wrote:
>Hi,
>
>Not sure where this question belongs ... I thought postgresql was
>running under the bash shell where I can use up and down arrow
>keys to traverse my command history. I can do this in mysql but
>oddly not in oracle or postgresql.
>
>/home/postgres
>
>-rw-r--r--   1 postgres postgres 1422 Feb 16 15:50 .Xdefaults
>-rw---   1 postgres postgres  458 Feb 17 16:59 .bash_history
>-rw-r--r--   1 postgres postgres   24 Feb 16 15:50 .bash_logout
>-rw-r--r--   1 postgres postgres  230 Feb 16 15:50 .bash_profile
>-rw-r--r--   1 postgres postgres  313 Feb 17 16:36 .bashrc
>
>Which in my ignorance leads me to believe that postgres will run in
>the bash shell and so I expect the use of arrow keys or command
>history.
>
>Clues appreciated.
>
>Peter
>
>---
>"Reality is that which, when you stop believing in it, doesn't go
>away".
> -- Philip K. Dick
>
>---(end of broadcast)---
>TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] position(text,text) function

[Dorin Grunberg]

The correct way would be:

select position('el' in 'Hello');

Dorin

At 02:27 PM 7/23/01 +, Karl Orbell wrote:
I'm having trouble using the position
function, it's a simple built-in function.

It returns the index of a substring within a main string.

But, I just can't get it to work, it always gives this silly parser
error.
I've tried it in a variety of ways with variables instead of constants
casting 
to text and using it in other contexts, (ie. in functions, not just in a

select).  What am I doing wrong?  Is this not the correct
syntax for a two 
argument function?

test=# select position('hello','el');
ERROR:  parser: parse error at or near ","


Karl Orbell.


---(end of
broadcast)---
TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]

Re: [SQL] Wildcard in date field???

[Dorin Grunberg]

SELECT  * FROM my_table WHERE date_part('month', col_name::datetime) = '06' 
AND date_part('year', col_name::datetime) = '2000';

Looks for more detail in the manual for "date_part"

Dorin

At 03:52 PM 6/22/2000 -0400, Web Manager wrote:
>Hello,
>
>I need to make a query that will select items that have a date matching
>the current month for example.
>
>For June this means that any day between 2000-06-01 and 2000-06-30 are
>ok. To do that I need a wildcard like "%" to replace the actual day in
>the date field.
>
>Ex.:  select * from item where date = '2000-06-%%';
>
>but that doesn't work... What is the right way?
>
>Thanks!
>--
>~
>Marc Andre Paquin

[SQL] Subselects with IN and EXISTS

[Dorin Grunberg]

Hello,

Here is my query.

SELECT id, title, type, sub_type, order_number, version, date, referred_to, 
referred_in
FROM sop
WHERE (type||sub_type||order_number, version)

IN
^^^
(SELECT type||sub_type||order_number, max(version)
FROM sop
GROUP BY type||sub_type||order_number)
ORDER BY type, sub_type, order_number

It looks like is not as fast as I would like so I thought of rewriting it as:

SELECT id, title, type, sub_type, order_number, version, date, referred_to, 
referred_in
FROM sop
WHERE

EXISTS

(SELECT type||sub_type||order_number, max(version)
FROM sop
GROUP BY type||sub_type||order_number)
ORDER BY type, sub_type, order_number

The results that I get are not the same. Could anyone point what am I doing 
wrong?

tia

Dorin