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
-~----------~----~----~----~------~----~------~--~---
