-- Find specific types of quasars using spectroscopy.  
-- Get those with with a line width >2000 km/s 2.5 < z < 2.7. This will use
-- SpecObj tables, with a simple multi-constraint WHERE clause. We introduce
-- the use of a function, in this case dbo.fSpecClass, to select objects by
-- named types instead of using the bitwise flags.

SELECT  s.specObjID, max(l.sigma*300000.0/l.wave) kmps,  avg(s.z) as z  
-- get the object ID, the maximum linewidth in km/s, and redshift
into mydb.sampletest
FROM    SpecObj s, specLine l          -- from the spectrum table. 
WHERE  s.specObjID=l.specObjID 	       -- and it has lines in the line table
   and ( (s.specClass = dbo.fSpecClass('QSO'))    -- and it is a quasar 
      or  (s.specClass = dbo.fSpecClass('HIZ_QSO'))) -- or hiZ_qso. 
   and  s.z between 2.5 and 2.7   -- redshift of 2.5 to 2.7
   and  l.sigma*300000.0/l.wave >2000.0     -- convert sigma to km/s         
   and  s.zConf > 0.9                     -- high confidence on z estimate 
   GROUP BY s.specObjID		  
-- since there are multiple lines per object, we must eliminate duplicates
