X-Original-To: pgsql-sql@postgresql.org Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id D64FF475921 for ; Fri, 14 Mar 2003 12:04:44 -0500 (EST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50] (may be forged)) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h2ELEiIs010341; Fri, 14 Mar 2003 19:14:44 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h2ELEiQl010337; Fri, 14 Mar 2003 19:14:44 -0200 Date: Fri, 14 Mar 2003 19:14:44 -0200 (GMT+2) From: Achilleus Mantzios To: "Sill-II, Stephen" Cc: pgsql-sql@postgresql.org Subject: Re: look for latest user login in one table from a list of In-Reply-To: <4BAE87828F06EB45B2641B83D370350F4663@ostex002.ad.ost.dot.gov> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200303/366 X-Sequence-Number: 12405 On Fri, 14 Mar 2003, Sill-II, Stephen wrote: > 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"; Try foo=# SELECT users.name,someq.mlast from users left outer join (select id,max(last) as mlast from log group by id) as someq using(id); substituting with your names. > > 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 > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr