Article View: borland.public.interbase.sql
Article #11377Re: Selecting row in which a column value is maximum
From: Craig Stuntz TeamB
Date: Mon, 25 Nov 2002 16:35
Date: Mon, 25 Nov 2002 16:35
25 lines
2290 bytes
2290 bytes
In article <3de24925@newsgroups.borland.com>, eddielee@tropicsoft.com says... > I don't see what the difference between a descending and ascending index is > in this situation. I think the key point is that there be an index so that > each row doesn't have to be read to find the maximum value. > IB can use a DESCENDING INDEX to find a MAX value, but it can't use an ASCENDING INDEX to find a MAX. (Conversely, it can use an ASCENDING INDEX for a MIN value, but not a DESCENDING INDEX.) The reason is that IB prefix-compresses the index, and it would have to walk the entire index to get to the max node. It would then have to determine if that row (or rows) was visible to the current transaction, and "back out" if not. Since you can create a DESCENDING INDEX rather easily, it's not a huge limitation. -Craig -- Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH Delphi/InterBase Weblog : http://delphi.weblogs.com InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer
Technical Details
Message-ID:
<MPG.184c1b6154274d7b98a260@newsgroups.borland.com>Path:
arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-03!sn-xit-01!sn-xit-08!supernews.com!newsfeed.news2me.com!newsfeed.media.kyoto-u.ac.jp!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.freenet.de!eusc.inter.net!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mailReferences:
<3de051cf@newsgroups.borland.com> <3de0d0f0$1@newsgroups.borland.com> <MPG.184c120210a6b55a98a253@newsgroups.borland.com> <3de24925@newsgroups.borland.com>Show Full Headers
Complete Article Headers
X-Google-Language: ENGLISH,ASCII X-Google-Thread: 114a0e,60a1aedc82f6444 X-Google-Attributes: gid114a0e,public X-Google-ArrivalTime: 2002-11-25 08:39:56 PST From: Craig Stuntz [TeamB] <cstuntz@nospamplease.com (but use vertexsoftware to email)> Newsgroups: borland.public.interbase.sql Subject: Re: Selecting row in which a column value is maximum Date: Mon, 25 Nov 2002 11:35:34 -0500 Message-ID: <MPG.184c1b6154274d7b98a260@newsgroups.borland.com> References: <3de051cf@newsgroups.borland.com> <3de0d0f0$1@newsgroups.borland.com> <MPG.184c120210a6b55a98a253@newsgroups.borland.com> <3de24925@newsgroups.borland.com> Organization: Vertex Systems Corporation X-Newsreader: MicroPlanet Gravity v2.60 NNTP-Posting-Host: 65.86.87.242 X-Trace: newsgroups.borland.com 1038242041 65.86.87.242 (25 Nov 2002 08:34:01 -0800) Lines: 25 Path: arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!sn-xit-02!sn-xit-03!sn-xit-01!sn-xit-08!supernews.com!newsfeed.news2me.com!newsfeed.media.kyoto-u.ac.jp!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.freenet.de!eusc.inter.net!newsfeed01.sul.t-online.de!t-online.de!newsgroups.borland.com!not-for-mail