Re: [GENERAL] Problem with getting the right order
On Wed, 21 Oct 1998, Henrik Pedersen wrote: // When i do a : // // ./psql -c "select count(vnr),enr from votes where cnr=1 group by enr" // vote // This is almost what i want, execpt that i want the result to be // ordered by count, instead og enr. so that the entry, which got most // votes is listed first You should slap an ``order by count'' at the end. I think it'd be ``order by count(vnr)'' or you could alias it and order it by the alias. // For example: // //11| 1 //10| 8 // 4| 4 // 1| 2 // 1| 3 // 1| 5 // 1| 6 // // // // hope someone outthere can help me :) // // Henrik Pedersen // Demark // // -- SA, beyond.comThe world is watching America, pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <[EMAIL PROTECTED]> |Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE L and America is watching TV. __
[GENERAL] CORBA and PostgreSQL
Hello, I would like to know if there is a way to access a postgress database through a CORBA environment. Any suggestions for a further reading on this topic would be appreciated. Thanks. ---
[GENERAL] Problem with getting the right order
I have a datbase that look like this: vnr|cnr|enr| ip|dato ---+---+---+---+-- 1| 1| 17|195.192.136.223|1998-10-14 02:30:18+02 2| 1| 1|195.192.136.164|1998-10-14 02:33:03+02 5| 1| 13|195.192.136.173|1998-10-14 02:49:24+02 8| 1| 15|195.192.136.207|1998-10-14 02:49:27+02 9| 1| 16|195.192.136.211|1998-10-14 02:49:31+02 10| 1| 11|195.192.136.123|1998-10-14 02:49:44+02 11| 1| 15|195.192.136.162|1998-10-14 02:49:48+02 7| 1| 13|195.192.136.126|1998-10-14 02:49:50+02 Where vnr, cnr, enr are int fields, and ip, date are text. The database is used for a votingsystem. vnr is the vote number, cnr is the contestnumber and enr is the entrynumber. When i do a : ./psql -c "select count(vnr),enr from votes where cnr=1 group by enr" vote to get the result from contest number 1 i get : count|enr -+--- 11| 1 1| 2 1| 3 4| 4 1| 5 1| 6 10| 8 This is almost what i want, execpt that i want the result to be ordered by count, instead og enr. so that the entry, which got most votes is listed first For example: 11| 1 10| 8 4| 4 1| 2 1| 3 1| 5 1| 6 hope someone outthere can help me :) Henrik Pedersen Demark
Re: [GENERAL] datetime problems
At 17:48 +0200 on 21/10/98, Memphisto wrote: > Noted, thanks. I'm a newbie in postgreSQL and happy that these types and > functions exist, but I think the documentation is a bit spartan(lacks a > lot of pieces of information) and depend on those bits that are there in > the documentation. The correction for the subqueries is not specific to PostgreSQL. My guess is that you are not quite familiar with SQL in general. Perhaps you should consider buying a book. > Another question. Is there way to these truncation to weeks instead of > months. As far as I know, postgreSQL does not support it. Well, how does one truncate to weeks? Are Sundays in the next week or the last week? This differs from culture to culture. For some, Friday is the last day of the week... Let's rephrase the question. You want to know if date D1 is within the same week as date D2, given that a week starts on Sunday? Well, calculate the day of week for Date D1, by taking date_part( 'dow', D1 ). The result is an integer between 0 and 6. Subtract that number of days from D1: D1 - timespan( text( date_part( 'dow', D1 ) ) || ' days' ); You get the date of the Sunday on or before D1. Do the same for D2. You get the date of the Sunday on or before D2. Now see if you got the same date in both calculations... Intricate, but you can define this in an SQL function, and avoid queries which have too many parantheses for comfort. CREATE FUNCTION sunday_of_date( datetime ) RETURNS datetime AS 'SELECT $1 - timespan( text( date_part( ''dow'', $1 ) ) || '' days'' )' LANGUAGE 'sql'; (Can't test this myself, because I only have Postgres 6.3.1, which doesn't allow converting integer to text.) Now, your queries will be something like: SELECT * FROM annex_log WHERE sunday_of_date( login_start ) = sunday_of_date( 'now' ); If a week doesn't start on Sunday in your culture, you'll have to take the result of the dow, add the appropriate number of days and take the modulo of seven... Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
RE: [GENERAL] questions
> 2 questions. > > 1)Hi, I have this 2 tables: > esame=> select * from libro_autore\g > id_libro|id_autore > +- > s1 |5 > n3 |1 > n2 |2 > n1 |1 > n1 |4 > > and > esame=> select * from autore\g > cognome|nome |data_nascita|luogo_nascita|id_autore > ---+--++-+- > King |Stephen | 01-12-1940|NY |1 > Tamaro |Susanna | 15-02-1965|Milano |2 > Clancy |Tom | 05-05-1968|Los Angeles |3 > Straub |Peter | 01-01-1950|Milano |4 > Maraini|Dacia | 05-04-1962|Catania |5 > Zorza |Alessandra| 05-05-1974|Cernusco |6 > > Now I want to found Cognome, Nome and number of books foreach > "id_autore" > in > the libro_autore table. > I have think a SQL-statement like > > SELECT a.cognome, a.nome,la.id_autore, count(*) from libro_autore la, > autore a group by l > a.id_autore\g > I don't completely understand you question but this could be what you are looking for. SELECT a.cognome, a.nome, la.id_autore, count(la.id_libro) FROM libro_autore la, autore a WHERE la.id_autore = a.id_autore GROUP BY a.cognome, a.nome, la.id_autore; > (ok this is not correct). > Any ideas ?? > > 2)Now I have this table: > esame=> select * from view1\g > id_libro|id_utente|tipo > +-+ > n3 |9| 1 > (1 row) > > (for the moment only 1 item, but this table can contains more and more > items). > I want to find a way that return me for each "id_utente" the number of > books group by > "tipo". SELECT id_utente, tipo, count(id_libro) FROM view1 ORDER BY id_utente, tipo GROUP BY id_utente, tipo; > "Il divertimento e' giusto se la scimmia ci prende gusto" > -- > Italian Linux Meeting - http://www.pluto.linux.it/meeting/ > -- > Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group > [EMAIL PROTECTED] - http://ziobudda.enter.it/ > Linux Problem? Ask to [EMAIL PROTECTED] > "/dev/ziobudda: access to /var/tmp/beer denied, use > /var/adm/pineapple" >
Re: [GENERAL] datetime problems
> Probably because the parentheses are not balanced in the first query. But
> why the subqueries, anyway? Why not simply:
>
> SELECT * FROM annex_log
> WHERE login_start
> BETWEEN date_trunc('month','now'::datetime)
> AND ( date_trunc('month','now'::datetime) + '1 month'::timespan);
>
> (Note that I also removed the redundant type conversion you did on '1 month').
Noted, thanks. I'm a newbie in postgreSQL and happy that these types and
functions exist, but I think the documentation is a bit spartan(lacks a
lot of pieces of information) and depend on those bits that are there in
the documentation.
>
> Again, I recommend doing the >=, < thing rather than 'between', because
> 'between' will also allow the actual value of 1998-09-01 (for example) to
> be included. It's a close interval, rather than a half-open one.
That's right, I didn't know about the behaviour of 'between'. I'm going
to use '>=' and '<'.
Another question. Is there way to these truncation to weeks instead of
months. As far as I know, postgreSQL does not support it.
Sebestyén Zoltán AKA Memphisto It all seems so stupid,
it makes me want to give up.
[EMAIL PROTECTED]But why should I give up,
when it all seems so stupid?
MAKE INSTALL NOT WARAnd please avoid Necrosoft Widows
Re: [GENERAL] datetime problems
At 17:14 +0200 on 21/10/98, Memphisto wrote:
> Another problem. I issued the following query:
> select * from annex_log where login_start
> between
> (select date_trunc('month','now'::datetime)) and
> (select date_trunc('month','now'::datetime)) +
> timespan('1 month'::reltime);
>
> but postgresql said there's a parse error near select
> while the following query did work
>
> select * from annex_log where login_start >=
> (select date_trunc('month','now'::datetime)) and login_start <
> (select date_trunc('month','now'::datetime) +
> timespan('1 month'::reltime));
>
> Why?
Probably because the parentheses are not balanced in the first query. But
why the subqueries, anyway? Why not simply:
SELECT * FROM annex_log
WHERE login_start
BETWEEN date_trunc('month','now'::datetime)
AND ( date_trunc('month','now'::datetime) + '1 month'::timespan);
(Note that I also removed the redundant type conversion you did on '1 month').
Again, I recommend doing the >=, < thing rather than 'between', because
'between' will also allow the actual value of 1998-09-01 (for example) to
be included. It's a close interval, rather than a half-open one.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
Re: [GENERAL] datetime problems
On Wed, 21 Oct 1998, Herouth Maoz wrote:
> At 15:45 +0200 on 21/10/98, Memphisto wrote:
>
>
> > I'm terrible sorry, I meant to type login_start and no login_time which is
> > and intervallum. Thanks again for your help.
>
> By the way, I was thinking, and my WHERE clause may not catch datetimes
> which are on Aug 31, 1998, in the middle of the day. Better use:
>
> WHERE the_date >= '1998-08-01' AND the_date < '1998-09-01';
>
Thanks.
Another problem. I issued the following query:
select * from annex_log where login_start
between
(select date_trunc('month','now'::datetime)) and
(select date_trunc('month','now'::datetime)) +
timespan('1 month'::reltime);
but postgresql said there's a parse error near select
while the following query did work
select * from annex_log where login_start >=
(select date_trunc('month','now'::datetime)) and login_start <
(select date_trunc('month','now'::datetime) +
timespan('1 month'::reltime));
Why?
Sebestyén Zoltán AKA Memphisto It all seems so stupid,
it makes me want to give up.
[EMAIL PROTECTED]But why should I give up,
when it all seems so stupid?
MAKE INSTALL NOT WARAnd please avoid Necrosoft Widows
Re: [GENERAL] aggregate question
Hello Ulf, mercoledì, 21 ottobre 98, you wrote: UM> I posted the questions below a few days ago to the SQL list, but UM> apparently there is nobody there who can help -- maybe I'm luckier in UM> this list? :-) UM> -- UM> I'm a novice to postgresql, so please excuse me if I'm addressing to UM> the wrong mailing list! UM> I need to specify some column functions for statistical analysis, UM> e.g. standard deviation or variance. After reading the user's guide I UM> think that's to realize with aggregates, but with those docs only I UM> cannot figure out how to do it -- does anybody know whether there is a UM> ready-for-use "statistics" package, or how to write something that's UM> usable in a SQL statement like this: UM> select date, avg(temperature), stddev(temperature) UM> from temperatures ^^ UM> where date between '01.01.1999' and '15.02.1999' UM> group by date ? This query is OK, except for STDDEV function, you have to create this function, I think we don't have it on PostgreSQL. UM> Other question: I'm used to use DB2/2, and DB2/2 has functions like UM> "month(some_date)" and "hour(some_timestamp)". Are there equivalents UM> in postgresql? There's the SQL92 EXTRACT function... look, this is the actual date and time: prova=> select current_timestamp as today; today -- 1998-10-21 17:03:16+02 (1 row) How to display the month: prova=> select extract(month from current_date) as month; month - 10 (1 row) Unfortunately, for now, the EXTRACT function works only with DATE, DATETIME and TIMESPAN data types. You have to CAST a TIMESTAMP or a TIME as DATETIME to extract a field from it as in: prova=> select extract(hour from cast(current_timestamp as datetime)) as hour; hour 16 (1 row) Jose'
Re: [GENERAL] datetime problems
At 15:45 +0200 on 21/10/98, Memphisto wrote: > I'm terrible sorry, I meant to type login_start and no login_time which is > and intervallum. Thanks again for your help. By the way, I was thinking, and my WHERE clause may not catch datetimes which are on Aug 31, 1998, in the middle of the day. Better use: WHERE the_date >= '1998-08-01' AND the_date < '1998-09-01'; Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Re: [GENERAL] datetime problems
At 13:50 +0200 on 21/10/98, Memphisto wrote: > I'd like to issue a query that lists all the rows of a table whose 'date' > columns(its type is datetime) is falls in a month, i.e. these rows were > inserted in , say August. How can I do that? SELECT * FROM the_table WHERE the_date BETWEEN '1998-08-01' AND '1998-08-31'; Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
[GENERAL] That bug I mention in my prev. letter
I'm not sure if it's a real bug, it might be intentional, it's in connection with the daylights saving time. Sebestyén Zoltán AKA Memphisto It all seems so stupid, it makes me want to give up. [EMAIL PROTECTED]But why should I give up, when it all seems so stupid? MAKE INSTALL NOT WARAnd please avoid Necrosoft Widows
[GENERAL] datetime problems
I'd like to issue a query that lists all the rows of a table whose 'date'
columns(its type is datetime) is falls in a month, i.e. these rows were
inserted in , say August. How can I do that?
When I bugged with that problem I found an interesting bug:
elmu=> select date_trunc('month','now'::datetime);
date_trunc
-
Thu Oct 01 00:00:00 1998 CEST
(1 row)
That's OK.
elmu=> select date_trunc('month','now'::datetime) + timespan('1
month'::reltime)
;
?column?
Sat Oct 31 23:00:00 1998 CET
(1 row)
Why not Nov 1?
Sebestyén Zoltán AKA Memphisto It all seems so stupid,
it makes me want to give up.
[EMAIL PROTECTED]But why should I give up,
when it all seems so stupid?
MAKE INSTALL NOT WARAnd please avoid Necrosoft Widows
Re: [GENERAL] jdbc + postgres
On Tue, 20 Oct 1998, Peter T Mount wrote: > > Message:Connection failed: java.net.ConnectException: Connection refused > > This seems to be the most common problem ;-) > > You are not running the backend with the -i arg, which turns on tcp/ip. Thank you. Two characters more and everything works great. I need to come back to basic school ... > > Check the FAQ. Which one: java or postgres ? przemol
