Hi all
My table definition :
id | fref | mref
--+---+--
1 | 23 | 25
2 | 24 | 28
3 | 25 | 31
4 | 26 | 34
My problem :
i need a query that results in this :
id | ref
--+--
1 | 23
1
My table definition :
id | fref | mref
--+---+--
1 | 23| 25
2 | 24| 28
3 | 25| 31
4 | 26| 34
i need a query that results in this :
id |ref
--+--
1 | 23
1 | 25
2 | 24
2 | 28
Hi,
i have a table
ProdId | LastUpdate
---+
100| 2005-04-01
100| 2005-03-01
100| 2005-02-01
200| 2005-04-01
200| 2005-03-01
200| 2005-02-01
- How can i select only the newest record for each ProdId ?
100| 2005-04-01
200| 2005-04-01
- How can i select
Hello SQL Aces !
I want to do a select on a table distinct on linkid and sorted by
date. I have try this
SELECT DISTINCT ON (linkid) * FROM all_links
WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
TIMESTAMP '2005-06-01') ORDER BY linkid, read_date;
With this req
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Here's a question for the SQL guru's out there, which I've been trying to
solve for the last couple of hours. There's got to be a solution to this, but
somehow I can't find it.
Tables:
table1 (
uid int PK,
uname varchar(64)
)
tabl
Hi List;
I have a table that has 3 date columns :
create table xyz (
xyz_id integer,
date1 timestamp,
date2 timestamp,
date3 timestamp
)
I want to select in a query the xyz_id and the max date column for
each row
something like :
create table temp2 as select xyz_id (max date?) where .
I am using RH5.2 / Postgre 6.3.2 I need a query that has a having
clause. In 6.3.2 it says that having is not supported yet. I looked
at the changes in 6.4 and it appears that 6.4.2 supports this. Is this
true? Also I have found 6.4.2 rpms but no data rpm so now when I try to
use psql it say
I have a sql problem which I can't solve. The following table is defined
create table AdressGroup
(
AdrGroup_Id INTEGER NOT NULL
DEFAULT NEXTVAL('adrverw_id_seq'),
ZeitDATETIME NOT NULL,
Group_IdI
Please help, I am trying to write an SQL statement but with no success as I am just
starting out with sql.
I have a table with 3 columns: Account# ,OrderType and date
example of data:
Account#¦ Ordertype ¦ Date
1 ¦ A ¦ April
1 ¦ B ¦ May
1
: [GENERAL] sql question
Hi all My table definition : id
| fref |
mref--+---+-- 1
| 23 | 25
2 | 24 |
28 3 | 25
| 31 4 |
26 | 34My problem : i need a
query that results in this : id |
ref
On Fri, 11 Mar 2005 11:36 pm, Steven Verhoeven wrote:
> Hi all
>
> My table definition :
>
>id | fref | mref
> --+---+--
> 1 | 23| 25
> 2 | 24| 28
> 3 | 25| 31
> 4 | 26| 34
>
> My problem :
> i need a query that
On Fri, 11 Mar 2005 13:36:17 +0100, Steven Verhoeven
<[EMAIL PROTECTED]> wrote:
> Hi all
>
> My table definition :
>
>id | fref | mref
> --+---+--
> 1 | 23| 25
> 2 | 24| 28
> 3 | 25| 31
> 4 | 26| 34
>
> My probl
On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven
<[EMAIL PROTECTED]> wrote:
> My table definition :
>
>id | fref | mref
> --+---+--
> 1 | 23| 25
> 2 | 24| 28
> 3 | 25| 31
> 4 | 26| 34
>
> i need a query that
On Sun, 2005-03-13 at 23:13 -0600, George Essig wrote:
> On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven
> <[EMAIL PROTECTED]> wrote:
> [snip problem]
> select id, fref as ref from my_table
> union
> select id, mref as ref from my_table;
union ALL
(see other replies)
gnari
Steven Verhoeven wrote:
Hi all
My table definition :
id | fref | mref
--+---+--
1 | 23| 25
2 | 24| 28
3 | 25| 31
4 | 26| 34
My problem :
i need a query that results in this :
id |ref
--+--
1 |
On 15.04.2005 13:58 Alex wrote:
> Hi,
>
> i have a table
>
> ProdId | LastUpdate
> ---+
> 100| 2005-04-01
> 100| 2005-03-01
> 100| 2005-02-01
> 200| 2005-04-01
> 200| 2005-03-01
> 200| 2005-02-01
>
> - How can i select only the newest record for each Prod
from test."tableProd" t
where u."ProdId" = t."ProdId")
-Message d'origine-
De : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] la part de Alex
Envoyé : vendredi 15 avril 2005 13:59
À : pgsql-general@postgresql.org
Objet : [GENERAL] SQL Question
GIROIRE, Nicolas (COFRAMI) wrote:
For the first request (How can i select only the newest record for each ProdId
?), you can do :
select * from test."tableProd" u
where u."LastUpdate" = (select max(t."LastUpdate")
from test."tableProd" t
where u."ProdId" = t."ProdId")
Although this onl
005 15:42
À : GIROIRE, Nicolas (COFRAMI)
Cc : Alex; pgsql-general@postgresql.org
Objet : Re: [GENERAL] SQL Question
GIROIRE, Nicolas (COFRAMI) wrote:
> For the first request (How can i select only the newest record for each
> ProdId ?), you can do :
>
> select * from test."tableProd&
select max(lastupdate),prodid
from tablename
group by prodid
-Original Message-
From: Thomas Kellerer <[EMAIL PROTECTED]>
Subj: Re: [GENERAL] SQL Question
Date: Fri Apr 15, 2005 7:24 am
Size: 621 bytes
To: pgsql-general@postgresql.org
On 15.04.2005 13:58 Alex wrote:
> H
[EMAIL PROTECTED] wrote on 15.04.2005 16:42:
select max(lastupdate),prodid
from tablename
group by prodid
Even better :)
Thomas
---(end of broadcast)---
TIP 8: explain analyze is your friend
On Fri, Apr 15, 2005 at 21:58:31 +1000,
Alex <[EMAIL PROTECTED]> wrote:
> Hi,
>
> i have a table
>
> ProdId | LastUpdate
> ---+
> 100| 2005-04-01
> 100| 2005-03-01
> 100| 2005-02-01
> 200| 2005-04-01
> 200| 2005-03-01
> 200| 2005-02-01
>
> - How can i se
From: "Alex" <[EMAIL PROTECTED]>
- How can i select only the newest record for each ProdId ?
100| 2005-04-01
200| 2005-04-01
DISTINCT ON was made for this and on the similar tables I have performs
rather more efficiently than using a subquery.
select distinct on (ProdId) ProdId , LastUpda
Julian Scarfe wrote:
From: "Alex" <[EMAIL PROTECTED]>
- How can i select only the newest record for each ProdId ?
100| 2005-04-01
200| 2005-04-01
DISTINCT ON was made for this and on the similar tables I have
performs rather more efficiently than using a subquery.
select distinct on (Prod
nal Message ---
From: FC <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: Wed, 1 Jun 2005 16:40:48 +0200
Subject: [GENERAL] SQL question.
> Hello SQL Aces !
>
> I want to do a select on a table distinct on linkid and sorted by
> date. I have try this
>
> SEL
On Wed, Jun 01, 2005 at 04:40:48PM +0200, FC wrote:
>
> Hello SQL Aces !
>
> I want to do a select on a table distinct on linkid and sorted by
> date. I have try this
How about a subquery?:
SELECT * FROM
( SELECT DISTINCT ON (linkid) * FROM all_links
WHERE uid='2' AND DATE_TRUNC('day',rea
ay',TIMESTAMP '2005-06-01')
ORDER BY linkid
) A
ORDER BY read_date DESC limit 100
-- Original Message ---
From: FC <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: Wed, 1 Jun 2005 16:40:48 +0200
Subject: [GENERAL] SQL question.
Hello SQL Aces !
The issue has been solved thanks to a custom
nullif_int() function. Which if anyone has the same
issue, it was solved with...
CREATE FUNCTION nullif_int(text) RETURNS integer AS
'SELECT nullif($1,)::int;' LANGUAGE SQL;
SELECTS were not the issue; INSERT INTO a non-text
column was the issue.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
If someone knows this it would be great - because I'm still curious how to
solve it. However I just remodelled my db structure to eliminate the problem
(basically I pulled the several tables into one since each of the
table2/table3 tables only has
l I can do for tonight.
Vincent
- Original Message -
From: "Uwe C. Schroeder" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, November 06, 2004 3:13 PM
Subject: [GENERAL] SQL question
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Here's a questio
"Uwe C. Schroeder" <[EMAIL PROTECTED]> writes:
> Here's a question for the SQL guru's out there, which I've been trying to
> solve for the last couple of hours. There's got to be a solution to this, but
> somehow I can't find it.
>
> Tables:
>
> table1 (
> uid int PK,
> uname var
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Thanks for your answers Greg & Vincent.
Although I solved the problem by a change of schema - I'm happy that I have
something to digest I didn't know before. One never learns enough ...
U.C.
On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder
> I have a table that has 3 date columns :
>
> create table xyz (
> xyz_id integer,
> date1 timestamp,
> date2 timestamp,
> date3 timestamp
> )
>
>
> I want to select in a query the xyz_id and the max date column for
> each row
> something like :
> create table temp2 as select xyz_id (max
Hi -
Was wondering if anyone could help / had some thoughts. I am building
a model for a client, and right now doing customer attrition
modeling. Basically, the number of customers in this period is equal
to:
Beg # Customers
+ customers added this period
- attrition
Ending # Customers
Obviousl
PROTECTED]
Subject: [GENERAL] SQL Question
I am using RH5.2 / Postgre 6.3.2 I need a query that has a having
clause. In 6.3.2 it says that having is not supported yet. I
looked
at the changes in 6.4 and it appears that 6.4.2 supports this. Is
this
true? A
[EMAIL PROTECTED] wrote:
> > > SELECT * FROM adressGroup
> > > WHERE Group_Id = 6
> > > AND EXISTS( SELECT AdrGroup_Id FROM adressGroup ag_alias
> > > WHERE adressGroup.AdrGroup_Id = ag_alias.AdrGroup_Id
> > > GROUP BY AdrGroup_Id HAVING COUNT(*) = 1);
>
>
On Wed, Jul 12, 2000 at 05:33:09PM -0500, Travis Bauer wrote:
>
> Let's say I have a table t1 with two fields, x and y. How do I write an
> sql statement like:
>
> select x if y>1 else 0 from t1;
SELECT CASE WHEN y>1 THEN x ELSE 0 END FROM t1;
>From page 33 of Bruce's book, at:
http://www.po
Thanks,
Now that you mention it, I even remember reading it in the book!
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
On Wed, 12 Jul 2000, Ross J
13, 2005 6:34
PM
Subject: Re: [NOVICE] [GENERAL] sql
question
SELECT t1.id
, t1.fref
FROM t1
UNION ALL
SELECT t2.id
, t2.mref
FROM t2
- Original Message -
From:
Steven Verhoeven
To: pgsql-general
ktr73 wrote:
Hi -
Was wondering if anyone could help / had some thoughts. I am building
a model for a client, and right now doing customer attrition
modeling. Basically, the number of customers in this period is equal
to:
Beg # Customers
+ customers added this period
- attrition
Ending # Cust
Hi,
I have a question about using Group By.
On a table like this:
Type (varchar) | Active (boolean)
Type One | False
Type Two | True
Type One | True
Type Fifty | Flase
Typ
Emil Rachovsky wrote:
>
> Hi,
> Can anyone show me a simple way of creating an index
> in PostGre like that:
> create index indName on someTable(someIntColumn DESC)
> ?
Not using that particular syntax, but you can do that if you create the
appropiate operator classes. Note that if you want to u
OK, this does not work:
select max(t1."When"), t1."Pt_Id", t2."DateOfBirth"
from "PtStaffAccess" t1, "Person" t2
where t1."Pt_Id" = t2.id
group by t1."Pt_Id";
But this does:
select max(t1."When"), t1."Pt_Id", min(t2."DateOfBirth")
from "PtStaffAccess" t1, "Person" t2
where t1."Pt_Id" = t2.id
gro
You could use COUNT() in conjunction with NULLIF:
select "Type",
count(nullif("Active", false)) as "Active Count",
count(nullif("Active", true)) as "Inactive Count",
100 * count(nullif("Active", false)) / count(*) as "Active Percent"
from table_name group by "Type"
On Feb 23, 2:50 pm, "Mike" <[E
Thank you! Exactly what I needed.
Mike
On Feb 23, 4:42 pm, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> You could use COUNT() in conjunction with NULLIF:
>
> select "Type",
> count(nullif("Active", false)) as "Active Count",
> count(nullif("Active", true)) as "Inactive Count",
> 100 * count(nu
Hi,
I have a question about using Group By.
On a table like this:
Type (varchar) | Active (boolean)
Type One | False
Type Two | True
Type One | True
Type Fifty | Flase
[EMAIL PROTECTED] wrote:
> You could use COUNT() in conjunction with NULLIF:
>
> select "Type",
> count(nullif("Active", false)) as "Active Count",
> count(nullif("Active", true)) as "Inactive Count",
> 100 * count(nullif("Active", false)) / count(*) as "Active Percent"
> from table_name group by
Hi all,
Given the following tables -
create table people (
person_id text primary key,
person_name text,
[...etc...]
);
create table items (
item_id text primary key,
item_name text,
is_required boolean,
[...etc...]
);
create table items_for_people (
p
On 28 January 2010 21:32, Scott Ribe wrote:
> OK, this does not work:
>
> select max(t1."When"), t1."Pt_Id", t2."DateOfBirth"
> from "PtStaffAccess" t1, "Person" t2
> where t1."Pt_Id" = t2.id
> group by t1."Pt_Id";
>
> But this does:
>
> select max(t1."When"), t1."Pt_Id", min(t2."DateOfBirth")
>
> You can't include an aggregate in the select if you don't group by
> non-aggregates, so it should be:
>
> select max(t1."When"), t1."Pt_Id", t2."DateOfBirth"
> from "PtStaffAccess" t1, "Person" t2
> where t1."Pt_Id" = t2.id
> group by t1."Pt_Id", t2."DateOfBirth";
I was aware that I could alter
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Scott Ribe
> Sent: Thursday, January 28, 2010 2:10 PM
> To: Thom Brown
> Cc: pgsql-general
> Subject: Re: [GENERAL] SQL question re aggregat
Scott Ribe writes:
> Given that t2.id is the primary key, grouping by any other column of t2 is
> really redundant. I know *what* SQL won't allow me to do, I'm interested in
> knowing if there's some reason *why* other than historical...
SQL92 says so. More recent versions of the SQL spec descri
On Mon, Oct 30, 2000 at 07:59:06PM -0500, Keith L. Musser wrote:
> "(SELECT messages.msgid FROM messages, subject_index WHERE
> ((subject_index.word='Hello' or subject_index.word='There') and
> (subject_index.msgid = messages.msgid))
> GROUP BY messages.msgid HAVING count(messages.msgid)=2)
> INTE
d 'Jones'.)
So I cannot remove either having clause without changing the meaning.
What I would really like to know is why INTERSECT does not allow this.
If I understand that, maybe I can figure out how to get what I need.
-Original Message-
From: Igor Roboul <[EMAIL PROTEC
On Thu, Nov 02, 2000 at 06:50:07AM -0500, Keith L. Musser wrote:
> > upper(a.word) = 'JIM' or upper(a.word) = 'JONES'
> > upper(s.word) = 'HELLO' or upper(s.word) = 'THERE'
> Interesting possibility. Unfortunately, the outer select ends up being
> a sequential scan over the entire messages table,
On 8/10/07, Raymond O'Donnell <[EMAIL PROTECTED]> wrote:
> - how can I find those people who don't have _all_ of the items which
> are marked "required"?
>
> In other words, how do I select those rows in "people" which don't have
> a corresponding row in "items_for_people" for *each* row in "items"
On 10/08/2007 21:29, Scott Marlowe wrote:
select table1.id from table1 where table1.id is not in (select id from table2);
Duh! I should have thought of that thanks for that, and apologies
for the stupidity (blame it on the glass of wine I had with dinner!).
Ray.
--
On 8/10/07, Raymond O'Donnell <[EMAIL PROTECTED]> wrote:
> On 10/08/2007 21:29, Scott Marlowe wrote:
>
>
> > select table1.id from table1 where table1.id is not in (select id from
> > table2);
>
> Duh! I should have thought of that thanks for that, and apologies
> for the stupidity (blame it o
On 10/08/2007 22:03, Carlos Ortíz wrote:
Select * from people where person_id in (
Select person_ID from Items_for_people group by Person_id Having Count(*)
= (
Select count(*) from Items Where is_required = true))
That seems to work fine! I'd only change "having count(*) = .
On 10/08/2007 21:42, Scott Marlowe wrote:
Show us the query when you're done, I'm sure there are enough folks
who'd like to see your solution.
Here's what I came up with:
select distinct ip.person_id from items_for_people ip
where exists (
(
select item_id from items
where
#x27;s not correct, but I'm new in postgresql. In sql server
it's ok)
De: [EMAIL PROTECTED] en nombre de Raymond O'Donnell
Enviado el: Vie 10/08/2007 03:07 p.m.
Para: 'PostgreSQL'
Asunto: [GENERAL] SQL question: checking all required it
I have a SQL statement that uses group-by to generate reports as such:
GroupFieldClicks
---
Page 1 50
Page 2 20
Page 3 30
Now, as an added field, I also want it to tell me what percent of
total clicks each row represe
On Mon, Mar 12, 2007 at 12:53:11 -0700,
Mike <[EMAIL PROTECTED]> wrote:
>
> How do I get access to the total of all clicks on per row basis so I
> can divide it? The only solution that comes to my mind is create a
> subquery that does a (select count(*) from... where... ) of the
> original group
hi, there. i'm trying to write a SQL statement which does the following
things.
1. checks if data already exists in the database
2. if not, insert data into database otherwise skip.
for example, i'd like to insert a student called 'Michael Jordan' whose
ID is 'JORDANMICHAEL' only if the id, 'JORD
nuno wrote:
>hi, there. i'm trying to write a SQL statement which does the following
>things.
>
>1. checks if data already exists in the database
>2. if not, insert data into database otherwise skip.
>
>
>
Check this thread :
http://archives.postgresql.org/pgsql-general/2005-10/msg01787.php
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
Im building an user database with many tables keeping the data for the
Address, Phone numbers, etc which are referenced by a table where I keep
the single users. My question is, how do I get the "Id"-value of a newly
inserted address to store it i
On Wed, Jun 21, 2006 at 10:59:42PM -0700, nuno wrote:
> insert into student (studentid, fname, lname)
> select 'JORDANMICHAEL', 'Michale', 'Jordan' from student where
> studentid not in
> (select studentid from student);
>
> however, this does not seem to work. it does not insert data even if it
>
nuno wrote:
> hi, there. i'm trying to write a SQL statement which does the
> following things.
>
> 1. checks if data already exists in the database
> 2. if not, insert data into database otherwise skip.
>
> for example, i'd like to insert a student called 'Michael Jordan'
> whose ID is 'JORDANM
After you've done the insert on the address table, you can use
currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have
to have used nextval() for the original insert.
Hope this helps,
On Sun, Nov 09, 2003 at 11:29:49AM +0100, Andreas Fromm wrote:
> Hi,
>
> Im building an user
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Martijn van Oosterhout wrote:
> After you've done the insert on the address table, you can use
> currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have
> to have used nextval() for the original insert.
>
> Hope this helps,
>
..goi
On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
> After you've done the insert on the address table, you can use
> currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you
> have to have used nextval() for the original insert.
What if someone else inserts another addre
Scott Chapman <[EMAIL PROTECTED]> writes:
> On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
> > After you've done the insert on the address table, you can use
> > currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you
> > have to have used nextval() for the original
On Sun, Nov 09, 2003 at 10:26:51AM -0800, Scott Chapman wrote:
> On Sunday 09 November 2003 03:13, Martijn van Oosterhout wrote:
> > After you've done the insert on the address table, you can use
> > currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you
> > have to have used nextval
On Mon, Nov 10, 2003 at 08:09:29AM -0800, Scott Chapman wrote:
> Chronological events here:
>
> X inserts a new record into A.
> Y inserts a new record into A.
> X fetches currval of the SA. What value does X get in this case, the one
> from X's insert or Y's?
X's.
--
Alvaro Herrera ()
"¿Qué
On Monday 10 November 2003 08:23, David Green wrote:
> Are X & Y two different connections?
> If you execute 2 statements on the same connection and then get
> currval() it will give the last generated id.
>
> Ex.
> On 1 connection:
> INSERT INTO A (fld) VALUES (val); -- id generated = 1
> INSERT I
I saw this method of Statement class in jdbc.
Will the return int contain the autogenerated key value ??
public int executeUpdate(String sql,
int autoGeneratedKeys)
throws SQLException
thanks,
kathy
Scott Chapman wrote:
> On Monday 10 November 2003 08
On Mon, Nov 10, 2003 at 08:56:03AM -0800, Scott Chapman wrote:
> I talked with the author or SQLObject about this recently and I thnk
> he's implementing this correctly, by querying the cursor for the last
> OID?:
That won't scale unless you index oid. And your tables will all need
oids, which
"scott.marlowe" <[EMAIL PROTECTED]> writes:
> select tablename.fieldname.currval;
That syntax would be problematic, it would mean to select all rows from
tablename and evaluate fieldname.currval for each one. Actually it's worse, it
would be confused with schemas I think.
The postgres-ish way t
Scott Chapman <[EMAIL PROTECTED]> writes:
> On Wednesday 12 November 2003 11:29, Doug McNaught wrote:
> > Scott Chapman <[EMAIL PROTECTED]> writes:
> > > It would be nice if PostgreSQL could return the primary key it
> > > inserted with but that may not be a fool-proof solution either. Is
> > > t
Scott Chapman <[EMAIL PROTECTED]> writes:
> It would be nice if PostgreSQL could return the primary key it inserted
> with but that may not be a fool-proof solution either. Is there a nice
> way to handle this situation?
Write a database function that inserts the record and returns the
primary
80 matches
Mail list logo
