[SQL] strange result for select * from table where id=random()*900
hi list, this is my query: select * from _g2977 where id=floor(random() * (900)); in table _g2977 i have id(s) from 1 up to 900. i just want to select one random dataset by the above query but i does not work. actually i get sometime zero, sometimes one, sometimes two and sometimes even three results back from the above query although i thought it should give only one random dataset from the table. what is wrong? i appreciate any help. thanks in advance, stefan _ In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten! Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] strange result for select * from table where id=random()*900
On 07/27/2007 09:48 AM, Stefan Zweig wrote: hi list, this is my query: select * from _g2977 where id=floor(random() * (900)); in table _g2977 i have id(s) from 1 up to 900. i just want to select one random dataset by the above query but i does not work. actually i get sometime zero, sometimes one, sometimes two and sometimes even three results back from the above query although i thought it should give only one random dataset from the table. random() isn't immutable, so it's re-calculated for every row. Try f.ex this instead: SELECT * FROm _g2977 ORDER BY random() LIMIT 1 -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] strange result for select * from table where id=random()*900
Στις Παρασκευή 27 Ιούλιος 2007 10:48, ο/η Stefan Zweig έγραψε: > hi list, > > this is my query: > > select > * > from _g2977 > where > id=floor(random() * (900)); > > in table _g2977 i have id(s) from 1 up to 900. i just want to select one > random dataset by the above query but i does not work. > > actually i get sometime zero, sometimes one, sometimes two and sometimes > even three results back from the above query although i thought it should > give only one random dataset from the table. > > what is wrong? A lot of things seem wierd: a) Why do you want such a query for? b) Is "id" a UNIQUE KEY? If yes, you should get ONE or ZERO rows from the above query. If not, you should get a number of rows according to the number of rows in the table with this specific "id" value. c) why returning (as you put it in the first place) sometimes 1,2,3 rows is different than returning a "random" dataset? If "id" was a PRIMARY KEY, maybe it would have sense to do smth like select * from table where id between floor(random() * 900) and floor(random() * 900); (note this might return zero rows as well). Note also that by random, we could very well mean the empty set, so there is no problem with the above. But, i feel from your text, that something is highly misunderstood, especially the way you set the question. > > i appreciate any help. > > thanks in advance, stefan > _ > In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten! > Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114 > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Achilleas Mantzios ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] calling webservice through postgresql function
Good day, Jyoti.
JS> we can use any of the "untrusted" procedural languages
JS> http://www.pgsql.cz/index.php/PL/Perlu_-_Untrusted_Perl_(en)
What !! There is no need to have
CREATE OR REPLACE FUNCTION
open FILE, $_[0]; my @cntn = ();
while () { }
__You as man (by hands) convert__ tree of data into database tables
by procedural language once again.
You are wrong: all, which is necessary, is to have __a.b.c__ in language for
JS> create table a (
JS> id num primary key,
JS> data float
JS> );
JS> create table b (
JS> id num primary key,
JS> ref num references a(id),
JS> data float
JS> );
JS> create table c (
JS> id num primary key,
JS> link num references b(id),
JS> data float
JS> );
JS> to access resources outside the database
JS> (e.g. pl/perlu).
There is no need to have
CREATE OR REPLACE FUNCTION
use DBI;
my $dbh = DBI->connect('dbi:mysql:'.$_[0],$_[1],$_[2],
{ RaiseError => 1, AutoCommit = > });
__You as man (by hands) install connect__ between DBMS and external world
by procedural language once again.
You are wrong once again:
advantage is to __refuse__ from procedural language (to install connect)
instead of put it from outside of DBMS into inside of DBMS.
All, which is necessary, is DBMS itself listen some port/protocol.
Dmitry Turin
SQL4 (4.1.3) http://sql40.chat.ru
HTML6 (6.3.0) http://html6.by.ru
Unicode2 (2.0.0) http://unicode2.chat.ru
Computer2 (2.0.3) http://computer20.chat.ru
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] problem join
chester c young escreveu: having problem joining these correctly: schedule - cal_id references calendar not null - usr_id references users not null = unique( calZ_id, usr_id ) - result_no not null activity - cal_id references calendar not null - usr_id references users not null = unique( cal_id, usr_id ) - from_ts timestamp not null trying to join - cal_id - usr_id - schedule.result_no (might be null) - activity.from_ts (might be null) where schedule.usr_id = activity.usr_id would result in one row Try: SELECT * FROM schedule FULL OUTER JOIN activity USING (cal_id, usr_id) Osvaldo ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] strange result for select * from table where id=random()*900
On 27.07.2007, at 10:48, Stefan Zweig wrote: hi list, this is my query: select * from _g2977 where id=floor(random() * (900)); in table _g2977 i have id(s) from 1 up to 900. i just want to select one random dataset by the above query but i does not work. actually i get sometime zero, sometimes one, sometimes two and sometimes even three results back from the above query although i thought it should give only one random dataset from the table. what is wrong? i appreciate any help. thanks in advance, stefan __ ___ In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und gestalten! Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings SELECT * FROM _g2977 g, (SELECT floor(random() * (900) as rnd) r WHERE g.id = r.rnd should work better, haven't tested though. Kristo Kaiv http://kaiv.wordpress.com (PostgreSQL blog)
Re: [SQL] group by range of values
Carol Cheung wrote: Hello, Here's my table: db=# select * from tester order by birth_year; birth_year | salary + 1946 | 78000 1949 | 61000 1951 | 58000 1953 | 56000 1958 | 52000 1962 | 5 1965 | 45000 1967 | 6 1968 | 57000 1970 | 47000 1972 | 32000 1973 | 42000 (12 rows) How can I display the average salary grouped by decade of birth year? That is, is it possible to display the average salary of those born in the 1940's, the average salary of those born in the 1950's, average salary of those born in the 1960's, and those born in the 1970's, all in one result table? Something like: decade | average(salary) ---+- 1940 | 69500 1950 | 5.33 1960 | 53000 1970 | 40333.33 Assuming birth_year is an integer (if it's not, then just change the query to cast it to one before the division), one possible approach might be: select birth_year / 10 || '0' as decade, avg(salary::numeric) as average_salary from tester group by decade order by decade asc; -Jon -- Senior Systems Developer Media Matters for America http://mediamatters.org/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] group by range of values
Hello, Here's my table: db=# select * from tester order by birth_year; birth_year | salary + 1946 | 78000 1949 | 61000 1951 | 58000 1953 | 56000 1958 | 52000 1962 | 5 1965 | 45000 1967 | 6 1968 | 57000 1970 | 47000 1972 | 32000 1973 | 42000 (12 rows) How can I display the average salary grouped by decade of birth year? That is, is it possible to display the average salary of those born in the 1940's, the average salary of those born in the 1950's, average salary of those born in the 1960's, and those born in the 1970's, all in one result table? Something like: decade | average(salary) ---+- 1940 | 69500 1950 | 5.33 1960 | 53000 1970 | 40333.33 Thanks in advance, C ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] group by range of values
On 7/27/07, I wrote: > On 7/27/07, Carol Cheung <[EMAIL PROTECTED]> wrote: > > Something like: > > > > decade | average(salary) > > ---+- > >1940 | 69500 > >1950 | 5.33 > >1960 | 53000 > >1970 | 40333.33 > > CREATE TABLE tester ( > birth_year integer, > salary numeric(10,2) > ); > > SELECT > SUBSTRING(TO_CHAR(BIRTH_YEAR,'FM') FROM 1 FOR 3)||'0' > AS DECADE > , AVG(SALARY) AS AVG_SALARY > FROM TESTER > GROUP BY > SUBSTRING(TO_CHAR(BIRTH_YEAR,'FM') FROM 1 FOR 3)||'0' > ORDER BY DECADE; A bit simpler, if year is int: SELECT BIRTH_YEAR/10*10 AS DECADE , AVG(SALARY) AS AVG_SALARY FROM TESTER GROUP BY BIRTH_YEAR/10*10 ORDER BY DECADE; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] group by range of values
On 7/27/07, Carol Cheung <[EMAIL PROTECTED]> wrote: > Something like: > > decade | average(salary) > ---+- >1940 | 69500 >1950 | 5.33 >1960 | 53000 >1970 | 40333.33 CREATE TABLE tester ( birth_year integer, salary numeric(10,2) ); SELECT SUBSTRING(TO_CHAR(BIRTH_YEAR,'FM') FROM 1 FOR 3)||'0' AS DECADE , AVG(SALARY) AS AVG_SALARY FROM TESTER GROUP BY SUBSTRING(TO_CHAR(BIRTH_YEAR,'FM') FROM 1 FOR 3)||'0' ORDER BY DECADE; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] group by range of values
Hello you can use CASE like SELECT CASE WHEN birth_year BETWEEN 1940 AND 1949 THEN 1940 WHEN birth_year BETWEEN 1950 AND 1959 THEN 1950 WHEN birth_year BETWEEN 1960 AND 1969 THEN 1960 WHEN birth_year BETWEEN 1970 AND 1979 THEN 1979 END, AVG(salary) FROM tester GROUP BY 1 ORDER BY 1; Regards Pavel Stehule 2007/7/27, Carol Cheung <[EMAIL PROTECTED]>: > Hello, > > Here's my table: > > db=# select * from tester order by birth_year; > birth_year | salary > + > 1946 | 78000 > 1949 | 61000 > 1951 | 58000 > 1953 | 56000 > 1958 | 52000 > 1962 | 5 > 1965 | 45000 > 1967 | 6 > 1968 | 57000 > 1970 | 47000 > 1972 | 32000 > 1973 | 42000 > (12 rows) > > How can I display the average salary grouped by decade of birth year? > That is, is it possible to display the average salary of those born in > the 1940's, the average salary of those born in the 1950's, average > salary of those born in the 1960's, and those born in the 1970's, all in > one result table? > Something like: > > decade | average(salary) > ---+- >1940 | 69500 >1950 | 5.33 >1960 | 53000 >1970 | 40333.33 > > Thanks in advance, > C > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] group by range of values
2007/7/27, Carol Cheung <[EMAIL PROTECTED]>:
db=# select * from tester order by birth_year;
birth_year | salary
+
1946 | 78000
1949 | 61000
What is the data type of the birth_year column? I'd suggest using
date if you can, as what it is is a date with year precision. You
can't specify such a precision, but you can decide that all
birth_year's will have month and year of January 1 (enforced by a
CHECK constraint, if you wish), or you could just choose to ignore
the month and year part in your calculations.
How can I display the average salary grouped by decade of birth year?
That is, is it possible to display the average salary of those born in
the 1940's, the average salary of those born in the 1950's, average
salary of those born in the 1960's, and those born in the 1970's,
all in
one result table?
Something like:
decade | average(salary)
---+-
1940 | 69500
1950 | 5.33
1960 | 53000
1970 | 40333.33
Here's an example:
CREATE TABLE salaries (birth_year DATE PRIMARY KEY, salary NUMERIC
NOT NULL);
INSERT INTO salaries (birth_year, salary) VALUES
('1946-01-01',78000), ('1949-01-01',61000), ('1951-01-01',58000)
, ('1953-01-01',56000), ('1958-01-01',52000), ('1962-01-01',5)
, ('1965-01-01',45000), ('1967-01-01',6), ('1968-01-01',57000)
, ('1970-01-01',47000), ('1972-01-01',32000), ('1973-01-01',42000);
SELECT birth_decade, AVG(salary)
FROM (
SELECT birth_year
, date_trunc('decade', birth_year)::date as birth_decade
, salary
FROM salaries) as salaries_with_decades
GROUP BY birth_decade
ORDER BY birth_decade;
birth_decade |avg
--+
1940-01-01 | 69500.
1950-01-01 | 55333.
1960-01-01 | 53000.
1970-01-01 | 40333.
(4 rows)
If birth_year is an integer column, here's another way to do it,
taking advantage of the fact that integer division truncates.
CREATE TABLE salaries (birth_year INTEGER PRIMARY KEY, salary NUMERIC
NOT NULL);
INSERT INTO salaries (birth_year, salary) VALUES
(1946,78000), (1949,61000), (1951,58000), (1953,56000),
(1958,52000)
, (1962,5), (1965,45000), (1967,6), (1968,57000),
(1970,47000)
, (1972,32000), (1973,42000);
SELECT birth_decade, AVG(salary)
FROM (
SELECT birth_year
, birth_year / 10 * 10 as birth_decade
, salary
FROM salaries) as salaries_with_decades
GROUP BY birth_decade
ORDER BY birth_decade;
birth_decade |avg
--+
1940 | 69500.
1950 | 55333.
1960 | 53000.
1970 | 40333.
(4 rows)
Hope this gives you some options.
Michael Glaesemann
grzm seespotcode net
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
