Remove
-----Original Message-----
From: owner-pgsql-sql-digest@hub.org
[mailto:owner-pgsql-sql-digest@hub.org]
Sent: Sunday, August 22, 1999 9:01 PM
To: pgsql-sql-digest@hub.org
Subject: pgsql-sql-digest V1 #328
pgsql-sql-digest Sunday, August 22 1999 Volume 01 : Number 328
Index:
Re: [SQL] Counting the number of distinct rows returned
Re: [SQL] What JDBC datatype can be used for DATETIME ?
----------------------------------------------------------------------
Date: Sun, 22 Aug 1999 15:44:37 +0300
From: Herouth Maoz <herouth@oumail.openu.ac.il>
Subject: Re: [SQL] Counting the number of distinct rows returned
At 16:56 +0300 on 19/08/1999, Sejin Oh wrote:
> yeah... GROUP BY should do..
>
> try this:
>
> SELECT COUNT(*) FROM sales_by_region GROUP BY user_id;
No, no, it returns the exact opposite of what he wanted. Imagine a table
that has the values
fld
===
2
1
2
1
3
Doing select distinct on this table would return
fld
===
2
1
3
Therefore the number Drew needed is 3. But SELECT COUNT(*) with GROUP BY
would give:
fld count
=== =====
2 2
1 2
3 1
That is, for each distinct value, it will give the frequency of that value
in the table. This is not the number of distinct values, it is the number
of times each distinct value appears.
I have once already posted a (kinda kludgy) solution for this in the
absence of COUNT DISTINCT in PostgreSQL:
SELECT count(*)
FROM the_table t1
WHERE int4( oid ) =
( SELECT max( int4(oid) ) FROM the_table t2 WHERE t1.fld = t2.fld );
The inner select gives you the maximal oid of the row that has a given
field value. The outer select selects only rows whose oid is this maximal
one. Thus it selects only one row for each distinct value, and so the count
is COUNT DISTINCT. If the original SELECT DISTINCT selected several fields,
more fields should be added to the WHERE clause of the subselect.
A more efficient solution would probably be:
SELECT DISTINCT( fld )
INTO TABLE tmp_tbl
FROM the_table;
SELECT COUNT( * ) -- This is the query that returns the needed result
FROM tmp_tbl;
DROP TABLE tmp_tbl;
Herouth
- --
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
------------------------------
Date: Sun, 22 Aug 1999 15:22:39 +0300
From: Herouth Maoz <herouth@oumail.openu.ac.il>
Subject: Re: [SQL] What JDBC datatype can be used for DATETIME ?
At 18:42 +0300 on 19/08/1999, Jon Windfeld Bundesen wrote:
>
> When i try using ResultSet.getTimestamp() instead, i also get an error:
> java.lang.NumberFormatException: 1999 CEST
> at java.lang.Float.<init>(Float.java)
> at postgresql.ResultSet.getTimestamp(ResultSet.java:455)
> at TestDate.main(TestDate.java:32)
>
> I CAN use getString(), but i'd like to get a real Date object, not just
> a String. Can anyone help ?
Basically, Java recognises the string structure sent to it by PostgreSQL's
timestamp. Since the string structure sent by datetime is different by
default, it fails to interpret is as a datetime:
testing=> SELECT '22-aug-1999 15:10'::timestamp;
?column?
- ----------------------
1999-08-22 15:10:00+03
(1 row)
testing=> SELECT '22-aug-1999 15:10'::datetime;
?column?
- ----------------------------
Sun Aug 22 15:10:00 1999 IDT
(1 row)
The former is the ISO style. One may change the datestyle to get the same
result out of datetime, but this is not recommended within the context of
JDBC, because the JDBC interpretation functions set it themselves, if I'm
not mistaken:
testing=> SET DATESTYLE='iso';
SET VARIABLE
testing=> SELECT '22-aug-1999 15:10'::datetime;
?column?
- ----------------------
1999-08-22 15:10:00+03
(1 row)
Another solution that achieves the same goal is to make sure all the fields
you select from your tables are in timestamp rather than datetime. Either
have that in your table (but this has a problem with indexing), or just
change your selects. Instead of
SELECT dt FROM foo;
Use
SELECT timestamp( dt ) FROM foo;
In 6.4 there is a bug in this, so you can define your own function for the
conversion. Or you can use abstime(dt) instead. The conversion function
would be:
testing=> CREATE FUNCTION dt_timestamp( datetime ) RETURNS timestamp
testing-> AS 'SELECT timestamp_in( datetime_out( $1 ) )
testing'> WHERE $1 IS NOT NULL'
testing-> LANGUAGE 'sql';
Herouth
- --
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
------------------------------
End of pgsql-sql-digest V1 #328
*******************************
************
