pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddifficult query (for me)
7+ messages / 5 participants
[nested] [flat]
* difficult query (for me)
@ 2001-12-29 15:20 QdlatY <qdlaty@wielun.dhs.org>
0 siblings, 1 reply; 7+ messages in thread
From: QdlatY @ 2001-12-29 15:20 UTC (permalink / raw)
To: pgsql-sql
Hello
I will try to explain my problem as clearly as i can.
So, I have tree tables
Named:
Tables
Threads
Posts
It is typicall structure of discussion phorum.
Now, Tables has a column ID (unique random index)
Threads has a columns ID (index) and TableID (index of one row of tables
table)
Posts has attribute ThreadId and CreateTime
(i only wrote important columns to this query)
Now, I have Tables row with ID = 10
And then i want to have list of all Threads (belongs to Table with id = 10,
so WHERE Threads.TableId = 10) sorted by CreateTime of newest Posts row
belong to every thread.
F.E.
Table.iD = 10
Thread
ID TableID
1 10
2 10
3 11
Posts
ThreadId CreateTime (i will use nubers only to explain)
1 10
1 20
1 25
2 43
3 02
So i want to have result from Table ID = 10:
Threads
ID
2 (because CreateTime of Posts from 2 Thread is highest)
1 (and so on...)
(Thread 3 not belongs to Table.ID = 10)
Of course result can contains adequate Posts columns but don't must to...
I hope you will able to help me, i think it's not difficult but i'm a
little newbie, so, i gave up on this :PPP
Best regards
QdlatY
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: difficult query (for me)
@ 2001-12-29 16:05 Andrew G. Hammond <drew@xyzzy.dhs.org>
parent: QdlatY <qdlaty@wielun.dhs.org>
0 siblings, 0 replies; 7+ messages in thread
From: Andrew G. Hammond @ 2001-12-29 16:05 UTC (permalink / raw)
To: QdlatY <qdlaty@wielun.dhs.org>; +Cc: pgsql-sql
On Sat, 2001-12-29 at 10:20, QdlatY wrote:
> Hello
>
> I will try to explain my problem as clearly as i can.
>
> So, I have tree tables
> Named:
>
> Tables
> Threads
> Posts
>
> It is typicall structure of discussion phorum.
There is no such thing as typical. To help you, we need the exact
schema of your database. From the above, it's not clear what the
relation between Tables and the rest of your database is.
> Now, Tables has a column ID (unique random index)
random? Aren't you using the SERIAL type, since this is exactly the
kind of thing it's made for?
> Threads has a columns ID (index) and TableID (index of one row of tables
> table)
> Posts has attribute ThreadId and CreateTime
>
> (i only wrote important columns to this query)
>
> Now, I have Tables row with ID = 10
> And then i want to have list of all Threads (belongs to Table with id = 10,
> so WHERE Threads.TableId = 10) sorted by CreateTime of newest Posts row
> belong to every thread.
Sounds like you want to use a join with DISTINCT ON the thread id and
the ORDER BY clause to sort things by create time.
SELECT DISTINCT ON (t.id) * -- or you could list the columns you want
FROM threads t, posts p
WHERE t.tableid = 10 AND t.id = p.threadid
ORDER BY t.id, p.createtime DESC
If this will be a common query then you might consider creating an index
or two on the tables involved.
> F.E.
> Table.iD = 10
>
> Thread
> ID TableID
> 1 10
> 2 10
> 3 11
>
> Posts
> ThreadId CreateTime (i will use nubers only to explain)
> 1 10
> 1 20
> 1 25
> 2 43
> 3 02
>
>
> So i want to have result from Table ID = 10:
> Threads
> ID
> 2 (because CreateTime of Posts from 2 Thread is highest)
> 1 (and so on...)
>
> (Thread 3 not belongs to Table.ID = 10)
>
> Of course result can contains adequate Posts columns but don't must to...
>
> I hope you will able to help me, i think it's not difficult but i'm a
> little newbie, so, i gave up on this :PPP
Hope this helps.
--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
^ permalink raw reply [nested|flat] 7+ messages in thread
* Difficult query (for me)
@ 2003-04-04 20:06 Stephane <sylfanie@club-internet.fr>
0 siblings, 1 reply; 7+ messages in thread
From: sylfanie@club-internet.fr @ 2003-04-04 20:06 UTC (permalink / raw)
To: pgsql-sql
Hello,
Basicaly I have two tables like this:
Table 1
RecordName Value
a 100
b 100
c 100
d 100
e 100
f 100
Table 2
RecordName Value
a 25
b 50
c 75
g 150
h 150
I would like a query that give me a result like this:
a 75 (100-25)
b 50 (100-50)
c 25
d 100
e 100
f 100
g -150
h -150
It is table1-table2.
I could do it for records a,b,c but I do not how to have record which
are only in one of the two tables.
Thanks for your help.
Stephane.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Difficult query (for me)
@ 2003-04-04 22:58 Lee Harr <missive@frontiernet.net>
parent: Stephane <sylfanie@club-internet.fr>
0 siblings, 1 reply; 7+ messages in thread
From: Lee Harr @ 2003-04-04 22:58 UTC (permalink / raw)
To: pgsql-sql
In article <4ff986f9.0304041206.1ae7b97a@posting.google.com>, Stephane wrote:
> Hello,
>
> Basicaly I have two tables like this:
>
> Table 1
> RecordName Value
> a 100
> b 100
> c 100
> d 100
> e 100
> f 100
>
> Table 2
> RecordName Value
> a 25
> b 50
> c 75
> g 150
> h 150
>
> I would like a query that give me a result like this:
> a 75 (100-25)
> b 50 (100-50)
> c 25
> d 100
> e 100
> f 100
> g -150
> h -150
>
> It is table1-table2.
> I could do it for records a,b,c but I do not how to have record which
> are only in one of the two tables.
> Thanks for your help.
> Stephane.
test=# select * from one;
n | v
---+-----
a | 100
b | 100
c | 100
d | 100
e | 100
f | 100
(6 rows)
test=# select * from two;
n | v
---+-----
a | 25
b | 50
c | 75
g | 150
h | 150
(5 rows)
test=# select coalesce(one.n, two.n), one.v as v1, two.v as v2,
test-# coalesce(one.v, 0)-coalesce(two.v, 0) as difference
test-# from one full outer join two on (one.n = two.n);
case | v1 | v2 | difference
------+-----+-----+------------
a | 100 | 25 | 75
b | 100 | 50 | 50
c | 100 | 75 | 25
d | 100 | | 100
e | 100 | | 100
f | 100 | | 100
g | | 150 | -150
h | | 150 | -150
(8 rows)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Difficult query (for me)
@ 2003-04-06 21:09 Stephane <sylfanie@club-internet.fr>
parent: Lee Harr <missive@frontiernet.net>
0 siblings, 0 replies; 7+ messages in thread
From: sylfanie@club-internet.fr @ 2003-04-06 21:09 UTC (permalink / raw)
To: pgsql-sql
Lee Harr <missive@frontiernet.net> wrote in message news:<b6l2m8
[...]
Thanks.
Stephane.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Difficult query (for me)
@ 2003-04-07 16:32 David Olbersen <DOlbersen@stbernard.com>
0 siblings, 0 replies; 7+ messages in thread
From: David Olbersen @ 2003-04-07 16:32 UTC (permalink / raw)
To: pgsql-sql
Stephane,
SELECT
table1.recordname,
table1.value - table2.value
FROM
table1,
table2
WHERE
table1.recordname = table2.recordname
Right?
--------------------------
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
> -----Original Message-----
> From: Stephane [mailto:sylfanie@club-internet.fr]
> Sent: Friday, April 04, 2003 12:06 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Difficult query (for me)
>
>
> Hello,
>
> Basicaly I have two tables like this:
>
> Table 1
> RecordName Value
> a 100
> b 100
> c 100
> d 100
> e 100
> f 100
>
> Table 2
> RecordName Value
> a 25
> b 50
> c 75
> g 150
> h 150
>
> I would like a query that give me a result like this:
> a 75 (100-25)
> b 50 (100-50)
> c 25
> d 100
> e 100
> f 100
> g -150
> h -150
>
> It is table1-table2.
> I could do it for records a,b,c but I do not how to have record which
> are only in one of the two tables.
> Thanks for your help.
> Stephane.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Difficult query (for me)
@ 2003-04-07 16:33 David Olbersen <DOlbersen@stbernard.com>
0 siblings, 0 replies; 7+ messages in thread
From: David Olbersen @ 2003-04-07 16:33 UTC (permalink / raw)
To: pgsql-sql
Stephane,
Sorry, further reading shows me that I'm dumb and you can already do this.
--------------------------
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152
> -----Original Message-----
> From: David Olbersen
> Sent: Monday, April 07, 2003 9:32 AM
> To: pgsql-sql@postgresql.org
> Subject: RE: [SQL] Difficult query (for me)
>
>
> Stephane,
>
> SELECT
> table1.recordname,
> table1.value - table2.value
> FROM
> table1,
> table2
> WHERE
> table1.recordname = table2.recordname
>
> Right?
>
> --------------------------
> David Olbersen
> iGuard Engineer
> 11415 West Bernardo Court
> San Diego, CA 92127
> 1-858-676-2277 x2152
>
>
> > -----Original Message-----
> > From: Stephane [mailto:sylfanie@club-internet.fr]
> > Sent: Friday, April 04, 2003 12:06 PM
> > To: pgsql-sql@postgresql.org
> > Subject: [SQL] Difficult query (for me)
> >
> >
> > Hello,
> >
> > Basicaly I have two tables like this:
> >
> > Table 1
> > RecordName Value
> > a 100
> > b 100
> > c 100
> > d 100
> > e 100
> > f 100
> >
> > Table 2
> > RecordName Value
> > a 25
> > b 50
> > c 75
> > g 150
> > h 150
> >
> > I would like a query that give me a result like this:
> > a 75 (100-25)
> > b 50 (100-50)
> > c 25
> > d 100
> > e 100
> > f 100
> > g -150
> > h -150
> >
> > It is table1-table2.
> > I could do it for records a,b,c but I do not how to have
> record which
> > are only in one of the two tables.
> > Thanks for your help.
> > Stephane.
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-04-07 16:33 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-29 15:20 difficult query (for me) QdlatY <qdlaty@wielun.dhs.org>
2001-12-29 16:05 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
2003-04-04 20:06 Difficult query (for me) Stephane <sylfanie@club-internet.fr>
2003-04-04 22:58 ` Re: Difficult query (for me) Lee Harr <missive@frontiernet.net>
2003-04-06 21:09 ` Re: Difficult query (for me) Stephane <sylfanie@club-internet.fr>
2003-04-07 16:32 Re: Difficult query (for me) David Olbersen <DOlbersen@stbernard.com>
2003-04-07 16:33 Re: Difficult query (for me) David Olbersen <DOlbersen@stbernard.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox