[SQL] look for latest user login in one table from a list of users in another
I'm trying to come up with an efficient way to do the following task, but I haven't found the correct join syntax to do it. I have table "users" for keeping a list of users I need to check logins for. It contains the following fields: id,Full-Name,User-Name I have table called "logs" that contains the actual radius log data. The three fields I am concerned with are: User-Name,Date,Time I have gotten thus far almost what I want with the following query. SELECT users."User-Name",max(logs."Date") as login_date,max(logs."Time") as login_time FROM logs where logs."User-Name"=users."User-Name" GROUP BY users."User-Name"; This gives me the User-Name, date, and time of people WHO HAVE LOGGED IN. I'm looking to have a query that returns all of the users in the first table, including those who have not logged in. I have an external perl script that generates a nice html report for the manager. I have a script that parses the raw .csv files, but I'm trying to move it entirely to postgres, including if possible stored-procedures in plperl. I already have a perl script that imports the raw log files into the logs table. Am I on the right track? Thanks, Stephen Sill II ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] What's wrong with this group by clause?
Thanks Tom, I applied the patch and it works perfect now.
Thanks to you all.
On Thursday 13 March 2003 14:02, Tom Lane wrote:
> Manfred Koizar <[EMAIL PROTECTED]> writes:
> > ISTM you have found a Postgres 7.3 bug.
>
> Yeah. Actually, the planner bug has been there a long time, but it was
> only latent until the parser stopped suppressing duplicate GROUP BY
> items:
>
> 2002-08-18 14:46 tgl
>
> * src/backend/parser/parse_clause.c: Remove optimization whereby
> parser would make only one sort-list entry when two equal()
> targetlist items were to be added to an ORDER BY or DISTINCT list.
> Although indeed this would make sorting fractionally faster by
> sometimes saving a comparison, it confuses the heck out of later
> stages of processing, because it makes it look like the user wrote
> DISTINCT ON rather than DISTINCT. Bug reported by
> [EMAIL PROTECTED]
>
> 7.3 patch is attached if you need it.
>
> regards, tom lane
>
>
> *** src/backend/optimizer/plan/planner.c.orig Wed Mar 5 13:38:26 2003
> --- src/backend/optimizer/plan/planner.c Thu Mar 13 11:21:16 2003
> ***
> *** 1498,1510
>* are just dummies with no extra execution cost.)
>*/
> List *sort_tlist = new_unsorted_tlist(subplan->targetlist);
> int keyno = 0;
> List *gl;
>
> foreach(gl, groupClause)
> {
> GroupClause *grpcl = (GroupClause *) lfirst(gl);
> ! TargetEntry *te = nth(grpColIdx[keyno] - 1, sort_tlist);
> Resdom *resdom = te->resdom;
>
> /*
> --- 1498,1511
>* are just dummies with no extra execution cost.)
>*/
> List *sort_tlist = new_unsorted_tlist(subplan->targetlist);
> + int grpno = 0;
> int keyno = 0;
> List *gl;
>
> foreach(gl, groupClause)
> {
> GroupClause *grpcl = (GroupClause *) lfirst(gl);
> ! TargetEntry *te = nth(grpColIdx[grpno] - 1, sort_tlist);
> Resdom *resdom = te->resdom;
>
> /*
> ***
> *** 1518,1523
> --- 1519,1525
> resdom->reskey = ++keyno;
> resdom->reskeyop = grpcl->sortop;
> }
> + grpno++;
> }
>
> Assert(keyno > 0);
pgp0.pgp
Description: signature
Re: [SQL] Poor performance on a right join
> When doing a join query I am getting a responce time of 3 seconds. The > callhist table has 66000 rows and the phone table has 1 rows. I > have an indexes on callhist.call_date, callhist.operator_id, > phone.phone, & phone.cust_code. Here's the SQL > SELECT a.CALL_DATE, a.OPERATOR_ID, a.CUST_CODE FROM PHONE as b right > join CALLHIST as a on (a.CUST_CODE=b.CUST_CODE) where (b.PHONE = > '555-555-') order by a.call_date desc; > The postgres db is running on a 2 P3 700 processor server with 1GB of > ram running Red Hat Linux 7.3. I am running PostgreSQL 7.2.2 > > Why is the query taking so long? What can I do to help the > performance? We'll need the output of EXPLAIN ANALYSE SELECT ... Perhaps table definitions too. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] What's wrong with this group by clause?
On Thu, 13 Mar 2003 01:34:34 -0600, "Len Morgan" <[EMAIL PROTECTED]> wrote: >>GROUP BY > > field1, > > field2, > >name; >I think the problem is that you don't have a column to group on. field1, field2, and name are the grouping columns. >Try adding >SELECT ,count(*) so that there is an aggregate of some kind. You don't need an aggregate in a GROUP BY query. A SELECT ... GROUP BY without any aggregate behaves like SELECT DISTINCT. There's nothing wrong with it. Performance might be a different story. BTW, Franco's problem has been recognised as a bug and a patch has already been published (cf. Tom Lane's mail in this thread). Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] View - Join based on dis-similar data types
On Thursday 13 Mar 2003 1:48 pm, speakeasy wrote: > Not sure if this ever made it to the group, I can't seem to find it in the > recent message lists. > > "speakeasy" <[EMAIL PROTECTED]> wrote in message news:... > > > I have a field defined as a character(50) data type, and the same field > > stored in a transition table as a text type. > > > > The view itself work based on the join, however sub-queries against the > view > > on that field do not return any data. > > > > Example: > > > > Table1 > > T1Data - Character(50) > > Table2 > > T2Data - Text > > View1: > > --- > > SELECT T1Data from Table1 JOIN Table2 ON (Table1.T1Data = Table2.T2Data); > > > > > > A select against View1 > > SELECT * FROM View1 returns all relevant records, however, adding a WHERE > > clause produces no output. Can you please provide a real example, otherwise we're just guessing. Are you sure you've not forgotten about the spaces padding your char(50) field? -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Deleting large object from table pg_largeobject
On Wednesday 12 Mar 2003 8:59 pm, David wrote: > Hello, > > I'm trying to delete a large object form the pg_largeobject table but I > get a permission denied error (in both psql and with the java JDBC ). > > Here is my sql stament in order: > > fletcher=> SELECT * from md_blob ; > blob_id | text | file_name | mime_type > -++---+ >4 | 292470 | 2.jpg | image/jpeg > 21 | 292572 | 2.jpg | image/jpeg > 22 | 292578 | 2.jpg | image/jpeg > (3 rows) > > == If I try to delete it directly > fletcher=> delete from pg_largeobject where loid = ( > select text from md_blob > where blob_id = 4 ); > > ERROR: pg_largeobject: permission denied Should you not be using the special large-object methods? I don't use Java, but there's a class mentioned in the manual : Client Interfaces : JDBC : extensions. With psql you probably want \lo_unlink HTH -- Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
