Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!c03.atl99!news.webusenet.com!newsfeed.icl.net!newsfeed.fjserv.net!diablo.theplanet.net!news.indigo.ie!not-for-mail Message-ID: <3ED72FCB.6050509@fotonation.com> From: Ilariu Raducan User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.theory Subject: Re: view with a rownum References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 54 Date: Fri, 30 May 2003 11:17:47 +0100 NNTP-Posting-Host: 62.77.187.108 X-Complaints-To: abuse@eircom.net X-Trace: news.indigo.ie 1054289868 62.77.187.108 (Fri, 30 May 2003 11:17:48 BST) NNTP-Posting-Date: Fri, 30 May 2003 11:17:48 BST Organization: Eircom.Net http://www.eircom.net Xref: core-easynews comp.databases.theory:26610 X-Received-Date: Fri, 30 May 2003 03:17:07 MST (news.easynews.com) That will work for Oracle. In MS Jet Engine(used by Microsoft Access) rownum is not defined My query evolved to: CREATE VIEW view3 AS SELECT p.file_name,r.num_total,r.num_detected,r.num_false,count(*) AS rid FROM pictures p, results r WHERE r.picture_id=p.picture_id AND r.version_id=2 GROUP BY r.result_id,p.file_name,r.num_total,r.num_detected,r.num_false;" But rid is now 1 for all rows. Something is missing. Lale Andy wrote: > Try > > CREATE VIEW view3 AS > SELECT *, rownum FROM ( your select without rownumber but with join ) > > The rownum is then the rownum of the record from the sub-query. > > It's a technique worth remembering as you can also use this technique to get > rownum for an ordered data set - the subquery can be ordered, the rownum > being "added" to the final dataset after the ordering rather than before as > is normally the case. > > Andy > > "Ilariu Raducan" wrote in message > news:JDrBa.15762$pK2.21592@news.indigo.ie... > >>Hi All, >> >>I'm trying to create a view from two tables that will have a column >>rownumber autoincrement. >>Don't laugh, all that in Microsoft Jet Engine. >> >>CREATE VIEW view3 AS >>SELECT p.file_name,r.num_total,r.num_detected,r.num_false,rownumber >>FROM pictures p, results r WHERE r.picture_id=p.picture_id AND >>r.version_id=2; >> >>Is it possible to do that? >> >>Thank you, >>Lale >> > > >