Обсуждение: Iterations in a SELECT
I have a small problem which I'm looking for an elegant solution to... I'm trying to put together a select where a list dates is compared to a period to identify dates which aren't in the list... I could just create a list of days in a table (like 20 years worth) and then compare it, but I'm wondering if there is a trick where one could get results like this select (for i := 1 to 5) as number; or select i as number from for(i,1,5); and get something like number ------ 1 2 3 4 5 Any suggestions? Thanks Scott.
Scott,
>I'm trying to put together a select where a list dates is compared to a
>period to identify dates which aren't in the list...
Sounds like you are trying to find out what values are not in a list of
other values.
Here's my example which you can adapt (if it's what you're trying to do).
Two tables,
test and test2, each with an integer column called val and val2
respectively:
select * from test;
val
---
1
2
3
5
6
3
select * from test2;
val2
----
3
4
Then you do a quick subselect and...
select * from test where val not in ((select val2 from test2));
Result is:
val
-----
1
2
5
6
Is that what you're hoping to do?
I can't think of anyway to do a select and genrate a list from x to y.
Wow, that's a great one!
Hope this helps,
-Robby
I could just create a list of days in a table (like 20 years worth) and then
compare it, but I'm wondering if there is a trick where one could get
results like this
select (for i := 1 to 5) as number;
or
select i as number from for(i,1,5);
and get something like
number
------
1
2
3
4
5
Any suggestions?
Thanks
Scott.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Thank you... Your suggestion is how I am doing it until a better suggestion
comes along, but without building a dynamic sql statement with many UNION
statements (read: mess), I can't think of how to build a non-table-driven
multi-row query result...
That is the main problem here....
Thanks for the prompt reply though, Robby.
Scott..
-----Original Message-----
From: Robby Slaughter [mailto:webmaster@robbyslaughter.com]
Sent: July 17, 2001 8:39 PM
To: Scott Muir; pgsql-novice@postgresql.org
Subject: RE: [NOVICE] Iterations in a SELECT
Scott,
>I'm trying to put together a select where a list dates is compared to a
>period to identify dates which aren't in the list...
Sounds like you are trying to find out what values are not in a list of
other values.
Here's my example which you can adapt (if it's what you're trying to do).
Two tables,
test and test2, each with an integer column called val and val2
respectively:
select * from test;
val
---
1
2
3
5
6
3
select * from test2;
val2
----
3
4
Then you do a quick subselect and...
select * from test where val not in ((select val2 from test2));
Result is:
val
-----
1
2
5
6
Is that what you're hoping to do?
I can't think of anyway to do a select and genrate a list from x to y.
Wow, that's a great one!
Hope this helps,
-Robby
I could just create a list of days in a table (like 20 years worth) and then
compare it, but I'm wondering if there is a trick where one could get
results like this
select (for i := 1 to 5) as number;
or
select i as number from for(i,1,5);
and get something like
number
------
1
2
3
4
5
Any suggestions?
Thanks
Scott.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
On Tue, 17 Jul 2001, Scott Muir wrote:
> Thank you... Your suggestion is how I am doing it until a better suggestion
> comes along, but without building a dynamic sql statement with many UNION
> statements (read: mess), I can't think of how to build a non-table-driven
> multi-row query result...
depending on the number of values you would like to compare, you can use
something like
select value from foo where value in (1,2,3,4,5)
have fun,
helge
how about using BETWEEN? select * from tablename where datefield between '2001-01-01' and '2001-05-05'; or if you're looking for dates outside the period select * from tablename where datefield NOT between '2001-01-01' and '2001-05-05'; (i'm assuming your list of dates has no gaps in it, could be wrong!) hth, tamsin -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Scott Muir Sent: 18 July 2001 03:08 To: pgsql-novice@postgresql.org Subject: [NOVICE] Iterations in a SELECT I have a small problem which I'm looking for an elegant solution to... I'm trying to put together a select where a list dates is compared to a period to identify dates which aren't in the list... I could just create a list of days in a table (like 20 years worth) and then compare it, but I'm wondering if there is a trick where one could get results like this select (for i := 1 to 5) as number; or select i as number from for(i,1,5); and get something like number ------ 1 2 3 4 5 Any suggestions? Thanks Scott. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html