Re: [SQL] need assistance with multi-row matching expression
Mark- This may not be the best way, but I couldn't resist taking a shot at it... If I understand correctly, your user selects 0-n features, so you are essentially querying against park_feature_map with your known parameters being the number of features and a feature_id list. suppose your park_feature_map was created like this: create table pfm (pid integer, fid integer); If a user wants all of the parks with features 1,2 & 3 then the feature list is (1,2,3) and the number of features is 3. I think this select would work: select case when count(pid) = 3 then pid end from pfm where fid in (1,2,3) group by pid or to illustrate the query better, you could use this: select pid, case when count(pid) = 3 then 'yes' else 'no' end from pfm where fid in (1,2,3) group by pid; It seems like you might also want to rank matches, so you could also do: select pid, count(pid) from pfm where fid in (1,2,3) group by pid order by count(pid) desc; The last one doesn't pinpoint matches, but might end up making a better user interface. You could combine the two to only list parks with at least N-1 matches like so: select case when count(pid) > (3-1) then pid end from pfm where fid in (1,2,3) group by pid order by count(pid) desc; -Nick ------ Nick Fankhauser [EMAIL PROTECTED] Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Mark Stosberg > Sent: Monday, August 19, 2002 10:21 AM > To: [EMAIL PROTECTED] > Subject: [SQL] need assistance with multi-row matching expression > > > > Hello, > > I'm using Postgres 7.1 and have been enjoying the upgraded "sub-select" > support in this release versus older versions. > > At the moment, I'm stuck with a SQL issue that I haven't run into > before. > > I need to select the data for all the "parks" that match some search > criteria. The parks are stored in a "parks" table with a park_id as a > primary key. > > Part of the search includes the logic of "match parks that include all > these features". The "features" are stored in their own table, and are > related to the parks table with a park_feature_map table, which contains > a park_id column and a feature_id column. > > A user can use 0 to N to features, and each park might have 0 to N > entries in the park_feature_map table. > > Where I'm stuck is that I'm used to putting together SQL statements to > match a given row. This is different-- to create a successful match for > a park_id, I need to check to match against N rows, where N is the > number of feature_ids provided. > > How do I do that? Can I do it in one query? > > Thanks! > > -mark > > http://mark.stosberg.com/ > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Is there a more elegant way to write this query?...
Hi-
I'm suffering from a performance problem, but when I look at my query, I'm
not convinced that there isn't a better way to handle this in SQL. -So I'm
seeking advice here before I go to the performance list.
I have three tables- case, actor and actor_case_assignment.
As the names imply, actor_case_assignment contains records that assign an
actor to a case. Actors such as attorneys or judges may have many cases,
while the average actor (we hope) only has one.
What I'm trying to do is link these tables to get back a single row per
actor that shows the actor's name, the number of cases that actor is
assigned to, and if they only have one case, I want the number for that
case. This means I have to do some grouping to get the case count, but I'm
then forced to use an aggregate function like max on the other fields. I
hope there's a better way. Any suggestions?
Here's what I'm using right now:
select
actor.actor_id,
max(actor.actor_full_name),
max(case_data.case_public_id),
max(case_data.case_id),
count(case_data.case_id) as case_count
from
actor,
actor_case_assignment,
case_data
where
actor.actor_full_name_uppercase like upper('martin%')
and actor.actor_id = actor_case_assignment.actor_id
and case_data.case_id = actor_case_assignment.case_id
group by
actor.actor_id
order by
max(actor.actor_full_name),
case_count desc,
limit
1000;
Thanks!
-Nick
-----
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is there a more elegant way to write this query?...
Hi Eric- Thanks for your suggestion. > An explain analyze would help. I'll do that (and move the question to the performance list) when I get to the performance question, but at this point, I'm just seeking some help in looking at this from a different angle- I couldn't figure out how to achieve the result I was after without using max(), which felt like a kludge. Your solution gave me the start I needed. -Nick > How about: > select > actor.actor_full_name, > actor.actor_id, > s1.ctCases, > s1.case_id, > case_data.case_public_id > from > actor inner join ( select actor_id, count(*) as ctCases, > max(case_id) > as case_id > from > actor_case_assignment group by actor_id) as s1 > on (actor.actor_id = s1.actor_id) >left outer join case_data using > (s1.case_id=case_data.case_id) > limit 1000; ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] interesting SQL puzzle - concatenating column with itself.
Hi- I'm doing a conversion from an older database in which a memo field was handled by storing one line per record and then displaying the related records in order. I want to compress all of the lines into a single text field with one record per memo entry. So for instance, the old database looks like this: memo_id | sequence | memo_text --- 666 | 1| The quick 666 | 2| red fox 666 | 3| jumped over 666 | 4| the lazy brown dog And my goal is to transform each group of lines into a single record that looks like this: memo_id | memo_text -- 666 | The quick red fox jumped over the lazy brown dog Any thoughts on how to do this via sql? I could write a little program to do it, but it seems like there must be a pure-SQL solution that I'm not seeing. Thanks -Nick -- -- Nick Fankhauser [EMAIL PROTECTED] Phone 765.935.4283 Fax 765.962.9788 Ray Ontko & Co. - Software Consulting Services http://www.ontko.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
Re: [SQL] interesting SQL puzzle - concatenating column with itself.
Excellent! Thanks for providing both the idea and an example. I didn't get the idea right away, but the example made it clear. I'll try that on my table and report back on how it works out. Regards, -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 765.935.4283 Fax 765.962.9788 Ray Ontko & Co. - Software Consulting Services http://www.ontko.com Alvaro Herrera wrote: You can create a custom aggregate to do concatenation and group by memo_id. create or replace function text_cat(text, text) returns text called on null input language sql immutable as 'select case when $1 is null then $2 when $2 is null then $1 else $1 || '' '' || $2 end'; create aggregate textcat (basetype = text, sfunc = text_cat, stype = text); create table memos (memo_id int, sequence int, memo_text text); insert into memos values (666, 3, 'jumped over'); insert into memos values (666, 1, 'The quick'); insert into memos values (666, 4, 'the lazy brown dog'); insert into memos values (666, 2, 'red fox'); select memo_id, textcat(memo_text) from (select * from memos order by memo_id, sequence) as foo group by memo_id; The order is not really guaranteed, though if this is a one-shot thing, you may get away with turning off hashed aggregates. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] interesting SQL puzzle - concatenating column with itself.
Alvaro Herrera wrote: The order is not really guaranteed, though if this is a one-shot thing, you may get away with turning off hashed aggregates. When I read this, I assumed there was a runtime parameter I could set that was similar to ENABLE_HASHJOIN. Are you referring to a different runtime parameter or something else entirely? BTW the concatenation function you suggest works nicely except that as you noted, it concatenates in an unpredictable order, so I'm now trying to solve that problem. -Nick -- Nick Fankhauser [EMAIL PROTECTED] Phone 765.935.4283 Fax 765.962.9788 Ray Ontko & Co. - Software Consulting Services http://www.ontko.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] Is there a more elegant way to write this query?...
Hi-
I'm suffering from a performance problem, but when I look at my query, I'm
not convinced that there isn't a better way to handle this in SQL. -So I'm
seeking advice here before I go to the performance list.
I have three tables- case, actor and actor_case_assignment.
As the names imply, actor_case_assignment contains records that assign an
actor to a case. Actors such as attorneys or judges may have many cases,
while the average actor (we hope) only has one.
What I'm trying to do is link these tables to get back a single row per
actor that shows the actor's name, the number of cases that actor is
assigned to, and if they only have one case, I want the number for that
case. This means I have to do some grouping to get the case count, but I'm
then forced to use an aggregate function like max on the other fields. I
hope there's a better way. Any suggestions?
Here's what I'm using right now:
select
actor.actor_id,
max(actor.actor_full_name),
max(case_data.case_public_id),
max(case_data.case_id),
count(case_data.case_id) as case_count
from
actor,
actor_case_assignment,
case_data
where
actor.actor_full_name_uppercase like upper('martin%')
and actor.actor_id = actor_case_assignment.actor_id
and case_data.case_id = actor_case_assignment.case_id
group by
actor.actor_id
order by
max(actor.actor_full_name),
case_count desc,
limit
1000;
Thanks!
-Nick
-----
Nick Fankhauser
[EMAIL PROTECTED] Phone 1.765.965.7363 Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
