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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
