I'll agree the three levels of subquery may be needed for distinct but I think the row_number() function is still more reliable than "rownum". similar tests work fine:
SELECT * FROM (SELECT tmp.*, row_number() over (order by tmp.path) as ora_rn FROM (SELECT DISTINCT tblk.path AS path FROM cms_dbs_int_global.block tblk) tmp) WHERE ora_rn between 0 and 10 This would be a little bit of extra wrapping performed in oracle.visit_select(). On Mar 25, 2008, at 4:35 PM, vkuznet wrote: > > Ok, here is an example in ORACLE. > Table schema (for simplicity I removed unnecessary columns): > > CREATE TABLE Block > ( > ID integer, > Name varchar(500) unique not null, > Path varchar(500) not null, > primary key(ID) > ); > So the path column can contains repeated values. Here is the code > using limit/offset: > t = self.getTable(dbAlias,'Block','tblk') > sel = > sqlalchemy > .select([t.c.path],from_obj=[t],distinct=True,limit=10,offset=0) > print sel > res = con.execute(sel) > for item in res: > print "result from Block",item > > and here is result: > > SELECT path > FROM (SELECT DISTINCT tblk.path AS path, ROW_NUMBER() OVER (ORDER BY > tblk.id) AS ora_rn > FROM cms_dbs_int_global.block tblk) > WHERE ora_rn>0 AND ora_rn<=10 > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW',) > > It does not select 10 different distinct paths. > > While if I'll do > sel = sqlalchemy.select([t.c.path],from_obj=[t],distinct=True) > tmp = sel.alias('tmp') > q = sqlalchemy.select(['tmp.*','rownum as > rnum'],from_obj=[tmp]) > sel = sqlalchemy.select(['*'],from_obj=[q]) > sel.append_whereclause( 'rnum between %s and %s'%(0,10) ) > print sel > res = con.execute(sel) > for item in res: > print "result from Block",item > > I'll get 10 different distinct paths: > > SELECT * > FROM (SELECT tmp.*, rownum as rnum > FROM (SELECT DISTINCT tblk.path AS path > FROM cms_dbs_int_global.block tblk) tmp) > WHERE rnum between 0 and 10 > result from Block ('/CSA07AllEvents/CMSSW_1_6_6-HLTSplit-A3-Stew/GEN- > SIM-DIGI-RAW', 1) > result from Block ('/GammaJetIsoPi0_Pt55to65_ptHat60/CMSSW_1_6_7- > CSA07-4067/GEN-SIM-DIGI-RAW', 2) > result from Block ('/LM3_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/ > AODSIM', 3) > result from Block ('/LM7_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/ > AODSIM', 4) > result from Block ('/LM6_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/ > AODSIM', 5) > result from Block ('/RS1GravitonZZ4Mu_1500GeV_01/CMSSW_1_6_7- > CSA07-3199/GEN-SIM-DIGI-RAW', 6) > result from Block ('/SingleMuPlusPt100To400/CMSSW_1_6_7- > HLT-1193394942/ > GEN-SIM-DIGI-RECO', 7) > result from Block ('/LM2_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/ > AODSIM', 8) > result from Block ('/LM8_isasdkpyt/CMSSW_1_6_5-FastSim-SUSYBSM-1234/ > AODSIM', 9) > result from Block ('/RS1GravitonZZ4Mu_1500GeV_01/CMSSW_1_4_6- > CSA07-2644/GEN-SIM', 10) > > Valentin. > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---