Startseite       Werkzeuge      Schema Browser    Projekte     Astronomie     SDSS       Danksagung       Download       Hilfe     
DR6 Help
 Archive Intro
 Table Descriptions
 Schema Browser
 Glossary
 Algorithms
 Introduction to SQL
     - Sample Queries
 Form Query User Guide
 Query Limits
 How To
 FAQ
 API
 sdssQA
 Download
 SkyServer Sites
 SkyServer Traffic Page
 Web Browsers
 Site News
 Contact Help Desk
Sample SQL Queries

The following is a selection of actual queries submitted by SDSS users, and some are in response to scientific questions posed by users. The queries are listed in increasing order of difficulty/complexity. Where applicable, query execution times for the latest SDSS data releases are noted.

NOTE: Please also read the Optimizing Queries and Bookmark Lookup Bug sections of the SQL Intro page to learn how to run faster queries, and the Query Limits page to see the timeouts and row limits on queries.

Click on the name of the query from the list below to go directly to that sample query. The queries are roughly in order of increasing complexity. You can cut and paste queries from here into your favorite search tool.

  • Basic SELECT-FROM-WHERE
  • Galaxies with two criteria
  • Unclassified spectra
  • Galaxies with multiple criteria
  • Spatial unit vectors
  • CVs using colors
  • Data subsample
  • Low z QSOs by colors
  • Velocities and errors
  • Using BETWEEN
  • Moving asteroids
  • Quasars in imaging
  • Selected neighbors in run
  • Multiple OUTER JOINs
  • Repeat spectra
  • Special program targets
    		•
  • Repeated high-z objects
  • Object counts and logic
  • Galaxy star blends
  • Stars in specific fields
  • Using three tables
  • Objects close pairs
  • QSOs in spectroscopy
  • Errors using flags
  • Elliptical galaxies
  • Galaxies blue centers
  • Diameter limited sample
  • Extremely red galaxies
  • LRG sample
  • Brightness of closest source
  • Multiple spectral lines
  • Special program data
    		•
  • Galaxies by spectra
  • Clean photometry
  • Binary stars colors
  • QSO broadlines near galaxy
  • Galaxies unsaturated
  • Ellipticals with odd lines
  • Broadest spectral lines
  • Gridded galaxy counts
  • Galaxy counts on HTM grid
  • Stars multiply measured
  • White Dwarf candidates
  • More quasar queries
  • Using LEFT OUTER JOIN
  • Galaxy counts in North
  • Counts by type and program
  • Spatial Queries using HTM
    		•

    Some general hints:

    1. If you're not sure how many objects a query is going to return, it's always a good idea to first do a "count" query, e.g. "SELECT count(*) FROM Galaxy WHERE ..." so as to get an idea of how many objects will be returned, so you don't find yourself waiting a lot longer than you expected to.
    2. If even a count takes a long time, this is a good indication that the actual query will take a much longer time to run, so you should check if you have formulated the query correctly and in the most efficient way.
    3. If a query takes much longer to run than you think it should, you should try it at a different time again to make sure that server load is not the main reason why it ran much slower the first time.
    4. A good way to find if there are any objects satisfying a given query is to use the "TOP <n>" SQL construct, e.g. "SELECT TOP 100 FROM ...", which will only return the up to the first 100 matching objects.
    5. If your query returns a lot of output (more than a few thousand objects), it is generally not a good idea to let the browser render the output by selecting the HTML output format (default) in the SQL Search page of SkyServer. You can try using the CSV output format instead of HTML in the browser for large outputs. However, you're much better off using one of the other interfaces (Emacs, sdssQA, sqlcl or CasJobs) to get large rowsets. Browsers are generally very slow in rendering large outputs, and this also slows down the webserver for other users.
    6. Be sure to exclude invalid values (unset or uncalculated quantities) as described in the SQL help under Excluding Invalid Data Values.

    Basic SELECT-FROM-WHERE      Back to Top

    -- Returns 5261 objects in DR2 (5278 in DR1) in a few sec.

    -- Find objects in a particular field.
    -- A basic SELECT - FROM - WHERE query.

    SELECT objID, -- Get the unique object ID,
    field, ra, dec
    		-- the field number, and coordinates
    FROM PhotoObj -- From the photometric data
    WHERE run=1336 and field = 11 -- that matches our criteria

    Galaxies with two criteria      Back to Top

    -- Returns 1000 objects in a few sec.

    -- Find all galaxies brighter than r magnitude 22, where the local
    -- extinction is > 0.175. This is a simple query that uses a WHERE clause,
    -- but now two conditions that must be met simultaneously. However, this
    -- query returns a lot of galaxies (29 Million in DR2!), so it will take a
    -- long time to get the results back. The sample therefore includes a
    -- "TOP 1000" restriction to make it run quickly.
    -- This query also introduces the Galaxy view, which contains the
    -- photometric parameters (no redshifts or spectra) for unresolved objects.

    SELECT TOP 1000 objID
    FROM Galaxy
    WHERE
    r < 22
    		-- r IS NOT deredenned
    and extinction_r > 0.175
    		-- extinction more than 0.175

    Unclassified spectra      Back to Top

    -- Find all objects with unclassified spectra.
    -- A simple SELECT-FROM-WHERE query, using a function

    SELECT specObjID
    FROM SpecObj
    WHERE SpecClass = dbo.fSpecClass('UNKNOWN')

    Galaxies with multiple criteria      Back to Top

    -- Find all galaxies with blue surface brightness between 23 and 25
    -- mag per square arcseconds, and -10 < supergalactic latitude (sgb) < 10, and
    -- declination less than zero. Currently, we have to live with ra/dec until we
    -- get galactic coordinates. To calculate surface brightness per sq. arcsec,
    -- we use (g + rho), where g is the blue magnitude, and rho= 5*log(r). This
    -- query now has three requirements, one involving simple math.

    SELECT objID
    FROM Galaxy
    WHERE ra between 250 and 270
    and dec > 50
    and (g+rho) between 23 and 25
    		-- g is blue magnitude, and rho= 5*log(r)

    Spatial unit vectors      Back to Top

    -- Find galaxies in a given area of the sky, using a coordinate cut
    -- in the unit vector cx,cy,cz that corresponds to RA beteen 40 and 100.
    -- Another simple query that uses math in the WHERE clause.

    SELECT colc_g, colc_r
    FROM Galaxy
    WHERE (-0.642788 * cx + 0.766044 * cy>=0)
    and (-0.984808 * cx - 0.173648 * cy <0)

    Cataclysmic variables (CVs) using colors      Back to Top

    -- Search for Cataclysmic Variables and pre-CVs with White Dwarfs and
    -- very late secondaries. Just uses some simple color cuts from Paula Szkody.
    -- Another simple query that uses math in the WHERE clause

    SELECT run,
    camCol,
    rerun,
    field,
    objID,
    u, g, r, i, z,
    ra, dec
    		-- Just get some basic quantities
    FROM PhotoPrimary -- From all primary detections, regardless of class
    WHERE u - g < 0.4
    and g - r < 0.7
    and r - i > 0.4
    and i - z > 0.4
    		-- that meet the color criteria

    Data subsample      Back to Top

    -- Give me the colours of a random 1% sample of objects from all fields
    -- which are 'survey quality' so that I could plot up colour-colour diagrams
    -- and play around with more sophisticated cuts. From Karl Glazebrook. Uses
    -- the HTM spatial index ID to apply the cut against. Replace the last '1' by
    -- a different number if you want to sample a different percentage of objects.

    SELECT u, g, r, i, z FROM Galaxy
    WHERE htmid*37 & 0x000000000000FFFF < (650 * 1)

    Low-z QSOs using colors      Back to Top

    -- Low-z QSO candidates using the color cuts from Gordon Richards.
    -- Also a simple query with a long WHERE clause.

    SELECT
    g,
    run,
    rerun,
    camcol,
    field,
    objID
    FROM
    Galaxy
    WHERE ( (g <= 22)
    and (u - g >= -0.27)
    and (u - g < 0.71)
    and (g - r >= -0.24)
    and (g - r < 0.35)
    and (r - i >= -0.27)
    and (r - i < 0.57)
    and (i - z >= -0.35)
    and (i - z < 0.70) )

    Object velocities and errors      Back to Top

    -- Get object velocities and errors. This is also a simple query that uses a WHERE clause.
    -- However, we perform a more complex mathematical operation, using 'power' to
    -- exponentiate. (From Robert Lupton).

    -- NOTE: This query takes a long time to run without the "TOP 1000".

    SELECT TOP 1000
    run,
    camCol,
    field,
    objID,
    rowC, colC, rowV, colV, rowVErr, colVErr,
    flags,
    psfMag_u, psfMag_g, psfMag_r, psfMag_i, psfMag_z,
    psfMagErr_u, psfMagErr_g, psfMagErr_r, psfMagErr_i, psfMagErr_z
    FROM PhotoPrimary
    WHERE
    -- where the velocities are reliable
    power(rowv, 2) / power(rowvErr, 2) +
    power(colv, 2) / power(colvErr, 2) > 4

    Using BETWEEN      Back to Top

    -- Find galaxies with an isophotal surface brightness (SB) larger
    -- than 24 in the red band, and with an ellipticity > 0.5, and with the major
    -- axis of the ellipse having a declination between 30" and 60" arc seconds.
    -- This is also a simple query that uses a WHERE clause with three conditions
    -- that must be met. We introduce the syntax 'between' to do a range search.

    SELECT ObjID
    FROM Galaxy
    WHERE
    r + rho < 24
    		-- red surface brightness more than
    -- 24 mag/sq-arcsec
    and isoA_r between 30 and 60
    		-- major axis between 30" and 60"
    and (power(q_r,2) + power(u_r,2)) > 0.25
    		-- square of ellipticity > 0.5 squared

    Moving Asteroids      Back to Top

    -- Provide a list of moving objects consistent with an asteroid.
    -- Also a simple query, but we introduce the 'as' syntax, which allows us to
    -- name derived quantities in the result file.

    SELECT
    objID,
    sqrt( power(rowv,2) + power(colv, 2) ) as velocity
    FROM PhotoObj
    WHERE
    (power(rowv,2) + power(colv, 2)) > 50
    and rowv >= 0 and colv >=0

    Quasars in imaging      Back to Top

    -- Find quasars as specified by Xiaohui Fan et.al.
    -- A rather straightforward query, just with many conditions. It also introduces
    -- the Star view, which contains the photometric parameters for all primary point-like
    -- objects (including quasars).

    SELECT run,
    camCol,
    rerun,
    field,
    objID,
    u, g, r, i, z,
    ra, dec
    FROM Star -- or Galaxy
    WHERE ( u - g > 2.0 or u > 22.3 )
    and ( i < 19 )
    and ( i > 0 )
    and ( g - r > 1.0 )
    and ( r - i < (0.08 + 0.42 * (g - r - 0.96)) or g - r > 2.26 )
    and ( i - z < 0.25 )

    Selected neighbors in run      Back to Top

    -- This is a query from Robert Lupton that finds selected neighbors in a given run and
    -- camera column. It contains a nested query containing a join, and a join with the
    -- results of the nested query to select only those neighbors from the list that meet
    -- certain criteria. The nested queries are required because the Neighbors table does
    -- not contain all the parameters for the neighbor objects.

    select
    obj.run, obj.camCol, str(obj.field, 3) as field,
    str(obj.rowc, 6, 1) as rowc, str(obj.colc, 6, 1) as colc,
    str(dbo.fObj(obj.objId), 4) as id,
    str(obj.psfMag_g - 0*obj.extinction_g, 6, 3) as g,
    str(obj.psfMag_r - 0*obj.extinction_r, 6, 3) as r,
    str(obj.psfMag_i - 0*obj.extinction_i, 6, 3) as i,
    str(obj.psfMag_z - 0*obj.extinction_z, 6, 3) as z,
    str(60*distance, 3, 1) as D,
    dbo.fField(neighborObjId) as nfield,
    str(dbo.fObj(neighborObjId), 4) as nid,'new' as 'new'
    from
    (select obj.objId,
    run, camCol, field, rowc, colc,
    psfMag_u, extinction_u,
    psfMag_g, extinction_g,
    psfMag_r, extinction_r,
    psfMag_i, extinction_i,
    psfMag_z, extinction_z,
    NN.neighborObjId, NN.distance
    from photoObj as obj
    join neighbors as NN on obj.objId = NN.objId
    where
    60*NN.distance between 0 and 15 and
    NN.mode = 1 and NN.neighborMode = 1 and
    run = 756 and camCol = 5 and
    obj.type = 6 and (obj.flags & 0x40006) = 0 and
    nchild = 0 and obj.psfMag_i < 20 and
    (g - r between 0.3 and 1.1 and r - i between -0.1 and 0.6)
    ) as obj
    join photoObj as nobj on nobj.objId = obj.neighborObjId
    where
    nobj.run = obj.run and
    (abs(obj.psfMag_g - nobj.psfMag_g) < 0.5 or
    abs(obj.psfMag_r - nobj.psfMag_r) < 0.5 or
    abs(obj.psfMag_i - nobj.psfMag_i) < 0.5)
    order by obj.run, obj.camCol, obj.field

    Object counting and logic      Back to Top

    -- Using object counting and logic in a query.
    -- Find all objects similar to the colors of a quasar at 5.5
    SELECT count(*) as 'total',
    sum( case when (Type=3) then 1 else 0 end) as 'Galaxies',
    sum( case when (Type=6) then 1 else 0 end) as 'Stars',
    sum( case when (Type not in (3,6)) then 1 else 0 end) as 'Other'
    FROM PhotoPrimary -- for each object
    WHERE (( u - g > 2.0) or (u > 22.3) ) -- apply the quasar color cut.
    and ( i between 0 and 19 )
    and ( g - r > 1.0 )
    and ( (r - i < 0.08 + 0.42 * (g - r - 0.96)) or (g - r > 2.26 ) )
    and ( i - z < 0.25 )

    Galaxies blended with stars      Back to Top

    -- Find galaxies that are blended with a star, and output the
    -- deblended galaxy magnitudes.

    -- This query introduces the use of multiple tables or views. You can assign
    -- nicknames to tables as in the FROM clause below. Since you are using
    -- multiple tables, you ,ust specify which table each quantity in the SELECT
    -- clause comes from. Finally, the last line in the query is the join between
    -- the two tables, which is achieved by requiring that a quantity present in
    -- both tables be equal.

    -- NOTE: This query takes a long time to run without the "TOP 1000".

    SELECT G.ObjID, G.u, G.g, G.r, G.i, G.z -- get the ObjID and final mags
    FROM Galaxy G, Star S -- use two Views, Galaxy and Star, as a
    -- convenient mechanism to compare objects
    WHERE G.parentID > 0 -- galaxy has a "parent", which tells us this
    -- object was deblended
    and G.parentID = S.parentID
    		-- and the star has the same parent

    Stars in specific fields      Back to Top

    -- Give me the PSF colors of all stars brighter than g=20 that have PSP_STATUS = 2.
    -- Another simple multi-table query.

    SELECT
    s.psfMag_g,
    		-- or whatever you want from each object
    s.run,
    s.camCol,
    s.rerun,
    s.field
    FROM Star s, Field f
    WHERE s.fieldID = f.fieldID
    and s.psfMag_g < 20
    and f.pspStatus = 2

    Using three tables      Back to Top

    -- Find the parameters for all objects in fields with desired PSF width and range
    -- of columns. Now we are using three tables, but it is still a simple query.

    SELECT
    g.run,
    g.rerun,
    g.camCol,
    f.field,
    p.objID,
    p.ra,
    p.dec,
    p.Rowc,
    p.Colc,
    p.u,
    p.modelMagErr_u ,
    p.g,
    p.modelMagErr_g,
    p.r,
    p.modelMagErr_r,
    p.petroMag_r - p.extinction_r,
    p.petroMagErr_r,
    p.i,
    p.modelMagErr_i,
    p.z,
    p.status & 0x00002000,
    f.psfWidth_r
    FROM
    photoObj p, field f, segment g
    WHERE
    f.fieldid = p.fieldid
    and f.segmentid = g.segmentid
    and g.run = 1336 and g.camCol = 1
    and f.field between 11 and 13
    and f.psfWidth_r > 1.2
    and p.colc > 400.0

    QSOs in spectroscopy      Back to Top

    -- Find quasars with 2.5 < redshift < 2.7. This will use the spectro 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 specObjID, -- get the spectroscopic object id
    z, zConf,
    		-- redshift, redshift confidence
    SpecClass
    		-- and spectral classification
    FROM SpecObj -- from the spectroscopic objects
    WHERE
    -- use a function to translate SpecClass bits to names; want quasars
    (SpecClass=dbo.fSpecClass('QSO')
    or SpecClass=dbo.fSpecClass('HIZ_QSO'))
    -- and the redshift is 2.5 to 2.7. Remember, z is redshift in SpecObj.
    and z between 2.5 and 2.7
    -- and we have a high confidence redshift estimate.
    and zConf > 0.90

    Objects close pairs      Back to Top

    -- Find all objects within 30 arcseconds of one another
    -- that have very similar colors: that is where the color ratios
    -- u-g, g-r, r-I are less than 0.05m.

    SELECT TOP 10 P.ObjID -- distinct cases
    FROM PhotoPrimary P, -- P is the primary object
    Neighbors N, -- N is the neighbor link
    PhotoPrimary L -- L is the lens candidate of P
    WHERE P.ObjID = N.ObjID -- N is a neighbor record
    and L.ObjID = N.NeighborObjID -- L is a neighbor of P
    and P.ObjID < L. ObjID -- avoid duplicates
    and abs((P.u-P.g)-(L.u-L.g))<0.05 -- L and P have similar spectra.
    and abs((P.g-P.r)-(L.g-L.r))<0.05
    and abs((P.r-P.i)-(L.r-L.i))<0.05
    and abs((P.i-P.z)-(L.i-L.z))<0.05

    Errors using flags      Back to Top

    -- Another useful query is to see if the errors on moving (or
    -- apparently moving) objects are correct. For example, it used to be that
    -- some known QSOs were being flagged as moving objects. One way to look for
    -- such objects is to compare the velocity to the error in velocity and see if
    -- the "OBJECT1_MOVED" or "OBJECT2_BAD_MOVING_FIT" is set.
    -- This query introduces bitwise logic for flags, and uses the 'as' syntax to
    -- make the query more readable. Note that if a flag is not set, the value
    -- will be zero. If you want to ensure multiple flags are not set, you can
    -- either check that each individually is zero, or their sum is zero.
    -- (From Gordon Richards)

    -- NOTE: This query takes a long time to run without the "TOP 1000".

    SELECT TOP 1000
    run,
    rerun,
    camcol,
    field,
    objID,
    ra, dec,
    rowv, colv,
    rowvErr, colvErr,
    i,
    (flags & dbo.fPhotoFlags('MOVED')) as MOVED,
    (flags & dbo.fPhotoFlags('BAD_MOVING_FIT')) as BAD_MOVING_FIT
    FROM Galaxy
    WHERE
    (flags & (dbo.fPhotoFlags('MOVED') + dbo.fPhotoFlags('BAD_MOVING_FIT'))) > 0
    and (rowv * rowv + colv * colv) >=
    (rowvErr * rowvErr + colvErr * colvErr)

    Elliptical galaxies based on model fits      Back to Top

    -- Find all galaxies with a deVaucouleours profile and the
    -- photometric colors consistent with an elliptical galaxy. NOTE THAT THE
    -- NAMES AND VALUES OF THE LIKELIHOODS HAVE CHANGED SINCE THE EDR; they are
    -- now log likelihoods, and named accordingly (lDev is now lnlDev, etc.) to
    -- indicate these are log likelihoods. This query has many conditions, and
    -- also has the use of bitwise logic necessary for dealing with flags.

    SELECT ObjID
    FROM Galaxy as G
    WHERE
    G.lnlDev_r > G.lnlExp_r + 0.1
    -- the likelihood of the deVaucouleours profile fit is 10% greater than the
    -- likelihood of the exponential fit
    and G.lnlExp_r > -999
    -- and the likelihoods are actually meaningful
    and (G.flags & (dbo.fPhotoFlags('BINNED1') + dbo.fPhotoFlags('BINNED2') +
    dbo.fPhotoFlags('BINNED4'))) > 0
    -- and it is detected from at least one of the binned images
    and (G.flags & ( dbo.fPhotoFlags('BLENDED') + dbo.fPhotoFlags('NODEBLEND') +
    dbo.fPhotoFlags('CHILD'))) != dbo.fPhotoFlags('BLENDED')
    -- and, if it is blended, it is either a child or not deblended further
    and (G.flags & (dbo.fPhotoFlags('EDGE') + dbo.fPhotoFlags('SATURATED'))) = 0
    -- and it is not near a ccd edge or saturated, where measurements may be bad
    and G.petroMag_i > 17.5
    -- and it is fainter than 17.5 in i-band
    and (G.petroMag_r > 15.5 or G.petroR50_r > 2)
    and (G.petroMag_r > 0 and G.g > 0 and G.r > 0 and G.i > 0)
    and ( (G.petroMag_r - G.extinction_r) < 19.2
    and (G.petroMag_r - G.extinction_r <
    (13.1 + (7/3)*(G.g - G.r) + 4 *(G.r - G.i) - 4 * 0.18) )
    and ( (G.r - G.i - (G.g - G.r)/4 - 0.18) < 0.2 )
    and ( (G.r - G.i - (G.g - G.r)/4 - 0.18) > -0.2 ) )
    or ( (G.petroMag_r - G.extinction_r < 19.5)
    and ( (G.r - G.i - (G.g - G.r)/4 - 0.18) >
    (0.45 - 4*(G.g - G.r) ) )
    and ( (G.g - G.r) > (1.35 + 0.25 *(G.r - G.i) ) ) )
    -- and many constraints on colors and mags to make it have elliptical-type colors.

    Galaxies with blue centers      Back to Top

    -- Galaxies with bluer centers, by Michael Strauss. For all galaxies with r_Petro < 18,
    -- not saturated, not bright, and not edge, give me those with centers appreciably bluer
    -- than their outer parts, i.e., define the center color as: u_psf - g_psf and define
    -- the outer color as: u_model - g_model; give me all objs which have
    --     (u_model - g_model) - (u_psf - g_psf) < -0.4
    --
    -- Another flags-based query.
    -- NOTE: This query takes a long time to run without the "TOP 1000".

    SELECT
    colc_u, colc_g, objID
    FROM Galaxy
    WHERE
    ( Flags & (dbo.fPhotoFlags('SATURATED') +
    dbo.fPhotoFlags('BRIGHT') +
    dbo.fPhotoFlags('EDGE')) ) = 0
    and petroRad_r < 18
    and ((colc_u - colc_g) - (psfMag_u - psfMag_g)) < -0.4

    Diameter limited sample      Back to Top

    -- Diameter-limited sample of galaxies from James Annis.
    -- Another query showing the use of flags, now using the bitwise '|' (or).

    SELECT
    run,
    camCol,
    rerun,
    field,
    objID,
    ra,
    dec
    FROM Galaxy
    WHERE ( flags & (dbo.fPhotoFlags('BINNED1')
    | dbo.fPhotoFlags('BINNED2')
    | dbo.fPhotoFlags('BINNED4')) ) > 0
    and ( flags & (dbo.fPhotoFlags('BLENDED')
    | dbo.fPhotoFlags('NODEBLEND')
    | dbo.fPhotoFlags('CHILD')) ) != dbo.fPhotoFlags('BLENDED')
    and ( ( (flags & dbo.fPhotoFlags('NOPETRO') = 0)
    and petroRad_i > 15)
    or ( (flags & dbo.fPhotoFlags('NOPETRO') > 0)
    and petroRad_i > 7.5)
    or ( (flags & dbo.fPhotoFlags('TOO_LARGE') > 0)
    and petroRad_i > 2.5)
    or ( (flags & dbo.fPhotoFlags('SATURATED') = 0)
    and petroRad_i > 17.5) )

    Extremely red galaxies      Back to Top

    -- Extremely red galaxies (from James Annis).
    -- Similar to the previous query.

    SELECT
    g.run,
    g.camCol,
    g.rerun,
    g.field,
    g.objID,
    g.ra, g.dec
    FROM Field f, Galaxy g
    WHERE
    g.fieldID = f.fieldID
    and ( g.flags & (dbo.fPhotoFlags('BINNED1')
    | dbo.fPhotoFlags('BINNED2')
    | dbo.fPhotoFlags('BINNED4')) ) > 0
    and ( g.flags & (dbo.fPhotoFlags('BLENDED')
    | dbo.fPhotoFlags('NODEBLEND')
    | dbo.fPhotoFlags('CHILD')) ) != dbo.fPhotoFlags('BLENDED')
    and ( g.flags & (dbo.fPhotoFlags('COSMIC_RAY')
    | dbo.fPhotoFlags('INTERP')) ) = 0
    and f.psfWidth_r < 1.5
    and (g.i - g.z > 1.0 )

    LRG sample      Back to Top

    -- A version of the LRG sample, by James Annis.
    -- Another query with many conditions and flag tests.

    SELECT
    run,
    camCol,
    rerun,
    field,
    objID,
    ra,
    dec
    FROM Galaxy
    WHERE ( ( flags & (dbo.fPhotoFlags('BINNED1')
    | dbo.fPhotoFlags('BINNED2')
    | dbo.fPhotoFlags('BINNED4')) ) > 0
    and ( flags & (dbo.fPhotoFlags('BLENDED')
    | dbo.fPhotoFlags('NODEBLEND')
    | dbo.fPhotoFlags('CHILD')) ) != dbo.fPhotoFlags('BLENDED')
    and ( flags & (dbo.fPhotoFlags('EDGE')
    | dbo.fPhotoFlags('SATURATED')) ) = 0
    and petroMag_i > 17.5
    and (petroMag_r > 15.5 or petroR50_r > 2)
    and (petroMag_r > 0 and g > 0 and r > 0 and i > 0)
    and ( (petroMag_r-extinction_r) < 19.2
    and (petroMag_r - extinction_r <
    (13.1 + (7/3) * (dered_g - dered_r) + 4 * (dered_r - dered_i)
    - 4 * 0.18) )
    and ( (dered_r - dered_i - (dered_g - dered_r)/4 - 0.18) < 0.2)
    and ( (dered_r - dered_i - (dered_g - dered_r)/4 - 0.18) > -0.2)
    -- dered_ quantities already include reddening
    and ( (petroMag_r - extinction_r +
    2.5 * LOG10(2 * 3.1415 * petroR50_r * petroR50_r)) < 24.2) )
    or ( (petroMag_r - extinction_r < 19.5)
    and ( (dered_r - dered_i - (dered_g - dered_r)/4 - 0.18) > (0.45 - 4 *
    (dered_g - dered_r)) )
    and ( (dered_g - dered_r) > (1.35 + 0.25 * (dered_r - dered_i)) ) )
    and ( (petroMag_r - extinction_r +
    2.5 * LOG10(2 * 3.1415 * petroR50_r * petroR50_r) ) < 23.3 ) )

    Brightness of closest source      Back to Top

    -- The query below was originally written by Andy Connolly to find the brightness of
    -- the closest source within 0.5arcmin. It involves a 3-way join of the PhotoObjAll table
    -- with itself and the Neighbors table. This is a huge join because the PhotoObjAll table
    -- is the largest table in the DB, and the Neighbors table has over a billion entries
    -- (although it is a thin table). The two versions of the query shown below illustrate
    -- how we can speed up the query a lot by using the (much thinner) PhotoTag table
    -- instead of the PhotoObjAll table. See also the Optimizing Queries section of the
    -- SQL Intro page for more on using the PhotoTag table. The query also illustrates the
    -- LEFT JOIN construct and the use of nested joins.

    -- The first (original) version of the query uses the PhotoObjAll table twice in the 3-way
    -- join because we need some of the columns that are only in the PhotoObjall table.
    -- Since this version literally takes days to run on the entire DR2 database, a TOP 100
    -- has been inserted into the SELECT to prevent the query from being submitted as is.

    SELECT TOP 100 o.ra,o.dec,o.flags, o.type,o.objid,
    o.psfMag_g,o.psfMag_r,o.psfMag_i,o.modelMag_g,o.modelMag_r,o.modelMag_i,
    o.petroRad_r,
    o.q_g,o.q_r,o.q_i,
    o.u_g,o.u_r,o.u_i,
    o.mE1_r,o.mE2_r,o.mRrCc_r,o.mCr4_r,
    o.isoA_r,o.isoB_r,o.isoAGrad_r,o.isoBGrad_r,o.isoPhi_r,
    n.distance,p.r,p.g
    FROM PhotoObjAll as o
    left join Neighbors as n on o.objid=n.objid,
    PhotoObjAll p
    WHERE
    (o.ra > 120) and (o.ra < 240)
    and (o.r > 16.) and (o.r<21.0)
    and n.neighborObjId=(select top 1 nn.neighborObjId
    from Neighbors nn, photoObjAll pp
    where nn.objId=o.objId and nn.neighborObjId = pp.objID
    order by pp.r)
    and p.objId=n.neighborObjId

    -- The second version of this query demonstrates the advantage of using the PhotoTag
    -- table over the PhotoObjAll table. One of the PhotoObjAll joins in the main 3-way
    -- join is replaced with PhotoTag, and the nested PhotoObjAll join with Neighbors is
    -- also replaced with PhotoTag. This version runs in about 2-3 hours on the DR2 DB.
    -- Note that when you replace PhotoObjAll or its views by PhotoTag, you have to also
    -- replace any references to the shorthand (simplified) magnitudes (u,g,r,i,z) and errors
    -- by their full names (modelMag_u and modelMagErr_u etc.).

    SELECT o.ra,o.dec,o.flags, o.type,o.objid,
    o.psfMag_g,o.psfMag_r,o.psfMag_i,o.modelMag_g,o.modelMag_r,o.modelMag_i,
    o.petroRad_r,
    o.q_g,o.q_r,o.q_i,
    o.u_g,o.u_r,o.u_i,
    o.mE1_r,o.mE2_r,o.mRrCc_r,o.mCr4_r,
    o.isoA_r,o.isoB_r,o.isoAGrad_r,o.isoBGrad_r,o.isoPhi_r,
    n.distance,p.r,p.g
    FROM PhotoObjAll as o
    left join Neighbors as n on o.objid=n.objid,
    PhotoTag p     -- replace second PhotoObjAll by PhotoTag
    WHERE
    (o.ra > 120) and (o.ra < 240)
    and (o.r > 16.) and (o.r<21.0)
    and n.neighborObjId=(select top 1 nn.neighborObjId
    from Neighbors nn,
    PhotoTag pp     -- replace PhotoObjAll with PhotoTag here too
    where nn.objId=o.objId and nn.neighborObjId = pp.objID
    order by pp.modelMag_r)   -- PhotoTag doesnt have shorthand u,g,r,i,z mags
    and p.objId=n.neighborObjId

    Galaxies by spectra      Back to Top

    -- Two versions of a query to find galaxies with particular spectral lines.
    -- Version 1: Find galaxies with spectra that have an equivalent width in
    -- H_alpha > 40 Angstroms. We want object ID's from the photometry (Galaxy)
    -- but constraints from spectroscopy. The line widths and IDs are stored in
    -- SpecLine. This is a simple query, but now we are using three tables. The
    -- spectroscopy tables of measured lines are arranged non-intuitively, and we
    -- urge users to read about them on the DAS help pages. -- IMPORTANT NOTE:
    -- Each spectroscopic object now has a match to at least two photometric
    -- objects, one in Target and one in Best. Therefore, when performing a join
    -- between spectroscopic photometric objects, you must specify either
    -- PhotoObj.ObjID=SpecObj.bestObjID OR PhotoObj.ObjID = SpecObj.targetObjID.
    -- Normally, the default photometric database is BEST, so you will want to use
    -- SpecObj.bestObjID

    SELECT G.ObjID -- we want the photometric ObjID
    FROM Galaxy as G,
    SpecObj as S,
    SpecLine as L
    WHERE G.ObjID = S.bestObjID -- the spectroscopic object should be
    -- (photometrically) a galaxy
    -- you could add a constraint that the spectral type is galaxy
    and S.SpecObjID = L.SpecObjID
    		-- and spectral line L is detected in spectrum
    and L.LineId = 6565
    		-- and line L is the H alpha line
    and L.ew > 40
    		-- and H alpha is at least 40 angstroms wide

    -- Second version of this query finds galaxies with more specific spectra.
    -- This version also requires weak Hbeta line (Halpha/Hbeta > 20.)

    SELECT G.ObjID -- return qualifying galaxies
    FROM Galaxy as G, -- G is the galaxy
    SpecObj as S,
    		-- S is the spectra of galaxy G
    SpecLine as L1,
    		-- L1 is a line of S
    SpecLine as L2,
    		-- L2 is a second line of S
    SpecLineNames as LN1,
    		-- the names of the lines (Halpha)
    SpecLineNames as LN2
    		-- the names of the lines (Hbeta)
    WHERE G.ObjID = S.BestObjID -- connect the galaxy to the spectrum
    and S.SpecObjID = L1.SpecObjID
    		-- L1 is a line of S.
    and S.SpecObjID = L2.SpecObjID
    		-- L2 is a line of S.
    and L1.LineId = LN1. value
    and LN1.name = 'Ha_6565'
    		-- L1 is the H alpha line
    and L2.LineId = LN2.value
    		-- L2 is the H alpha line
    and LN2.name = 'Hb_4863'
    		--
    and L1.ew > 200
    		-- BIG Halpha
    and L2.ew > 10
    		-- significant Hbeta emission line
    and L2.ew * 20 < L1.ew
    		-- Hbeta is comparatively small

    Clean photometry with flags      Back to Top

    -- This query demonstrates the use of the photometry flags to select clean
    -- photometry for star and galaxy objects. Note that using these flag combinations
    -- may invoke the bookmark lookup bug if your query is searching a large fraction
    -- of the database. In that case, use the prescribed workaround for it as described on
    -- the SQL intro page.

    -- For queries on star objects, when you use PSF mags, use only PRIMARY objects
    -- and the flag combinations indicated below. If you use the Star view as below, you
    -- will get only primary objects, otherwise you will need to add a "mode=1" constraint.
    -- NOTE: The symbolic flag values are purposely replaced in the following examples by
    -- the hex values for the flag masks. This is for efficiency (see the Using dbo
    -- functions     section of the SQL Intro page).

    -- For example, if you are interested in r-band magnitudes of objects, perform the
    -- following checks (add analogous checks with AND for other bands if you are
    -- interested in multiple magnitudes or colors):

    SELECT TOP 10 u,g,r,i,z,ra,dec, flags_r
    FROM Star
    WHERE
    ra BETWEEN 180 and 181 AND dec BETWEEN -0.5 and 0.5
    AND ((flags_r & 0x10000000) != 0)
    -- detected in BINNED1
    AND ((flags_r & 0x8100000c00a4) = 0)
    -- not EDGE, NOPROFILE, PEAKCENTER, NOTCHECKED, PSF_FLUX_INTERP,
    -- SATURATED, or BAD_COUNTS_ERROR
    AND (((flags_r & 0x400000000000) = 0) or (psfmagerr_g <= 0.2))
    -- not DEBLEND_NOPEAK or small PSF error
    -- (substitute psfmagerr in other band as appropriate)
    AND (((flags_r & 0x100000000000) = 0) or (flags_r & 0x1000) = 0)
    -- not INTERP_CENTER or not COSMIC_RAY

    -- For galaxies (i.e. not using PSF mags): Again use only PRIMARY objects. Other
    -- cuts are nearly the same, but remove the cut on EDGE. Possibly also remove
    -- the cut on INTERP flags.

    SELECT TOP 10 u,g,r,i,z,ra,dec, flags_r
    FROM Galaxy
    WHERE
    ra BETWEEN 180 and 181 AND dec BETWEEN -0.5 and 0.5
    AND ((flags_r & 0x10000000) != 0)
    -- detected in BINNED1
    AND ((flags_r & 0x8100000c00a0) = 0)
    -- not NOPROFILE, PEAKCENTER, NOTCHECKED, PSF_FLUX_INTERP, SATURATED,
    -- or BAD_COUNTS_ERROR.
    -- if you want to accept objects with interpolation problems for PSF mags,
    -- change this to: AND ((flags_r & 0x800a0) = 0)
    AND (((flags_r & 0x400000000000) = 0) or (psfmagerr_g <= 0.2))
    -- not DEBLEND_NOPEAK or small PSF error
    -- (substitute psfmagerr in other band as appropriate)
    AND (((flags_r & 0x100000000000) = 0) or (flags_r & 0x1000) = 0)
    -- not INTERP_CENTER or not COSMIC_RAY - omit this AND clause if you want to
    -- accept objects with interpolation problems for PSF mags.

    Binary stars colors      Back to Top

    -- Find binary stars with specific colors.
    -- At least one of them should have the colors of a white dwarf.

    SELECT TOP 100 s1.objID as s1, s2.objID as s2
    FROM Star S1, -- S1 is the white dwarf
    Neighbors N, -- N is the precomputed neighbors links
    Star S2 -- S2 is the second star
    WHERE S1.objID = N. objID -- S1 and S2 are neighbors-within 30 arc sec
    and S2.objID = N.NeighborObjID
    and N.NeighborObjType = dbo.fPhotoType('Star') -- and S2 is a star
    and N.distance < .05 -- the 3 arcsecond test
    and ((S1.u - S1.g) < 0.4 ) -- and S1 meets Paul Szkodys color cut for
    and (S1.g - S1.r) < 0.7 -- white dwarfs.
    and (S1.r - S1.i) > 0.4
    and (S1.i - S1.z) > 0.4

    QSO broadlines near galaxy      Back to Top

    -- Find quasars with a broad absorption line and a nearby galaxy within 10arcsec.
    -- Return both the quasars and the galaxies.

    SELECT Q.BestObjID as Quasar_candidate_ID , G.ObjID as Galaxy_ID, N.distance
    FROM SpecObj as Q, -- Q is the specObj of the quasar candidate
    Neighbors as N, -- N is the Neighbors list of Q
    Galaxy as G, -- G is the nearby galaxy
    SpecClass as SC, -- Spec Class
    SpecLine as L, -- L is the broad line we are looking for
    SpecLineNames as LN -- Line Name
    WHERE Q.SpecClass =SC.value -- Q is a QSO
    and SC.name in ('QSO', 'HIZ_QSO') -- Spectrum says "QSO"
    and Q.SpecObjID = L.SpecObjID -- L is a spectral line of Q.
    and L.LineID = LN.value -- line found and
    and LN.Name != 'UNKNOWN' -- not not identified
    and L.ew < -10 -- but its a prominent absorption line
    and Q.BestObjID = N.ObjID -- N is a neighbor record
    and G.ObjID = N.NeighborObjID -- G is a neighbor of Q
    and N.distance < 10.0/60 -- and it is within 10 arcseconds of the Q.

    Galaxies unsaturated near given location      Back to Top

    -- Find galaxies without saturated pixels within 1' of a given point (ra=185.0, dec=-0.5).
    -- This query uses a function fGetNearbyObjEq,which takes 3 arguments (ra,dec,
    -- distance in arcmin); this function uses the Neighbors table. The Neighbors and Galaxy
    -- tables have in common the objID, so we have to select objects from both where the
    -- objIDs are the same. The output of the function is a table with the Galaxy Object ID
    -- and distance in arcmin from the input. This query introduces the use of a JOIN to
    -- combine table contents. We also use the 'ORDER BY' syntax, which sorts the output.

    SELECT TOP 100 G.objID, GN.distance
    FROM Galaxy as G
    JOIN dbo.fGetNearbyObjEq(185.,-0.5, 1) as GN -- this function outputs a table, so we have to do a join
    on G.objID = GN.objID -- get objects from neighbors table GN with desired ObjID
    WHERE (G.flags & dbo.fPhotoFlags('saturated')) = 0 -- and the object is not saturated. f.PhotoFlags is a function that interprets the flag bits.
    ORDER BY distance -- sort these by distance

    Ellipticals odd lines      Back to Top

    -- Find all elliptical galaxies with spectra that have an anomalous emission line.
    -- This query introduces the SQL syntax DISTINCT, which will return only one instance
    -- of the requested parameter (ObjID, in this case), because our query may return the
    -- same object more than once. This is also the first nested query, where we use one
    -- SELECT (the inner one) to get a group of objects we are not interested in. The outer
    -- SELECT includes the new syntax 'not in', which is used to perform the exclusion.

    SELECT DISTINCT G.ObjID
    FROM
    Galaxy as G,
    SpecObj as S,
    SpecLine as L,
    XCRedshift as XC
    WHERE G.ObjID = S.bestObjID
    and S.SpecObjID = L.SpecObjID
    		-- L is a line of S and there is
    and S.SpecObjID = XC.SpecObjID
    		-- a crosscorrelation redshift
    and XC.tempNo = 8
    		-- template used is "elliptical"
    and L.lineID = 0
    		-- any line type is found
    and L.ew > 10
    		-- and the line is prominent by some
    -- definition; in this case, equivalent
    -- width is over 10 Angstroms
    and S.SpecObjID not in (
    		-- insist that there are no other lines
    SELECT S.SpecObjID
    		-- This is the chosen line.
    FROM SpecLine as L1
    		-- L1 is another line
    WHERE S.SpecObjID = L1.SpecObjID
    		-- for this object
    and abs(L.wave - L1.wave) <.01
    		-- at nearly the same wavelength
    and L1.LineID != 0
    		-- but with unknown line type
    )

    Broadest spectral lines      Back to Top

    -- What's the SQL to get the broadest line of each spectrum, together with its
    -- identification (or more generally, all the columns for the spectral line with the
    -- highest/lowest something)? The line width is stored in the column sigma, the
    -- identification is in the column restwave. (Sebastian Jester)

    SELECT
    L.specObjId,
    L.SpecLineID,
    sMax.Maxwidth
    -- get the spectroscopic object ID, the line ID, and the width
    FROM SpecLine L,
    (SELECT specObjID,
    Max(waveMax-waveMin) as MaxWidth
    -- Define MaxWidth as the largest difference in the min and max wavelength of
    -- an identified line, using the built-in Max function of SQL
    FROM SpecLine
    GROUP BY specObjID) as sMax
    -- sMax contains a single specObjID and MaxWidth. Remember that for each line
    -- identified in a given object's spectrum, there is an entry in
    -- SpecLine. This means that each specObjID appears many times in SpecLine,
    -- once for each line found, and they must be aggregated before performing any
    -- operation such as Max. The GROUP BY is thus NECESSARY.
    -- The upper FROM clause is telling us we will use the table SpecLine and this
    -- new output called sMax, which contains one entry with the SpecObjID and
    -- MaxWidth. If we used some other function instead of 'Max', such as 'top
    -- 10', sMax would have more than row.
    WHERE
    L.specObjID = sMax.specObjID
    and (L.waveMax-L.waveMin) = sMax.MaxWidth
    -- Just as with the specObjID, each specLineID appears many times in specLine.
    -- This final WHERE clause makes sure we get the one specLineID from SpecObj
    -- which matches the unique combination of specObjID and MaxWidth in sMax.

    Gridded galaxy counts      Back to Top

    -- Gridded galaxy counts and masks. Actually consists of TWO queries:
    -- 1) Create a gridded count of galaxies with u-g > 1 and r < 21.5 over 60 < dec < 70,
    -- and 200 < R.A. < 210, on a grid, and create a map of masks over the same grid.
    -- Scan the table for galaxies and group them in cells 2 arc-minuteson a side. Provide
    -- predicates for the color restrictions on u-g and r and to limit the search to the
    -- portion of the sky defined by the right ascension and declination conditions. Return
    -- the count of qualifying galaxies in each cell.
    -- 2) Run another query with the same grouping, but with a predicate to include only
    -- objects such as satellites, planets, and airplanes that obscure the cell. The second
    -- query returns a list of cell coordinates that serve as a mask for the first query.

    --- First find the gridded galaxy count (with the color cut)
    --- In local tangent plane, ra/cos(dec) is a linear degree.

    SELECT cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0 as raCosDec,
    cast(dec*30 as int)/30.0 as dec,
    count(*) as pop
    FROM Galaxy as G,
    fHTM_Cover('CONVEX J2000 6 175 -5 175 5 185 5 185 -5') as T
    WHERE htmID between T.HTMIDstart* power(2,28)and T. HTMIDend*power(2,28)
    and ra between 175 and 185
    and dec between -5 and 5
    and u-g > 1
    and r < 21.5
    GROUP BY cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0,
    cast(dec*30 as int)/30.0
    -- now build mask grid.
    -- This is a separate query if no temp tables can be made

    SELECT cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0 as raCosDec,
    cast(dec*30 as int)/30.0 as dec,
    count(*) as pop
    FROM photoObj as PO,
    dbo.fHTM_Cover('CONVEX J2000 6 175 -5 175 5 185 5 185 -5') as T,
    PhotoType as PT
    WHERE htmID between T. HTMIDstart*power(2,28) and T. HTMIDend*power(2,28)
    and ra between 175 and 185
    and dec between -5 and 5
    and PO.type = PT.value
    and PT.name in ('COSMIC_RAY', 'DEFECT', 'GHOST', 'TRAIL', 'UNKNOWN')
    group by cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0,
    cast(dec*30 as int)/30.0

    Galaxy counts on HTM grid      Back to Top

    -- Create a count of galaxies for each of the HTM triangles. Galaxies should satisfy a
    -- certain color cut, like 0.7u-0.5g-0.2i<1.25 && r<21.75, output it in a form adequate
    -- for visualization.

    SELECT (htmID / power(2,24)) as htm_8 ,
    -- group by 8-deep HTMID (rshift HTM by 12)
    avg(ra) as ra,
    avg(dec) as [dec],
    count(*) as pop -- return center point and count for display
    FROM Galaxy -- only look at galaxies
    WHERE (0.7*u - 0.5*g - 0.2*i) < 1.25 -- meeting this color cut
    and r < 21.75 -- brighter than 21.75 magnitude in red band.
    group by (htmID /power(2,24)) -- group into 8-deep HTM buckets.

    Stars multiply measured      Back to Top

    -- Find stars with multiple measurements with magnitude variations > 0.1. Note that
    -- this runs very slowly without the "TOP 100", so please see the Optimizing queries
    -- section of the SQL help page to learn how to speed up this query.

    SELECT TOP 100
    S1.objID as objID1, S2.objID as ObjID2 -- select object IDs of star and its pair
    FROM Star as S1, -- the primary star
    photoObj as S2, -- the second observation of the star
    neighbors as N -- the neighbor record
    WHERE S1.objID = N.objID -- insist the stars are neighbors
    and S2.objID = N.neighborObjID -- using precomputed neighbors table
    and distance < 0.5/60 -- distance is 0.5 arc second or less
    and S1.run != S2.run -- observations are two different runs
    and S2.type = dbo.fPhotoType('Star') -- S2 is indeed a star
    and S1.u between 1 and 27 -- S1 magnitudes are reasonable
    and S1.g between 1 and 27
    and S1.r between 1 and 27
    and S1.i between 1 and 27
    and S1.z between 1 and 27
    and S2.u between 1 and 27 -- S2 magnitudes are reasonable.
    and S2.g between 1 and 27
    and S2.r between 1 and 27
    and S2.i between 1 and 27
    and S2.z between 1 and 27
    and ( -- and one of the colors is different.
    abs(S1.u-S2.u) > .1 + (abs(S1.Err_u) + abs(S2.Err_u))
    or abs(S1.g-S2.g) > .1 + (abs(S1.Err_g) + abs(S2.Err_g))
    or abs(S1.r-S2.r) > .1 + (abs(S1.Err_r) + abs(S2.Err_r))
    or abs(S1.i-S2.i) > .1 + (abs(S1.Err_i) + abs(S2.Err_i))
    or abs(S1.z-S2.z) > .1 + (abs(S1.Err_z) + abs(S2.Err_z))
    )

    White Dwarf candidates      Back to Top

    -- Select white dwarf candidates, returning the necessary photometric parameters,
    -- proper motion, spectroscopic information, and the distance to the nearest neighbor
    -- brighter than g=21. (From Jeff Munn)

    SELECT
    o.*, ISNULL(nbor.nearest,999) as nearest
    FROM
    (
    -- This selects the white dwarf candidates, meeting the following criteria
    -- 1) Stars with dereddened g magnitudes between 15 and 20
    -- 2) Proper motion > 2 arcsec/century
    -- 3) Meet either a reduced proper motion cut, or have dereddened g-i < 0
    -- A left outer join is also performed to fetch the spectroscopic information
    -- for those stars with spectra.
    SELECT p.objID,
    p.psfMag_g - p.extinction_g + 5 * log(u.propermotion / 100.) + 5 AS rpm,
    p.psfMag_g - p.extinction_g - (p.psfMag_i - p.extinction_i) AS gi,
    p.psfMag_u, p.psfMag_g, p.psfMag_r, p.psfMag_i, p.psfMag_z,
    p.extinction_u, p.extinction_g, p.extinction_r, p.extinction_i,
    p.extinction_z,p.ra,p.dec,p.run,p.rerun,p.camcol,p.field,p.obj,
    p.status,p.flags,
    u.propermotion,
    ISNULL(s.specClass,0) as specClass, ISNULL(s.z,0) as z,
    ISNULL(s.zConf,0) as zConf, ISNULL(s.zWarning,0) as zWarning,
    ISNULL(s.plate,0) as plate, ISNULL(s.mjd,0) as mjd,
    ISNULL(s.fiberID,0) as fiberID
    FROM
    PhotoTag p JOIN USNO u ON p.objID = u.objID
    LEFT OUTER JOIN SpecObj s ON p.objID = s.bestObjID
    WHERE
    p.type = dbo.fPhotoType('Star')
    AND (p.flags & dbo.fPhotoFlags('EDGE')) = 0
    AND (p.psfMag_g - p.extinction_g) BETWEEN 15 AND 20
    AND u.propermotion > 2.
    AND (p.psfMag_g - p.extinction_g + 5 * log(u.propermotion / 100.) + 5 >
    16.136 + 2.727 * (p.psfMag_g - p.extinction_g -
    (p.psfMag_i - p.extinction_i)) OR
    p.psfMag_g - p.extinction_g - (p.psfMag_i - p.extinction_i) < 0.)
    ) AS o LEFT OUTER JOIN
    (
    -- This fetches the distance to the nearest PRIMARY neighbor (limited to stars
    -- or galaxies) whose g magntiude is brighter than 21. To speed the query a bit,
    -- we limit the objects to bright PRIMARY stars brighter than 21, since that
    -- includes all the objects that we'll be joining to.
    SELECT n.objID, MIN(n.distance) AS nearest
    FROM Neighbors n JOIN PhotoTag x ON n.neighborObjID = x.objID
    WHERE n.type = dbo.fPhotoType('Star') AND
    n.mode = dbo.fPhotoMode('Primary') AND
    n.neighborMode = dbo.fPhotoMode('Primary') AND
    (x.type = dbo.fPhotoType('Star') OR x.type = dbo.fPhotoType('Galaxy'))
    AND x.modelMag_g BETWEEN 10 AND 21
    GROUP BY n.objID ) AS nbor ON o.objID = nbor.objID

    More quasar queries      Back to Top

    -- Here is a query to get object IDs and field mjds for quasars with secondary matches.
    -- (From Jordan Raddick)

    SELECT p.objid as primary_objid, f.mjd_g as primary_mjd, m.matchobjid as
    secondary_obji, g.mjd_g as secondary_mjd, p.ra, p.dec, p.modelmag_g as
    primary_g, q.modelmag_g as secondary_g, s.z as redshift FROM phototag p, phototag q,match m, specobj s, field f, field g WHERE p.objid=m.objid AND
    q.objid=m.matchobjid AND
    p.objid=s.bestobjid AND
    p.fieldID=f.fieldID AND
    q.fieldID=g.fieldID AND
    (s.specclass=3 or s.specclass=4) ORDER BY p.modelmag_g

    -- Some more useful quasar queries (from Sebastian Jester).
    -- Getting magnitudes for spectroscopic quasars - retrieves BEST photometry.
    -- This query introduces the SpecPhoto view of the SpecPhotoAll table, which is a pre-computed join
    -- of the important fields in the SpecObjAll and PhotoObjAll tables. It is very convenient and much
    -- faster to use this when you can instead of doing the join yourself.

    SELECT ra,dec,psfmag_i-extinction_i AS mag_i,psfmag_r-extinction_r AS mag_r,z
    FROM SpecPhoto
    WHERE zconf > 0.35
    AND (specclass = dbo.fspecclass('QSO') OR specclass = dbo.fspecclass('HIZ_QSO'))
    AND ra between 180 AND 210 AND dec between -10 AND 10
    -- Getting TARGET photometry for spectra

    SELECT sp.ra,sp.dec,sp.z,
    sp.psfmag_i-sp.extinction_i AS best_i,
    p.psfmag_i-p.extinction_i AS target_i
    FROM specphoto AS sp
    INNER JOIN TARGDR2..photoprimary AS p
    ON sp.targetobjid = p.objid
    WHERE sp.zconf > 0.35
    AND (specclass = dbo.fspecclass('QSO') OR specclass = dbo.fspecclass('HIZ_QSO'))
    -- Getting FIRST data for spectroscopic quasars - returns only those quasars that match

    SELECT sp.ra,sp.dec,sp.z,
    sp.psfmag_i-sp.extinction_i AS mag_i,
    match,peak,integr,rms,delta
    FROM SpecPhoto AS sp
    INNER JOIN first AS f ON sp.objid = f.objid
    WHERE sp.zconf > 0.35
    AND (specclass = dbo.fspecclass('QSO') OR specclass = dbo.fspecclass('HIZ_QSO'))
    -- Surface density of quasar targets and FIRST matches to them on a field-by-field basis
    -- restricted to some part of the sky.

    SELECT f.run,f.rerun,f.camcol,f.field,ra_avg,dec_avg,
    center_ra,center_dec,n_targets,n_match,area
    FROM (
    SELECT run,rerun,camcol,field,stripearea AS area,fieldid,
    (ramax+ramin)/2 AS center_ra, (decmax+decmin)/2 AS center_dec
    FROM field
    WHERE (ramax+ramin)/2 between 160 AND 180
    AND (decmax+decmin)/2 between -10 AND 10
    AND quality = dbo.ffieldquality('good')
    ) AS f
    INNER JOIN (
    SELECT count(*) AS n_targets, p.fieldid,
    AVG(p.ra) AS ra_avg, avg(p.dec) AS dec_avg,
    ISNULL(sum(fi.match),0) AS n_match
    FROM photoprimary AS p
    LEFT OUTER JOIN first AS fi
    ON p.objid = fi.objid
    WHERE ((p.primtarget & dbo.fprimtarget('TARGET_QSO_CAP'))
    = dbo.fprimtarget('TARGET_QSO_CAP'))
    GROUP BY fieldid
    ) AS p
    ON f.fieldid = p.fieldid
    ORDER BY ra_avg,dec_avg

    Using LEFT OUTER JOIN      Back to Top

    -- This query from Sebastian Jester demonstrates the use of the LEFT OUTER JOIN
    -- construct in order to include even rows that do not meet the JOIN condition. The
    -- query also gets the sky brighness and turns it into a flux, which illustrates the use of
    -- the POWER() function and CAST to change the string representation into floating
    -- point. The First table contains matches between SDSS and FIRST survey objects.

    select fld.run, fld.avg_sky_muJy, fld.runarea as area, isnull(fp.nfirstmatch,0)
    from (
    --first part: for each run, get total area and average sky brightness
    select run, sum(stripearea) as runarea,
    3631e6*avg(power(cast(10. as float),-0.4*sky_r)) as avg_sky_muJy
    from field
    group by run
    ) as fld
    left outer join (
    -- second part: for each run,get total number of FIRST matches. To get the run number
    -- for each FIRST match, need to join FIRST with PHOTOPRIMARY. Some runs may have
    -- 0 FIRST matches, so these runs will not appear in the result set of this subquery.
    -- But we want to keep all runs from the first query in the final result, hence
    -- we need a LEFT OUTER JOIN between the first and the second query.
    -- The LEFT OUTER JOIN returns all the rows from the first subquery and matches
    -- with the corresponding rows from the second query. Where the second query
    -- has no corresponding row, a NULL is returned. The ISNULL() function in the
    -- SELECT above converts this NULL into a 0 to say "0 FIRST matches in this run".
    select p.run, sum(fm.match) as nfirstmatch
    from First as fm
    inner join photoprimary as p
    on p.objid=fm.objid
    group by p.run
    ) as fp
    on fld.run=fp.run
    order by fld.run

    Using multiple OUTER JOINs      Back to Top

    -- This query from Gordon Richards demonstrates the use of multiple OUTER JOINs
    -- It does take a few hours to run, hence the TOP 10 is added if you want to try it.

    SELECT TOP 10
    dbo.fSDSS(p.objId) as oid,
    p.objId,
    p.ra, p.dec,
    dbo.fHMS(p.ra) as raHMS,
    dbo.fDMS(p.dec) as decDMS,
    p.psfmag_u, p.psfmag_g, p.psfmag_r, p.psfmag_i, p.psfmag_z,
    p.psfmagerr_u, p.psfmagerr_g, p.psfmagerr_r, p.psfmagerr_i, p.psfmagerr_z,
    p.extinction_u, p.extinction_g, p.extinction_r, p.extinction_i,
    p.extinction_z,
    p.rowc,p.colc,
    p.type,
    p.mode,
    p.flags,
    p.flags_u,p.flags_g,p.flags_r,p.flags_i,p.flags_z,
    p.insideMask,
    p.primTarget,
    (p.primTarget & 0x00000001) as pthiz,
    (p.primTarget & 0x00000006) as ptlowz,
    (p.primTarget & 0x00000018) as ptfirst,
    s.z,
    s.zErr,
    s.zConf,
    s.zStatus,
    s.zWarning,
    s.specClass,
    s.plate,
    s.fiberID,
    s.mjd,
    s.sciencePrimary,
    str(fld.mjd_i,5,5) as mjdi,
    fld.quality,
    fld.culled,
    f.peak,
    r.cps
    FROM BESTDR3..photoObjAll as p with (index(0))
    left outer join SpecObj as s on p.objID = s.bestObjID
    left outer join First as f on p.objID = f.objID
    left outer join Rosat as r on p.objID = r.objID
    left outer join Field as fld on p.fieldID = fld.fieldID
    WHERE
    (
    (p.mode = 1) AND ((p.status & 0x10) > 0) AND
    (
    ((p.primTarget & 0x0000001f) > 0)
    OR
    ((s.specClass in (3,4)) AND (s.sciencePrimary = 1))
    ) )

    Searching for multiple spec lines     Back to Top

    -- A query from Tomo Goto that looks for several spec lines at once.

    SELECT
    S.ObjID, S.ra, S.dec, S.z,
    'Ha_6565', L.ew, L.ewErr, L.continuum,
    'Hb_4863', L2.ew, L2.ewErr, L2.continuum,
    'OII_3727', L_OII.ew ,L_OII.ewErr,L_OII.continuum,
    'Hd_4103', L_Hd.ew ,L_Hd.ewErr,L_Hd.continuum
    FROM SpecPhoto as S, -- S is the spectra of galaxy G
    SpecLine as L, -- L is a line of S
    -- SpecLineNames as LN, -- the names of the lines
    SpecLine as L2,
    -- SpecLineNames as LN2,
    SpecLine as L_OII,
    -- SpecLineNames as LN_OII,
    SpecLine as L_Hd
    -- SpecLineNames as LN_Hd

    WHERE S.SpecObjID = L.SpecObjID -- and the spectral line L is detected in the spectrum
    and S.SpecObjID = L2.SpecObjID --
    and L.LineId = 6565 -- L is the H alpha line
    -- and LN.name = 'Ha_6565'
    and L2.LineId = 4863
    -- and LN2.name = 'Hb_4863'
    --------------------------------------------------------------------------------
    and S.SpecObjID = L_OII.SpecObjID
    and L_OII.LineId = 3727
    -- and LN_OII.name = 'OII_3727'
    --------------------------------------------------------------------------------
    and S.SpecObjID = L_Hd.SpecObjID
    and L_Hd.LineId = 4103
    -- and LN_Hd.name = 'Hd_4103'
    --------------------------------------------------------------------------------

    Counting galaxies in North     Back to Top

    -- A query from Jon Loveday to count galaxies in the North.
    -- Galaxy number counts for northern Galactic hemisphere, ie. stripe < 50.
    --
    -- 262158 is the sum of the SATURATED, BLENDED, BRIGHT and EDGE flags,
    -- obtained with the query:
    --
    -- SELECT top 1 (dbo.fPhotoFlags('SATURATED')
    -- + dbo.fPhotoFlags('BLENDED')
    -- + dbo.fPhotoFlags('BRIGHT')
    -- + dbo.fPhotoFlags('EDGE')) from PhotoTag
    SELECT cast(2*(g.petroMag_r - g.extinction_r + 0.25) as int)/2.0 as r, 2*count(*) as N
    FROM galaxy g, segment seg, field f
    WHERE
    seg.segmentID = f.segmentID and f.fieldID = g.fieldID and
    seg.stripe < 50 and
    g.petroMag_r - g.extinction_r < 22 and
    (g.flags_r & 262158) = 0 and
    ((case when (g.type_g=3) then 1 else 0 end) +
    (case when (g.type_r=3) then 1 else 0 end) +
    (case when (g.type_i=3) then 1 else 0 end)) > 1
    GROUP BY cast(2*(g.petroMag_r - g.extinction_r + 0.25) as int)/2.0
    ORDER BY cast(2*(g.petroMag_r - g.extinction_r + 0.25) as int)/2.0

    Counts by type and program      Back to Top


    -- List the number of each type of object observed by each
    -- special program.
    SELECT plate.programname, dbo.fSpecClassN(specClass) AS Class,
    COUNT(specObjId) AS numObjs FROM specObjAll
    JOIN plateX plate ON plate.plate = specObjAll.plate
    WHERE plate.programtype > 0
    GROUP BY plate.programname, specClass
    ORDER BY plate.programname, specClass

    Finding special plates that repeat observations of objects in the main survey      Back to Top


    -- A query to list the primary and special plates that have objects in common
    -- Returns the pairs of special and primary plates, the total number of nights
    -- on which the objects they have in common have been observed, the progam to
    -- which the special plate belongs, and the number of objects the plates
    -- have in common.
    SELECT first.plate, other.plate,
    COUNT(DISTINCT other.mjd) + COUNT(DISTINCT first.mjd) AS nightsObserved,
    otherPlate.programname, count(DISTINCT other.bestObjID) AS objects
    FROM specObjAll first
    JOIN specObjAll other ON first.bestObjID = other.bestObjID
    JOIN plateX firstPlate ON firstPlate.plate = first.plate
    JOIN plateX otherPlate ON otherPlate.plate = other.plate
    WHERE first.scienceprimary = 1 AND other.scienceprimary = 0
    AND other.bestObjID > 0
    GROUP BY first.plate, other.plate, otherPlate.programname
    ORDER BY nightsObserved DESC, otherPlate.programname,
    first.plate, other.plate

    Special program targets      Back to Top


    -- A query to list the spec IDs and classifications of the primary
    -- targets of a special program, in this case fstar51.
    --
    -- Note that the flag may be different for other special programs
    SELECT specObjId, dbo.fSpecClassN(specClass) AS Class FROM specObjAll
    JOIN plateX plate ON plate.plate = specObjAll.plate
    WHERE plate.programName LIKE 'fstar51' AND
    NOT ((primTarget & 0x80002000) = 0)

    Special program data      Back to Top


    -- Find redshifts and types of all galaxies
    -- in the lowz special program with z < 0.01
    SELECT specObjID, z, zErr, zConf, dbo.fSpecClassN(specClass)
    FROM specObjAll
    JOIN plateX plate ON plate.plate = specObjAll.plate
    WHERE plate.programName LIKE 'lowz%' AND specClass = 2 AND z < 0.01

    Repeated high-z objects      Back to Top

    -- Compare different redshift measurements of the same object for objects
    -- with high redshift

    SELECT prim.bestObjId, prim.mjd AS PrimMJD, prim.plate AS PrimPlate,
    other.mjd AS OtherMJD, other.plate AS OtherPlate,
    prim.z AS PrimZ, other.z AS OtherZ, plate.programname
    FROM specObjAll prim
    JOIN specObjAll other ON prim.bestObjId = other.bestObjId
    JOIN platex plate ON other.plate = plate.plate AND other.mjd = plate.mjd
    WHERE other.bestObjId > 0
    AND prim.sciencePrimary = 1
    AND other.sciencePrimary = 0
    AND prim.z > 2.5
    ORDER BY prim.bestObjId

    Spatial Queries with HTM functions      Back to Top

    -- There are several built-in functions available to CAS users that make spatial
    -- queries, i.e., those with coordinate cuts, much more efficient than simply
    -- including the coordinate constraints in the WHERE clause. Some examples:

    -- 1) Rectangular search using straight coordinate constraints:
    SELECT objID, ra, dec
    FROM PhotoObj
    WHERE (ra between 179.5 and 182.3) and (dec between -1.0 and 1.8)

    -- This query can be rewritten as follows to use the HTM function that returns a
    -- rectangular search area:
    SELECT p.objID, p.ra, p.dec
    FROM PhotoObjAll p, dbo.fGetObjFromRect(179.5, -1.0, 182.3, 1.8) r
    WHERE p.objID = r.objID

    -- 2) Radial search for objects near a given position (cone search):
    SELECT objid, ra, dec
    FROM PhotoTag p, dbo.fGetNearbyObjEq(179.5, -0.5, 2.0) n
    WHERE p.objID = n.objID
    Ani Thakar
    Last Modified: Jun 16, 2005