Selected neighbors in run
-- A more complex query that also uses the Neighbors table.
-- 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. This query also contains
-- examples of setting the output precision of columns with STR.
SELECT TOP 10
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
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)
Repeated high-z objects
-- Compare different redshift measurements of the same object for objects
-- with high redshift
SELECT top 100 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 AS 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
Splitting 64-bit values
-- The flag fields in the SpecObjAll table are 64-bit, but some
-- analysis tools (and FITS format) only accept 32-bit integers.
-- Here is a way to split them up, using bitmasks to extract
-- the higher and lower 32 bits, and dividing by a power of
-- 2 to shift bits to the right (since there is no bit shift
-- operator in SQL.) The hexadecimal version can be used for debugging
-- to make sure you are splitting them up right.
--SELECT TOP 10 objid,ra,dec,
--flags,
---- output the whole bigint as a check
--flags & 0x00000000FFFFFFFF AS flags_lo,
---- get the lower 32 bits with a mask
---- shift the bigint to the right 32 bits, then use the same mask to
---- sget upper 32 bits
--(flags/power(cast(2 as bigint),32)) & 0x00000000FFFFFFFF AS flags_hi
--FROM PhotoObj
-- Hexadecimal version of above query - use for debugging ("Run this Query" button will run this).
SELECT TOP 10 objid,ra,dec,
CAST(flags AS BINARY(8)) AS flags,
CAST(flags & 0x00000000FFFFFFFF AS BINARY(8)) AS flags_lo,
CAST((flags/POWER(CAST(2 AS BIGINT),32)) & 0x00000000FFFFFFFF AS BINARY(8)) AS flags_hi
FROM PhotoObj
Using LEFT OUTER JOIN
-- This query demonstrates the use of a LEFT OUTER JOIN to include rows from the joined table
-- that do not satisfy the JOIN condition. The OUTER JOIN (LEFT or RIGHT)
-- is an inclusive JOIN, whereas the INNER (default) JOIN is an exclusive JOIN.
-- In this case the query returns galaxies with or without spectra that meet the criteria
-- in the WHERE clause. The ISNULL operator is used to replace null
-- values with zeros for galaxies without spectra.
SELECT TOP 10 p.objid, p.ra, p.dec, ISNULL(s.specobjid,0) AS specobjid, ISNULL(s.z, 0) AS z
FROM Galaxy AS p
LEFT OUTER JOIN SpecObj s ON s.bestObjID = p.objID
WHERE
p.u BETWEEN 0 AND 19.6 AND p.g BETWEEN 0 AND 20
Using Nested Queries
-- This query demonstrates, among other things, the use of a nested query (a query
-- inside another query) in which the outer query operates on the result of the inner query. 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 TOP 10 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(primaryArea) 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, count(*) 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