[SQL] how to cast this ?
Hello there I have a db table with two char fields : log_date char(10) log_time char(8) I added a new field log_timestamp of type timestamptz How do I take the data from log_date and log_time and put it into log_timestamp ? update log set log_timestamp = cast (log_date as timestamp) || cast (log_time as timestamp) is not working. Thank's in advance ... jr __ PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland internet : www.pup.ch phone : +4141 790 4040 fax : +4141 790 2545 mobile: +4179 211 0315 __ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how to cast this ?
On Thu, 2002-09-12 at 10:22, [EMAIL PROTECTED] wrote: > Hello there > > I have a db table with two char fields : > > log_date char(10) > log_time char(8) > > I added a new field log_timestamp of type timestamptz > > How do I take the data from log_date and log_time and put it into > log_timestamp ? > > update log set log_timestamp = cast (log_date as timestamp) || cast > (log_time as timestamp) > > is not working. I don't know what format you have your dates and times in, but maybe this will work: CAST (log_date || ' ' || log_time) AS TIMESTAMP -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Let the wicked forsake his way, and the unrighteous man his thoughts; and let him return unto the LORD, and He will have mercy upon him; and to our God, for he will abundantly pardon." Isaiah 55:7 ---(end of broadcast)--- TIP 3: 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
[SQL] still sorting and casting problems
Hello there Unfortunately, I can't change the fieldtype that easy because other applications use the date as char(10) field as well. I need to sort the query by the log_date desc (log_date is char(10)). I tried the following without success : select id, log_date from userlog order by cast (log_date as date) desc select id, log_date from userlog order by date(log_date) desc Please help and thank's in advance ... jr __ PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland internet : www.pup.ch phone : +4141 790 4040 fax : +4141 790 2545 mobile: +4179 211 0315 __ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] reset sequence
--- Ricardo Javier Aranibar León
<[EMAIL PROTECTED]> wrote:
> Hi List,
>
> I need that somebody help me.
> First, I use PostgreSQL 7.0.3 for linux.
> I need reset my sequence but that it begin in 1
> again when a use nextval.
> And I have been search in
> http://archives.postgresql.org/ about reset
> sequence but I obtain this information:
>SELECT setval('name_sequence', 1, false);
try SELECT setval('name_sequence',1);
Maybe setval(,,) doesn't exist in
7.0.3.
regards,
ludwig lim
__
Do you Yahoo!?
Yahoo! News - Today's headlines
http://news.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] reset sequence
Ludwig Lim wrote:
> --- Ricardo Javier Aranibar León
> <[EMAIL PROTECTED]> wrote:
> > Hi List,
> >
> > I need that somebody help me.
> > First, I use PostgreSQL 7.0.3 for linux.
> > I need reset my sequence but that it begin in 1
> > again when a use nextval.
> > And I have been search in
> > http://archives.postgresql.org/ about reset
> > sequence but I obtain this information:
> >SELECT setval('name_sequence', 1, false);
>
> try SELECT setval('name_sequence',1);
That will probably set the current value to 1, which will result in a
nextval of 2, which is perhaps not what Ricardo wanted.
> Maybe setval(,,) doesn't exist in
> 7.0.3.
\df setval
in psql should output all possible parameter combinations for setval.
Regards,
Michael Paesold
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Rules and Triggers: another question
Jerome Chochon wrote: > Thanks for your answer but i have another question. > Which one is the faster ? > > If i write this trigger... > CREATE TRIGGER trigger_name > BEFORE DELETE > ON table_name > FOR EACH ROW EXECUTE PROCEDURE function(); > > and this rule: > CREATE RULE name_rule AS > ON DELETE TO table_name > DO select function(); > > When i delete values, whichone will call the value the first ? > > Someone can help me ?? With the RULE, the function will be called earlier than with the TRIGGER. (according to what Ross said before) Regards, Michael ---(end of broadcast)--- TIP 3: 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
[SQL] Select the max on a field
Hi all, Suppose that I have a table like this: att_1 |att_2 |att_3 | att_4 1 | a |y |y1 2 | b |y |y2 3 | a |xx |y3 4 | c |zz |y4 5 | a |t|y5 6 | c |x |y6 I want obtain all row that have for each att_2 the max of att_1 some like SELECT * FROM MY_TABLE GROUP BY att_2 HAVING att_1 = max ( id_user_log) for obtain: att_1 |att_2 |att_3 | att_4 2 | b |y |y2 5 | a |t|y5 6 | c |x |y6 Ciao Gaetano ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Select the max on a field
"Gaetano Mendola" <[EMAIL PROTECTED]> wrote in message alq3mr$2s7o$[EMAIL PROTECTED]">news:alq3mr$2s7o$[EMAIL PROTECTED]... > Hi all, > > Suppose that I have a table like this: > > > att_1 |att_2 |att_3 | att_4 > > 1 | a |y |y1 > 2 | b |y |y2 > 3 | a |xx |y3 > 4 | c |zz |y4 > 5 | a |t|y5 > 6 | c |x |y6 > > > > I want obtain all row that have for each att_2 > the max of att_1 I forgot to say: "with a single total query without sub select " Ciao Gaetano ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] still sorting and casting problems
On Thu, 12 Sep 2002 [EMAIL PROTECTED] wrote: > > Hello there > > Unfortunately, I can't change the fieldtype that easy because other > applications use the date as char(10) field as well. > > I need to sort the query by the log_date desc (log_date is char(10)). I > tried the following without success : > > select id, log_date from userlog order by cast (log_date as date) desc > > select id, log_date from userlog order by date(log_date) desc What do you get when trying this last query (something of the sort works for me in 7.3beta). It's possibly that you might need to use date(log_date::text) desc, but without the error it's hard to say. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] still sorting and casting problems
--- [EMAIL PROTECTED] wrote: > I need to sort the query by the log_date desc > (log_date is char(10)). I > tried the following without success : > > select id, log_date from userlog order by cast > (log_date as date) desc > > select id, log_date from userlog order by > date(log_date) desc > > Please help and thank's in advance ... jr > You haven't shown the error message here, but I'm guessing that there is no direct cast between char and date available in PostgreSQL. Try casting first to text, then to date. __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Select the max on a field
On Thu, 12 Sep 2002, Gaetano Mendola wrote: > Hi all, > > Suppose that I have a table like this: > > > att_1 |att_2 |att_3 | att_4 > > 1 | a |y |y1 > 2 | b |y |y2 > 3 | a |xx |y3 > 4 | c |zz |y4 > 5 | a |t|y5 > 6 | c |x |y6 > > > > I want obtain all row that have for each att_2 > the max of att_1 > > some like > > SELECT * > FROM MY_TABLE > GROUP BY att_2 > HAVING att_1 = max ( id_user_log) If you don't mind using postgres extensions and don't need a particular att_1 ordering: select distinct on (att_2) * from test order by att_2, att_1 desc; Otherwise I'm not sure you can do it without a subselect somewhere. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Select the max on a field
--- Gaetano Mendola <[EMAIL PROTECTED]> wrote: > > "Gaetano Mendola" <[EMAIL PROTECTED]> wrote in > message > alq3mr$2s7o$[EMAIL PROTECTED]">news:alq3mr$2s7o$[EMAIL PROTECTED]... > > Hi all, > > > > Suppose that I have a table like this: > > > > > > att_1 |att_2 |att_3 | att_4 > > > > 1 | a |y |y1 > > 2 | b |y |y2 > > 3 | a |xx |y3 > > 4 | c |zz |y4 > > 5 | a |t|y5 > > 6 | c |x |y6 > > > > > > > > I want obtain all row that have for each att_2 > > the max of att_1 > > I forgot to say: "with a single total query without > sub select " > If you don't mind a non-portable feature, DISTINCT ON should do what you want. Something like: SELECT DISTINCT ON (att_2) att_1, att_2, att_3, att_4 FROM table ORDER BY att_2, att_1 DESC; __ Do you Yahoo!? Yahoo! News - Today's headlines http://news.yahoo.com ---(end of broadcast)--- TIP 3: 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
[SQL] Performance inside and outside view ( WAS Re: Select the max on a field )
"Stephan Szabo" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > select distinct on (att_2) * from test > order by att_2, att_1 desc; Yes that's right it's help me, but here the optimizer have some problems: CREATE VIEW last_user_logs AS SELECT DISTINCT ON (id_user) * FROM user_logs ORDER BY id_user, id_user_log DESC; and now: # explain analyze select * from user_logs where id_user = '5430'; NOTICE: QUERY PLAN: Index Scan using idx_user_user_logs on user_logs (cost=0.00..3038.59 rows=939 width=68) (actual time=0.03..0.03 rows=0 loops=1) Total runtime: 0.12 msec EXPLAIN empdb=# explain analyze select * from last_user_logs where id_user = 5430; NOTICE: QUERY PLAN: Subquery Scan last_user_logs (cost=20256.12..20725.43 rows=18772 width=68) (actual time=3526.10..3526.10 rows=0 loops=1) -> Unique (cost=20256.12..20725.43 rows=18772 width=68) (actual time=3067.14..3522.54 rows=2226 loops=1) -> Sort (cost=20256.12..20256.12 rows=187723 width=68) (actual time=3067.13..3241.94 rows=187723 loops=1) -> Seq Scan on user_logs (cost=0.00..3813.23 rows=187723 width=68) (actual time=0.02..1070.59 rows=187723 loops=1) Total runtime: 3578.07 msec EXPLAIN if I do instead without view and #explain analyze SELECT DISTINCT ON (id_user) * FROM user_logs WHERE id_user = 5430 ORDER BY id_user, id_user_log DESC; NOTICE: QUERY PLAN: Unique (cost=3084.93..3087.27 rows=94 width=68) (actual time=0.21..0.21 rows=0 loops=1) -> Sort (cost=3084.93..3084.93 rows=939 width=68) (actual time=0.21..0.21 rows=0 loops=1) -> Index Scan using idx_user_user_logs on user_logs (cost=0.00..3038.59 rows=939 width=68) (actual time=0.03..0.03 rows=0 loops=1) Total runtime: 0.31 msec EXPLAIN Ciao Gaetano ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Performance inside and outside view ( WAS Re: Select the max on a field )
"Gaetano Mendola" <[EMAIL PROTECTED]> writes: > Yes that's right it's help me, > but here the optimizer have some problems: What's the datatype of id_user, and why are you quoting the compared constant in some cases but not others? I don't think the difference has anything to do with the view here ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Performance inside and outside view ( WAS Re: Select the
On Thu, 12 Sep 2002, Tom Lane wrote: > "Gaetano Mendola" <[EMAIL PROTECTED]> writes: > > Yes that's right it's help me, > > but here the optimizer have some problems: > > What's the datatype of id_user, and why are you quoting the compared > constant in some cases but not others? I don't think the difference > has anything to do with the view here ... At least in current sources it would, since it can't push down the clause (unless I'm misreading subquery_is_pushdown_safe and the comment) through the distinct on. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Performance inside and outside view ( WAS Re: Select the max on a field )
"Tom Lane" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > "Gaetano Mendola" <[EMAIL PROTECTED]> writes: > > Yes that's right it's help me, > > but here the optimizer have some problems: > > What's the datatype of id_user, and why are you quoting the compared > constant in some cases but not others? I don't think the difference > has anything to do with the view here ... id_user is an INTEGER type. Well what about this two selects: a) SELECT DISTINCT ON (id_user) * FROM user_logs WHERE id_user = 5430 ORDER BY id_user, id_user_log DESC; b) CREATE VIEW last_user_logs AS SELECT DISTINCT ON (id_user) * FROM user_logs ORDER BY id_user, id_user_log DESC; SELECT * FROM last_user_logs WHERE id_user = 5430 I think that in the second case the filter is not pushed inside the view. Ciao Gaetano ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Performance inside and outside view ( WAS Re: Select the
On Thu, 12 Sep 2002, Gaetano Mendola wrote: > > "Tom Lane" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > "Gaetano Mendola" <[EMAIL PROTECTED]> writes: > > > Yes that's right it's help me, > > > but here the optimizer have some problems: > > > > What's the datatype of id_user, and why are you quoting the compared > > constant in some cases but not others? I don't think the difference > > has anything to do with the view here ... > > id_user is an INTEGER type. > > Well what about this two selects: > > a) SELECT DISTINCT ON (id_user) * > FROM user_logs >WHERE id_user = 5430 >ORDER BY id_user, id_user_log DESC; > > b) CREATE VIEW last_user_logs AS > SELECT DISTINCT ON (id_user) * > FROM user_logs > ORDER BY id_user, id_user_log DESC; > > SELECT * > FROM last_user_logs > WHERE id_user = 5430 > > > I think that in the second case the filter is not pushed inside the view. Pushing into distinct on has limited cases where it's legal, you're using one of them, but the system doesn't differentiate and doesn't push in either. select distinct on (col1) * from table where col2=2; and select * from (select distinct on (col1) * from table) v where col2=2; -- this is like a view case are not the same query. I believe Tom makes a comment to the effect that it should be safe if the clause only references distinct on'd columns. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Latitude / Longitude
Hi Folks - I'm working on a global weather database for a client and have hit an issue which I'm sure has been solved before. Unfortunately, the site search doesn't pull up anything. Essentially, I've got two tables, one with city/county/lat/long and another with lat/long/weather data. None of the lat/longs in the two tables match up directly, so I can't do a simple join of the two tables. I need to join on closest proximity on the lat/long fields. Any suggestions? It seems to me this will be pretty expensive on CPU resources unless there's a really elegant trick uncovered. Tim -- Founder - PHPBuilder.com / Geocrawler.com / SourceForge GPG Public Key: http://www.perdue.net/personal/pgp.php Perdue, Inc. / Immortal LLC 515-554-9520 msg07378/pgp0.pgp Description: PGP signature
Re: [SQL] Latitude / Longitude
Look at contrib/earthdistance, I **think** it does what you need. LER On Thu, 2002-09-12 at 12:44, Tim Perdue wrote: > Hi Folks - > > I'm working on a global weather database for a client and have hit an issue > which I'm sure has been solved before. Unfortunately, the site search doesn't > pull up anything. > > Essentially, I've got two tables, one with city/county/lat/long and another > with lat/long/weather data. > > None of the lat/longs in the two tables match up directly, so I can't do a > simple join of the two tables. I need to join on closest proximity on the > lat/long fields. > > Any suggestions? It seems to me this will be pretty expensive on CPU resources > unless there's a really elegant trick uncovered. > > Tim > > -- > Founder - PHPBuilder.com / Geocrawler.com / SourceForge > GPG Public Key: http://www.perdue.net/personal/pgp.php > Perdue, Inc. / Immortal LLC > 515-554-9520 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Performance inside and outside view ( WAS Re: Select the
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Thu, 12 Sep 2002, Tom Lane wrote: >> What's the datatype of id_user, and why are you quoting the compared >> constant in some cases but not others? I don't think the difference >> has anything to do with the view here ... > At least in current sources it would, since it can't push down the clause > (unless I'm misreading subquery_is_pushdown_safe and the comment) through > the distinct on. Oh, duh, you're right; I'd missed the DISTINCT ON ... regards, tom lane ---(end of broadcast)--- TIP 3: 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
[SQL] LIMIT within UNION?
Greetings- I have a table of participants to be contacted for a study. Some are in the "exposure" group, others in the "control" group. This is designated by a column, typenr, that contains 1 for exposure, 2 for control. The complication is this: I need to select 200 total. The 200 number should include *all* those eligible in the exposure group, plus enough from the control group to bring the total number up to 200. (Yes, there is a valid reason for this.) Furthermore, I need to sort the output of the two groups *together* by zip code. What I've done is to write a script that counts the number of eligible exposure candidates: SELECT count(*) FROM participants WHERE AND typenr=1 Then subtract that number (currently 28) from 200 to get 172 control participants. Then the problem starts. SELECT ... FROM participants WHERE typenr=1 AND UNION SELECT ... FROM participants WHERE typenr=2 LIMIT 172 ORDER BY zip; returns ERROR: parser: parse error at or near "ORDER" I've tried a variety of parentheses to no avail. Can someone shed some light? Thanks! -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] LIMIT within UNION?
Andrew Perrin <[EMAIL PROTECTED]> writes: > SELECT ... FROM participants > WHERE typenr=1 AND > UNION > SELECT ... FROM participants > WHERE typenr=2 LIMIT 172 > ORDER BY zip; I think you need SELECT * FROM ( SELECT ... FROM participants WHERE typenr=1 AND UNION (SELECT ... FROM participants WHERE typenr=2 LIMIT 172) ) ss ORDER BY zip; Not sure if the inner set of parens is essential, but it might be. The outer SELECT superstructure is definitely necessary to give a place to hang the ORDER BY on. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] LIMIT within UNION?
> "Andrew" == Andrew Perrin <[EMAIL PROTECTED]> writes: Andrew> What I've done is to write a script that counts the number Andrew> of eligible exposure candidates: Andrew> SELECT count(*) FROM participants WHERE Andrew> AND typenr=1 Andrew> Then subtract that number (currently 28) from 200 to get Andrew> 172 control participants. Then the problem starts. [...] Andrew> returns ERROR: parser: parse error at or near "ORDER" Can you do this via a subselect: SELECT * FROM ( SELECT ... FROM participants WHERE typenr=1 AND UNION SELECT ... FROM participants WHERE typenr=2 LIMIT 172 ) ORDER BY zip; roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [SQL] Latitude / Longitude
On Thu, Sep 12, 2002 at 12:49:21 -0500, Larry Rosenman <[EMAIL PROTECTED]> wrote: > Look at contrib/earthdistance, I **think** it does what you need. There isn't an index for the distance operator. You can use boxes to limit the candidates if there is a small bound on how far apart points can be before they are no longer candidates for a match even if they are a nearest neighbor pair. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] LIMIT within UNION?
Thanks! That did it. The inner parens are necessary - without them the ORDER BY seems to be parsed as part of the second subquery and is therefore a syntax error. Best, Andy -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu On Thu, 12 Sep 2002, Tom Lane wrote: > Andrew Perrin <[EMAIL PROTECTED]> writes: > > SELECT ... FROM participants > > WHERE typenr=1 AND > > UNION > > SELECT ... FROM participants > > WHERE typenr=2 LIMIT 172 > > ORDER BY zip; > > I think you need > > SELECT * FROM > ( > SELECT ... FROM participants > WHERE typenr=1 AND > UNION > (SELECT ... FROM participants > WHERE typenr=2 LIMIT 172) > ) ss > ORDER BY zip; > > Not sure if the inner set of parens is essential, but it might be. > The outer SELECT superstructure is definitely necessary to give a > place to hang the ORDER BY on. > > regards, tom lane > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIMIT within UNION?
On 12 Sep 2002, Roland Roberts wrote: > > "Andrew" == Andrew Perrin <[EMAIL PROTECTED]> writes: > ... > Can you do this via a subselect: > > SELECT * FROM > ( SELECT ... FROM participants > WHERE typenr=1 AND > UNION > SELECT ... FROM participants > WHERE typenr=2 LIMIT 172 ) > ORDER BY zip; > Unfortunately in this case the LIMIT is applied to the fully-UNIONed set, limiting the total number of cases to 172 instead of just those from the second subquery. Tom Lane's example worked, though. Thanks, Andy -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Latitude / Longitude
On Thu, Sep 12, 2002 at 12:49:21PM -0500, Larry Rosenman wrote: > Look at contrib/earthdistance, I **think** it does what you need. Thanks, yeah this starts to get me into the realm of what I need. It still has to run every possible permutation to figure out which one is closest for each postalcode. What I'll do is run that once and build a 3rd table which can be used to join the other two together using a view. Thanks, Tim -- Founder - PHPBuilder.com / Geocrawler.com / SourceForge GPG Public Key: http://www.perdue.net/personal/pgp.php msg07387/pgp0.pgp Description: PGP signature
Re: [SQL] LIMIT within UNION?
On Thu, 12 Sep 2002, Andrew Perrin wrote: > Greetings- > > I have a table of participants to be contacted for a study. Some are in > the "exposure" group, others in the "control" group. This is designated by > a column, typenr, that contains 1 for exposure, 2 for control. > > The complication is this: I need to select 200 total. The 200 number > should include *all* those eligible in the exposure group, plus enough > from the control group to bring the total number up to 200. (Yes, there is > a valid reason for this.) Furthermore, I need to sort the output of the > two groups *together* by zip code. Do you get more than 200 if there are more eligible people and does the ... ever include the same person in both sides of the union? If not in the second case, union all would probably save the database some extra work since it won't have to try to weed out duplicates. If not in the first case, then wouldn't a limit 200 on the after union result set work rather than a separate count and subtraction? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] LIMIT within UNION?
On Thu, 12 Sep 2002, Stephan Szabo wrote: > On Thu, 12 Sep 2002, Andrew Perrin wrote: > > > Greetings- > > > > I have a table of participants to be contacted for a study. Some are in > > the "exposure" group, others in the "control" group. This is designated by > > a column, typenr, that contains 1 for exposure, 2 for control. > > > > The complication is this: I need to select 200 total. The 200 number > > should include *all* those eligible in the exposure group, plus enough > > from the control group to bring the total number up to 200. (Yes, there is > > a valid reason for this.) Furthermore, I need to sort the output of the > > two groups *together* by zip code. > > Do you get more than 200 if there are more eligible people Yes - in the (rather rare) case that there are 200 or more eligible exposure subjects, the result set should be the total number of eligible exposure subjects. > and does the > ... ever include the same person in both sides of the union? No; each person is only in one of the two sides. > > If not in the second case, union all would probably save the database > some extra work since it won't have to try to weed out duplicates. I'll try that. > > If not in the first case, then wouldn't a limit 200 on the after union > result set work rather than a separate count and subtraction? > Interesting - this would count on the UNION including all cases in the first query before those in the second query. Are UNIONed records presented in any predictable order? -- Andrew J Perrin - http://www.unc.edu/~aperrin Assistant Professor of Sociology, U of North Carolina, Chapel Hill [EMAIL PROTECTED] * andrew_perrin (at) unc.edu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] LIMIT within UNION?
Andrew Perrin <[EMAIL PROTECTED]> writes: > Interesting - this would count on the UNION including all cases in the > first query before those in the second query. Are UNIONed records > presented in any predictable order? If you can use UNION ALL then this would be pretty safe to rely on. If you must use UNION (to eliminate dups) then it won't work. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] LIMIT within UNION?
Andrew, Another approach, one that does not require you to know before constructing the query how many eligible subjects there are, is not to use a union at all: SELECT * FROM ( SELECT ... , zip FROM participants WHERE (typenr = 1 and ) DESC LIMIT 200 ) p1 ORDER BY zip; The inner query gives you all of the records that meet the eligibility criteria, plus all of the records that have typenr = 2, in the order of whether or not they meet the criteria (as a boolean value) and truncates it at 200 records. The outer query then re-sorts this result in zip order. This seems, to me, much more flexible than using a UNION query. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] Latitude / Longitude
Tim Perdue wrote:
> Hi Folks -
>
> I'm working on a global weather database for a client and have hit an issue
> which I'm sure has been solved before. Unfortunately, the site search doesn't
> pull up anything.
>
> Essentially, I've got two tables, one with city/county/lat/long and another
> with lat/long/weather data.
>
> None of the lat/longs in the two tables match up directly, so I can't do a
> simple join of the two tables. I need to join on closest proximity on the
> lat/long fields.
>
> Any suggestions? It seems to me this will be pretty expensive on CPU resources
> unless there's a really elegant trick uncovered.
I see you've gotten some other help, but in case you're interested, I'll give
you another alternative. Here's a plpgsql function I wrote a while ago based
on the Haversine formula:
CREATE FUNCTION "zipdist" (float8,float8,float8,float8 ) RETURNS float8 AS '
DECLARE
lat1 ALIAS FOR $1;
lon1 ALIAS FOR $2;
lat2 ALIAS FOR $3;
lon2 ALIAS FOR $4;
dist float8;
BEGIN
dist := 0.621 * 6371.2 * 2 *
atan2( sqrt(abs(0 + pow(sin(radians(lat2)/2 -
radians(lat1)/2),2) + cos(radians(lat1)) * cos(radians(lat2)) *
pow(sin(radians(lon2)/2 - radians(lon1)/2),2))),sqrt(abs(1 -
pow(sin(radians(lat2)/2 - radians(lat1)/2),2) + cos(radians(lat1)) *
cos(radians(lat2)) * pow(sin(radians(lon2)/2 - radians(lon1)/2),2;
return dist;
END;
' LANGUAGE 'plpgsql';
I used the following PHP code to start looking for a match in a small circle,
and then expand it if no matches were found:
$dist = INIT_DIST;
$cnt = 0;
$cntr = 0;
do {
if ((! $zip == "") && (! $dist <= 0)) {
$sql = get_zip_sql($lon1d,$lat1d,$dist,$numtoshow);
$rs = connexec($conn,$sql);
$rsf = rsfetchrs($rs);
$dist *= 2;
$cntr++;
} else {
$cntr = 10;
}
} while (count($rsf) < $numadvisorstoshow && $cntr < 10);
Hopefully you get the idea.
As was suggested, you can narrow the results using a box to make the query
perform better, and then sort by distance to get the closest alternative.
Here's the related part of get_zip_sql():
function get_zip_sql($lon1d,$lat1d,$dist,$numtoshow)
{
$sql = "
SELECT DISTINCT
FROM tbl_a AS a
,tbl_d AS d
,tbl_a_zipcodes AS az
,tbl_zipcodes as z
WHERE
abs(z.lat - $lat1d) * 60 * 1.15078 <= $dist
and abs(z.long - $lon1d) * 60 * 1.15078 <= $dist
and zipdist($lat1d,$lon1d,lat,long) <= $dist
and z.zip = az.zipcode
ORDER BY
LIMIT $numtoshow;
";
return $sql;
}
The "X * 60 * 1.15078" converts differences in degrees lat/long into rough
distances in miles. The zipdist function returns a more-or-less exact distance
using the Haversine formula.
Hope this helps. Let me know if you want/need more explanation of any of this.
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] DISTINCT
Hello, I'd like to select from one table ( contains duplicates ) into a new table. However the new table is exactly the same as the original ( duplicates included ) I think I have a problem with my DISTINCT clause - sql below. It seems the DISTINCT is applied to the whole row not just the 'phone' field. Is the a way I can select the whole row into a new table based on a DISTINCT phone number ? Is the a way I can select the whole row into a new table based on a DISTINCT phone number and state ? Else I'll need to select into a table then select again etc,etc to remove duplicates. SELECT DISTINCT phone, company_name, address, state, idc_desc_1, street_number, street_name, street_xtra, suburb, postcode, area_code, ac_phone, fax_area_code, fax_phone, fax_ac_phone, latitude, longitude INTO export_temp FROM export; Thanks Rudi ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] DISTINCT
> SELECT > DISTINCT phone, > company_name, > address, > state, > idc_desc_1, > street_number, > street_name, > street_xtra, > suburb, > postcode, > area_code, > ac_phone, > fax_area_code, > fax_phone, > fax_ac_phone, > latitude, > longitude > INTO export_temp > FROM export; SELECT DISTINCT ON (phone) phone, company_name, address, state, idc_desc_1, street_number, street_name, street_xtra, suburb, postcode, area_code, ac_phone, fax_area_code, fax_phone, fax_ac_phone, latitude, longitude INTO export_temp FROM export; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] DISTINCT
Christopher, Thank you. I though if I'm lucky Chris will be online and will see that one. Kind regards Rudi. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] DISTINCT ON
Hi, Just quickly, DISTINCT ON - Is that Postgresql only ? Thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] DISTINCT ON
Don't know. I've seen it elsewhere so it might even be "standard". Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Rudi Starcevic > Sent: Friday, 13 September 2002 10:50 AM > To: [EMAIL PROTECTED] > Subject: [SQL] DISTINCT ON > > > Hi, > > Just quickly, DISTINCT ON - Is that Postgresql only ? > Thanks > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 3: 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
