select count(*)
/*
g1.objid as g1_id, 
g1.ra as g1_ra, g1.dec as g1_dec, 
g1.modelmag_u as g1_u,  
g1.modelmag_g as g1_g,  
g1.modelmag_r as g1_r,  
g1.modelmag_i as g1_i,  
g1.modelmag_z as g1_z,
g2.objid as g2_id, 
g2.ra as g2_ra, g2.dec as g2_dec, 
g2.modelmag_u as g2_u,  
g2.modelmag_g as g2_g,  
g2.modelmag_r as g2_r,  
g2.modelmag_i as g2_i,  
g2.modelmag_z as g2_z, 
g1.petroR50_r as g1_radius, 
g2.petroR50_r as g2_radius, 
n.distance as separation
*/
FROM Galaxyg1
    JOIN Neighbors n on g1.objID = N.objID
    JOIN Galaxy g2 on g2.objID = N.NeighborObjID
where 
   g1.objId < g2.ObjID
   and N.NeighborType = 3
   and g1.petrorad_u > 0 and g2.petrorad_u > 0
   and g1.petrorad_g > 0 and g2.petrorad_g > 0
   and g1.petrorad_r > 0 and g2.petrorad_r > 0
   and g1.petrorad_i > 0 and g2.petrorad_i > 0
   and g1.petrorad_z > 0 and g2.petrorad_z > 0
   and g1.petroradErr_g > 0 and g2.petroradErr_g > 0
   and g1.petroMag_g BETWEEN 16 and 21
   and g2.petroMag_g BETWEEN 16 and 21
   and g1.modelmag_u > -9999
   and g1.modelmag_g > -9999
   and g1.modelmag_r > -9999
   and g1.modelmag_i > -9999
   and g1.modelmag_z > -9999
   and g2.modelmag_u > -9999
   and g2.modelmag_g > -9999
   and g2.modelmag_r > -9999
   and g2.modelmag_i > -9999
   and g2.modelmag_z > -9999
   and abs(g1.modelmag_g - g2.modelmag_g) > 3
   and (g1.petroR50_r BETWEEN 0.25*g2.petroR50_r AND 4.0*g2.petroR50_r)
   and (g2.petroR50_r BETWEEN 0.25*g1.petroR50_r AND 4.0*g1.petroR50_r)
   and (n.distance <= (g1.petroR50_r + g2.petroR50_r))

Zeljko put together a number of queries about variable objects.

I've implemented them in SQL and appended my solutions to this
message; I could do all except three; one (query8) because I couldn't
think of a solution, and two because some of the fields in the matchHead
tables are not set.

I do NOT guarantee that these are optimal; in fact I don't quite
guarantee that they are even correct.

They are significantly complicated by three problems:
	0. SQL doesn't include any operators to calculate
order statistics such as medians

	1.  The match tables only give the matches. This means
that they include all except one of the observations of a given
object.  I have a workaround in the queries, but maybe we should
add the self-match rows too?  I've flagged them as distance == -1"

	2. The matchHead tables do not guarantee that the objId
of the head is a PRIMARY.  This is a problem when using some of the
the functions that return matches, as some (but not all) only return
primaries. For example, fGetNearbyObjEq() does but fGetObjFromRect()
doesn't,

I think that the best solution would be to make all such dbo.functions
return all objects -- it's easy to say e.g.
	(select objId from dbo.fGetObjFromRect(ra1, ra2, dec1, dec2)
					where mode = @primaryMode) as N

	3. The issues with matchHead.missCount; but this is a separate
issue (which is why I said three problems, not four).

					R

-- N.b. This is designed to be used with my emacs interface; other users
-- may have to evaluate the set statements for @variables, and
-- substitute them by hand.

/*

The crucial table for variability is the MATCH table:

PhotoObj pairs from different runs (times) that probably are the same
object. SDSS primary and secondary objects of type STAR, GALAXY, and
UNKNOWN within 1 arcsec of one another and from different runs (so two
different observations) Table also holds type, mode and distance
information.  See also MatchHead Table.

name		     |unit        |description
---------------------------------------------------------------------------
objID1               |            | The unique objId of the center object
mode1                |            | is object primary,secondary,family
                     |            |    outside
type1                |            | Object type of the start object
objID2               |            | The objId of the matching object
mode2                |            | is neighbor primary,secondary,family or 
                     |            |    outside
type2                |            | Object type of the matching object
distance             | arcmin     | Distance between center and neighbor
---------------------------------------------------------------------------
*/

-- Note: the following variable declarations can be used in CasJobs or
-- the emacs interface, but not in SkyServer.  If you are running the
-- queries in SkyServer, you will need to replace the variables with
-- their actual values.

declare @r numeric        set @r = select 1/60.0 -- search radius, arcmin

declare @ra numeric       set @ra =  217.924624	 -- a QSO
declare @dec numeric      set @dec = -1.2251894

declare @box numeric      set @box = 0.1
declare @ra1 numeric      set @ra1 = select @ra - @box/2.0
declare @dec1 numeric     set @dec1 = select @dec - @box/2.0
declare @ra2 numeric      set @ra2 = select @ra + @box/2.0
declare @dec2 numeric     set @dec2 = select @dec + @box/2.0

declare @bad_flags bigint   set @bad_flags = (dbo.fPhotoFlags('SATURATED') | 
		dbo.fPhotoFlags('BRIGHT') | dbo.fPhotoFlags('EDGE') | 
                dbo.fPhotoFlags('PSF_FLUX_INTERP'))
declare @star int	  set @star = dbo.fPhotoType('star')
declare @galaxy int	  set @galaxy = dbo.fPhotoType('galaxy')

declare @primaryMode int   set @primaryMode = dbo.fPhotoMode('primary')

-- query1

-- 1) Return all detections within R arcsec from a position given
--    by (RA,Dec). Motivation: light curve for a given object

--
-- I assume that R is small enough to lie within the match table radius,
-- so I can first find the object, then use the match table to get the
-- light curve
--

select
   dbo.fSDSS(obj.objId) as ID,
   ltrim(str(60*distance, 12, 2)) as distance,	 -- arcsec
   ltrim(str(mjd_r,20,2)) as MJD,
   modelMag_g, modelMagErr_g,
   modelMag_r, modelMagErr_r,
   modelMag_i, modelMagErr_i
from
   (
      select objId1, objId2, -1/60. as distance FROM match
      union all
      select objId1, objId2, distance FROM match
   ) as M
   JOIN PhotoObj as Obj on (obj.objId = M.objId2)
   JOIN Field as F on F.fieldId = obj.fieldId
where
   M.ObjId1 = dbo.fGetNearestObjIdEq(@ra, @dec, @r)

/*
Notes:

The basic query is:

select
   dbo.fSDSS(obj.objId) as ID
from
   match as M join
   PhotoObj as Obj on (obj.objId = M.objId2)
where
   M.objId1 = dbo.fGetNearestObjIdEq(@ra, @dec, @r)

This is simple enough; find the object nearest to @ra, @dec, look it
up in the match table, and then show us all of its matches. There's a
problem with this approach, namely that it doesn't include the matched
object itself, only its matches.

The solution to this is to use not MATCH but a home-brewed temporary
table (what's the proper word for these?) that includes an entry with
the objid2 equal to the objID.  We should probably fix the
database to do this for us.  With this change, the query becomes:

select
   dbo.fSDSS(obj.objId) as ID
from
   (
      select objId1, objId2, -1/60. as distance FROM match
      union all
      select objId1, objId2, distance FROM match
   ) as M join
   PhotoObj as Obj on (obj.objId = M.objId2)
where
   M.objId1 = dbo.fGetNearestObjIdEq(@ra, @dec, @r)

and the query given above is merely an elaboration of this approach.
*/

--------------------------------------------------------------------------

-- query2

-- 2) Return all detections within a rectangle specified by equatorial
--    or galactic coordinates; index all unique sources, provide the
--    total number of detections for each source, index detections
--    according to time (run number). Motivation: doing light curves
--    for a large number of sources

--
-- I assume that R is small enough to lie within the match table radius,
-- so I can first find the object, then use the match table to get the
-- light curve
--
-- query2a

select
   dbo.fSDSS(M.objId1) as UID,	 -- These are the unique object IDs
   dbo.fSDSS(obj.objId) as ID,
   ltrim(str(60*distance, 12, 2)) as distance,	 -- arcsec
   ltrim(str(mjd_r,20,2)) as MJD,
   psfMag_r, psfMagErr_r,
   dbo.fPhotoTypeN(type) as type
from
   (
      select objId1, objId2, -1/60. as distance FROM match
      union all
      select objId1, objId2, distance FROM match
   ) as M
   JOIN PhotoObj as Obj on (obj.objId = M.objId2)
   join (select objId 
         from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2) 
         where mode = @primaryMode) as N on (M.objId1 = N.objId1)
   JOIN Field as F on (F.fieldId = obj.fieldId)
where
   0 = (flags & @bad_flags)
order by M.objId1

/*
Without the selection on PRIMARY mode we'd double count matches
 */

-- query2b

/*
Here's another way to do the same thing, using the matchHead table. Here
the UID is the entry in the matchHead, and is in not in general a primary.

This approach won't work using table-valued functions that only return
primaries, such as fGetNearbyObjEq(), as not all the entries in
matchHead are primaries
*/

select
   dbo.fSDSS(MH.objId1) as UID,	 -- These are the unique object IDs
   dbo.fSDSS(obj.objId) as ID,
   ltrim(str(60*distance, 12, 2)) as distance,	 -- arcsec
   ltrim(str(mjd_r,20,2)) as MJD,
   psfMag_r, psfMagErr_r,
   dbo.fPhotoTypeN(obj.type) as type
from
   matchHead as MH
   join (
      select objId1, objId2, -1/60. as distance FROM match
      union all
      select objId1, objId2, distance FROM match
   ) as M on (M.objId1 = MH.objID1)
   JOIN PhotoObj as Obj on (obj.objId = M.objId2)
   JOIN Field as F on (F.fieldId = obj.fieldId)
where
   MH.objId1 in (select objId 
                from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2))
   and 0 = (flags & @bad_flags)
order by MH.objId1

-- query3
-- query3a

-- 3) Return all objects with difference between the brightest and
--    faintest state (in a given band, or any band), or between
--    one of the extreme states and the mean/median value, larger/smaller
--    than X mag. Motivation: objects with impeccable photometry,
--    eclipsing binaries, outbursts, etc.

--
-- I'll ask for the min value to differ from the mean by more than 0.1,
-- and add another constraint that the mean magnitude be brighter than
-- some limit. Only search a small region, and only consider stars

-- The extra "select * from" is there so that I can use the calculated
-- quantities in a "where" clause.

select * from (
   select
      dbo.fSDSS(min(M.objId1)) as UID, -- These are the unique object IDs
      count(*) as n,
      min(obj.psfMag_r) as psfMagMin_r,
      avg(obj.psfMag_u) as psfMag_u,
      avg(obj.psfMag_g) as psfMag_g,
      avg(obj.psfMag_r) as psfMag_r,
      avg(obj.psfMag_i) as psfMag_i,
      avg(obj.psfMag_z) as psfMag_z,
      avg(obj.psfMagErr_r)/sqrt(count(*)) as psfMagErr_r,
      avg(obj.psfMag_r) - min(obj.psfMag_r) as psfDelta_r
   from
      (
         select objId1, type1, objId2, -1/60. as distance
	 FROM match
	 union all
	 select objId1, type1, objId2, distance
	 FROM match
      ) as M join
      PhotoObj as Obj on (obj.objId = M.objId2)
   where
      M.objId1 in (select objId 
                  from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
                  where mode = @primaryMode)
      and 0 = (flags & @bad_flags) and psfMagErr_r >= 0
      and M.type = @star
   group by M.objId1
) as RESULT
where
   psfMag_r < 20 and
   psfMag_r - psfMagMin_r > 0.05
order by psfMag_r - psfMagMin_r desc

-- query3b

/*
 * And here's the same query using a median. As Jim Gray says,
 * "The median SHOULD be in SQL but it isn't"; this causes real pain.
 *
 * The pain is made worse as correlated subqueries are only allowed with
 * a scalar value, and the natural way to write the median query uses a
 * table value; I circumvent this by generating an table with medians,
 * and joining it back.
 */
select * from (
   select
      dbo.fSDSS(M.objId1) as UID,     -- These are the unique object IDs
      count(*) as n,
      min(obj.psfMag_r) as psfMagMin_r,
      avg(obj.psfMag_r) as psfMag_r,
      avg(obj.psfMagErr_r)/sqrt(count(*)) as psfMagErr_r,
      min(psfMagMed_r) as psfMagMed_r,
      min(psfMagMed_r) - min(obj.psfMag_r) as psfDelta_r
      ,dbo.fGetUrlNavId(M.objId) as URL
   from
      (
         select objId1, type1, objId2, -1/60. as distance
	 FROM match
	 union all
	 select objId1, type1, objId2, distance
	 FROM match
      ) as M join
      PhotoObj as Obj on (obj.objId = M.objId2) join
      (
         select
	    M50.objId1,
	    (select cast(min(psfMag_r) as float) 
               -- the median, assuming n is odd
	     from (
		  select top 50 percent
		     psfMag_r
		  FROM PhotoObj AS iobj
		  where iobj.objId in
		     (select M50.objId1 union
		     select objId2 FROM match AS MM 
                     where M50.objId1=MM.objId1) and
		        0 = (iobj.flags & @bad_flags)
		  order by psfMag_r desc
	       ) as top50
	    ) as psfMagMed_r
	 from
	    match as M50
      ) as med on med.objId1 = M.objId1
   where
      M.objId1 in (select objId 
	          from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
          where mode = @primaryMode)
      and 0 = (flags & @bad_flags)
      and M.type = @star
   group by M.objId1
) as RESULT
-- /*
where
   psfMagMed_r < 20 and
   psfMagMed_r - psfMagMin_r > 0.05
-- */
order by psfMag_r - psfMagMin_r desc

-- query4

-- 4) Return all objects with rms in a given band greater/smaller
--    than X mag. Motivation: a part of classic variability search

--
-- I'll ask for the rms to be less than 0.02, and only
-- search a small region.
--

select * from (
   select
      dbo.fSDSS(M.objId1) as UID,  -- These are the unique object IDs
      count(*) as n,
      avg(psfMag_r) as psfMag_r,
      stdevp(psfMag_r) as rms_r,  -- Not in ANSI SQL 92
      avg(psfMagErr_r) as psfMagErr_r
   from
      (
         select objId1, objId2, -1/60. as distance FROM match
	 union all
	 select objId1, objId2, distance FROM match
      ) as M join
      PhotoObj as Obj on (obj.objId = M.objId2)
   where
      M.objId1 in (select objId 
	          from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
        where 
          mode = @primaryMode)
      and 0 = (flags & @bad_flags)
      and psfMag_r < 20
   group by M.objId1
) as RESULT
where
   rms_r > 0.05			 -- n.b. psfMagErr^2 isn't subtracted
order by rms_r desc

-- query5
-- query5a

-- 5) Return all objects with chi2 in a given band greater/smaller
--    than Y. Motivation: a part of classic variability search
--
-- I'll only search a small region.
--

select * from (
   select
      dbo.fSDSS(UID) as UID,
      avg(ra) as ra, avg(dec) as dec,
      avg(mean.psfMag_r) as psfMag_r,
      count(*) as n,
      sum(square((obj.psfMag_r - mean.psfMag_r)/obj.psfMagErr_r))/count(*) 
         as rchi2_psfMag_r
   from
      (
         select
	    iM.objId1 as UID,	-- These are the unique object IDs
	    avg(psfMag_r) as psfMag_r
	 from
	    (
               select objId1, type1,
                      objId2, -1/60. as distance 
               FROM match
	       union all
	       select objId1, type1, objId2, distance FROM match
	    ) as iM join
	    PhotoObj as iobj on (iobj.objId = iM.objId2)
	 where
	    iM.objId1 in
	    (select objId 
	     from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
             where mode = @primaryMode)
            and iM.type1 = @star
	    and 0 = (iobj.flags & @bad_flags)
	 group by iM.objId
      ) as mean join
      (
         select objId1, objId2, -1/60. as distance
	 FROM matchwhere mode1 = @primaryMode
	 union
	 select objId1, objid2, distance
	 FROM matchwhere mode1 = @primaryMode
      ) as M on mean.UID = M.objId1 join
      PhotoObj as Obj on (obj.objId = M.objId2)
   where
      0 = (flags & @bad_flags)
   group by UID
) as RESULT
where
   psfMag_r < 20 and
   n > 1 and rchi2_psfMag_r > 2
   
-- query5b
--
-- This query doesn't work:
--	Cannot perform an aggregate function on an expression containing an
--	aggregate or a subquery
--
select
   dbo.fSDSS(min(M.objId1)) as UID,
   count(*) as n,
   sum(square((obj.psfMag_r - avg(psfMag_r))/obj.psfMagErr_r))/count(*)
      as rchi2_psfMag_r
from
   (
      select distinct objId1, objId2, -1/60. as distance
      FROM match
      union
      select objId1, objid2, distance FROM match
   ) as M join
   PhotoObj as Obj on (obj.objId = M.objid2)
where
   M.objId1 in
   (select objId from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)) and
   0 = (flags & @bad_flags)
group by M.objId1

-- query6
-- 6) Return all objects with (max-min) in a given band greater/smaller
--   than k*rms. Motivation: another way to look for variables (using
--    a different light curve statistic)
--
-- I'll only search a small region.
--

select * from (
   select
      dbo.fSDSS(M.objID1) as UID,
      avg(ra) as ra, avg(dec) as dec,
      avg(psfMag_r) as psfMagMean_r,
      min(psfMag_r) as psfMagMin_r,
      max(psfMag_r) as psfMagMax_r,
      -- Two alternative estimates of "RMS"; from the data, or
      -- from the quoted errors.
      -- stdevp(psfMag_r) as psfMagRMS_r, -- Not in ANSI SQL 92
      sqrt(sum(power(psfMagErr_r, 2))/count(*)) as psfMagRMS_r,
      count(*) as n
   from
      (
         select objId1, type1, objId2, -1/60. as distance 
         FROM match
	 union all
	 select objId1, type1, objId2, distance FROM match
      ) as M join
      PhotoObj as Obj on (obj.objId = M.objId2)
   where
      M.objId1 in
      (select objId from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
       where mode = @primaryMode)
      and M.type1 = @star
      and 0 = (flags & @bad_flags)
   group by M.objId1
) as RESULT
where
   (psfMagMax_r - psfMagMin_r) > 2.5*psfMagRMS_r
   and psfMagMean_r < 22
order by psfMagMean_r

-- query7

-- 7) Return all objects with (q75-q50) in a given band greater/smaller
--    than k*(q50-q25), where qN are quartiles. Motivation: another
--    way to look for variables (using a different light curve statistic)

-- N.b. match.objId are not unique, so without both "select distinct"
-- and "union" (not "union all") while looking for percentiles we'll
-- get double counting.

select * from (
   select
      dbo.fSDSS(M.objId1) as UID,    -- These are the unique object IDs
      count(*) as n,
      min(obj.psfMag_r) as psfMagMin_r,
      avg(obj.psfMag_r) as psfMag_r,
      avg(obj.psfMagErr_r)/sqrt(count(*)) as psfMagErr_r,
      min(psfMagQ25_r) as psfMagQ25_r,
      min(psfMagQ50_r) as psfMagQ50_r,
      min(psfMagQ75_r) as psfMagQ75_r,
      min(psfMagQ75_r) - min(psfMagQ25_r) as psfIQR_r
--      ,dbo.fGetUrlNavId(M.objId) as URL
   from
      (
         select objId1, type1, objId2, -1/60. as distance 
         FROM match
	 union all
	 select objId1, type1, objId2, distance FROM match
      ) as M join
      PhotoObj as Obj on (obj.objId = M.objId2) join
      (
         select distinct
	    M25.objId1,
	    (  select
                  cast(min(psfMag_r) as float) -- Q75
	       from (
		  select top 75 percent	 -- not 25%
		     psfMag_r
		  FROM PhotoObj AS iobj
		  where iobj.objId in
		     (select M25.objId1 union
		     select objId2 FROM match AS MM 
                     where M25.objId1=MM.objId1) and
		        0 = (iobj.flags & @bad_flags)
		  order by psfMag_r desc	 -- magnitudes!
	       ) as top25
	    ) as psfMagQ25_r
	 from
	    match as M25
      ) as Q25 on Q25.objId1 = M.objId1 join
      (
         select distinct
	    M50.objId1,
	    (  select
                  cast(min(psfMag_r) as float)	-- Q50
	       from (
		  select top 50 percent
		     psfMag_r
		  FROM PhotoObj AS iobj
		  where iobj.objId in
		     (select M50.objId union
		     select matchObjId FROM match AS MM 
                     where M50.objId=MM.objId) and
		        0 = (iobj.flags & @bad_flags)
		  order by psfMag_r desc	 -- magnitudes!
	       ) as top50
	    ) as psfMagQ50_r
	 from
	    match as M50
      ) as Q50 on Q50.objId1 = M.objId1 join
      (
         select distinct
	    M75.objId1,
	    (  select
                  cast(min(psfMag_r) as float)	-- Q25
	       from (
		  select top 25 percent	 -- not 75%
		     psfMag_r
		  FROM PhotoObj as iobj
		  where iobj.objId in
		     (select M75.objId1 union
		     select objId2 FROM match AS MM 
                     where M75.objId1=MM.objId2) and
		        0 = (iobj.flags & @bad_flags)
		  order by psfMag_r desc	 -- magnitudes!
	       ) as top75
	    ) as psfMagQ75_r
	 from
	    match as M75
      ) as Q75 on Q75.objId = M.objId
   where
      M.objId1 in (select objId 
                  from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2)
                  where mode = @primaryMode)
      and 0 = (flags & @bad_flags)
      and M.type = @star
   group by M.objId1
) as RESULT
-- /*
where
   psfMagQ50_r < 21 and
   (psfMagQ75_r - psfMagQ50_r) > 0.0*(psfMagQ50_r - psfMagQ25_r)
-- */
order by psfMag_r - psfMagMin_r desc

-- query8

-- 8) Return all objects with at least one brightness derivative
--    (m2-m1)/(t2-t1) in a given band greater than X mag. Motivation:
--    another way to look for variables (e.g. Mira variables have huge
--    light curve amplitudes, but never a large time derivatives; RR Lyrae
--    on the other hand have much smaller light curve amplitudes, but much
--    larger time derivatives).

--
-- RHL can't think of a way to do this one. Ideas anyone?
--

-- query9

-- 9) Return all objects that at least once were not detected (that is,
--    their position was observed at least twice, and at least once
--    there was no detection). Motivation: photo problems, eclipsing
--    binaries

--
-- We can't do this (yet) as matchHead.missCount isn't set;
-- well, not without understanding the HTM. The outline of
-- a possible query is given below.
--
-- Even then we won't do a great job with the current schema; I think
-- that we'd do better to include some sort of place-holder for these
-- missing objects in the match tables.
--
select
   dbo.fSDSS(MH.objId1) as UID,	 -- These are the unique object IDs
   dbo.fSDSS(obj.objId) as ID,
   ltrim(str(60*distance, 12, 2)) as distance,	 -- arcsec
   ltrim(str(mjd_r,20,2)) as MJD,
   psfMag_r, psfMagErr_r,
   dbo.fPhotoTypeN(obj.type) as type
from
   matchHead as MH
   join (
      select objId1, objId2, -1/60. as distance FROM match
      union all
      select objId1, objId2, distance FROM match
   ) as M on (M.objId1 = MH.objID)
   JOIN PhotoObj as Obj on (obj.objId = M.objId2)
   JOIN Field as F on (F.fieldId = obj.fieldId)
where
   MH.objId1 in (select objId 
                from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2))
   and MH.missCount > 0
   and 0 = (flags & @bad_flags)
   and psfMag_r < 20
order by MH.objId1

-- query10

-- 10) Return all objects that were detected only m times (or only within
--     T1 days), while their position was observed at least N times
--     (m < N ),  or longer than T2 days (T1 < T2).
--     Motivation: afterglows, microlensing, SNe

--
-- Need matchHead.missCount. 
--

-- query11

-- 11) Like 10, but object must be an unresolved child, and must have
--     at least one resolved sibling. Motivation: SNe in nearby galaxies

--
-- Need matchHead.missCount
--

-- query12

-- 12) Return all resolved objects whose rms for model magnitude in
--     a given band is smaller than X mag, while rms for psf magnitude
--     is larger than Y mag. Motivation: finding nearby galaxies with AGN
--     by nuclear variability

--
-- This won't work, as the psf mag of an extended object depends
-- on the seeing. But the query can be written.
--
-- I'll only search a small region, and correct the RMS errors for
-- the measured errors
--

select * from (
   select
      dbo.fSDSS(M.objID1) as UID,
      --avg(ra) as ra, avg(dec) as dec,
      count(*) as n,
      --
      avg(psfMag_r) as psfMagMean_r,
      stdevp(psfMag_r) as psfMagRMS_r, -- Not in ANSI SQL 92
      sqrt(sum(power(psfMagErr_r, 2))/count(*)) as psfMagErr_r,
      --
      avg(modelMag_r) as modelMagMean_r,
      stdevp(modelMag_r) as modelMagRMS_r, -- Not in ANSI SQL 92
      sqrt(sum(power(modelMagErr_r, 2))/count(*)) as modelMagErr_r
   from
      (
         select objId1, type1, objId2, -1/60. as distance FROM match
	 union all
	 select objId1, type1, objId2, distance FROM match
      ) as M 
      JOIN PhotoObj as Obj on (obj.objId = M.objId2)
   where
      M.objId1 in
      (select objId from dbo.fGetObjFromRect(@ra1,@ra2,@dec1,@dec2) 
       where mode = @primaryMode)
      and M.type = @galaxy
      and 0 = (flags & @bad_flags)
   group by M.objId1
) as RESULT
where
   modelMagMean_r < 22 and
   (square(modelMagRMS_r) - square(modelMagErr_r)) < square(0.02) and
   (square(psfMagRMS_r) - square(psfMagErr_r)) > square(0.2)
order by modelMagMean_r