Re: Refining query statement
От | Ron |
---|---|
Тема | Re: Refining query statement |
Дата | |
Msg-id | 26851021-69e3-36e4-170d-40ed7219a180@gmail.com обсуждение исходный текст |
Ответ на | Re: Refining query statement (Thomas Kellerer <spam_eater@gmx.net>) |
Ответы |
Re: Refining query statement
(Rich Shepard <rshepard@appl-ecosys.com>)
Re: Refining query statement (Adrian Klaver <adrian.klaver@aklaver.com>) Re: Refining query statement (Rob Sargent <robjsargent@gmail.com>) |
Список | pgsql-general |
On 1/15/19 9:47 AM, Thomas Kellerer wrote: > Rich Shepard schrieb am 15.01.2019 um 16:39: >> Working with my sales/client management system using psql I have a select >> statement to identify contacts to be made. This statement works: >> >> select (C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact) >> from Contacts as C, Organizations as O, Activities as A >> where C.org_id = O.org_id and C.contact_id = A.contact_id and >> A.next_contact <= 'today' and A.next_contact > '2018-12-31' and >> A.next_contact is not null; >> >> but would benefit from tweaking. When I have had multiple contacts with >> someone I want only the most recent one displayed, not all, and they should >> be no more ancient than a defined period (e.g., a month). >> >> I want to learn how to make this query cleaner and more flexible. When I >> write the UI for this I want to be able to specify a data range in addition >> to a fixed 'today'. Pointers on what to read will be very helpful. > With regards to "cleaner": the first thing to do is to remove the parentheses around the column list. > In Postgres "(a,b,c)" creates a single column with an anonymous record type (that contains three fields), rather than selectingthree columns. > In other DBMS those parentheses are simply useless. > > "cleaner" is always subjective, but I find explicit JOIN operators a lot cleaner than the old implicit joins. > > The condition "A.next_contact is not null" is actually no necessary because you already have a condition on that column,so NULL values won't be returned anyway. > > To get the "most recent one" in Postgres, DISTINCT ON () is usually the best way to do it: > > So we end up with something like this: > > select distinct on (C.contact_id) C.contact_id, C.lname, C.fname, C.direct_phone, O.org_name, A.next_contact > from Contacts as C > join Organizations as O on C.org_id = O.org_id > join Activities as A on C.contact_id = A.contact_id > where A.next_contact <= 'today' > and A.next_contact > '2018-12-31' > order by c.contact_id, a.next_contact DESC; And I've never liked this method (though I'm old and crotchety).... -- Angular momentum makes the world go 'round.
В списке pgsql-general по дате отправления: