meta data for this page
  •  

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
pluginto:useful_sql_queries [2023/10/19 21:46] – joetulenkopluginto:useful_sql_queries [2024/12/03 17:29] (current) – joetulenko
Line 258: Line 258:
  
 If you want an EXACT match, omit the %'s inside the parentheses. Although this statement is still not case sensitive, just FYI.// If you want an EXACT match, omit the %'s inside the parentheses. Although this statement is still not case sensitive, just FYI.//
 +
 +----
 +
 +\\
 +
 +7. Filtering selection by choosing samples from sites that have AT LEAST 3 samples per site. For example, let's say I only trust moraine ages in the database that are based on at least 3 boulder ages on the moraine, any less and the age might be uncertain.\\
 +**New Feature:** lots of new features in this one, but mainly the GROUP_BY and HAVING COUNT (*) built-in functions.
 +
 +<code>
 +
 +SELECT base_sample.name, base_sample.lat_DD, base_sample.lon_DD
 +FROM base_sample
 +JOIN base_site ON base_site.id = base_sample.site_id
 +WHERE base_site.what LIKE "%moraine%"
 +AND base_sample.site_id IN
 +(SELECT base_sample.site_id FROM base_sample GROUP BY base_sample.site_id HAVING COUNT(*) > 2)
 +
 +</code>
 +
 +//note: the statement before and within the parentheses (I think) works more or less like this:\\
 +1. "AND base_sample.site_id IN" = give me all samples where their site_id field meets the following criteria.\\
 +2."SELECT base_sample.site_id FROM base_sample GROUP BY base_sample.site_id" = within the parentheses argument, select the site_id field from the samples table and group all entries in the samples table by their site_id. \\
 +3. "HAVING COUNT(*) > 2" = once samples are grouped by their site_id, find and select only samples where the number of samples per respective site is greater than 2 (ie at least 3).//
 +
 +----
 +
 +\\
 +
 +8. Query used to filter the ICE-D Alpine application for samples in the Cascades and Sierra Nevada as part of Tulenko et al. (2024): Cosmogenic 21Ne exposure ages on late Pleistocene moraines in Lassen Volcanic National Park, California, USA.
 +
 +<code>
 +
 +SELECT base_sample.name, base_site.short_name, base_calculatedage.t_LSDn, base_calculatedage.dtint_LSDn, base_sample.lat_DD, base_sample.lon_DD, base_calculatedage.nuclide
 +FROM base_sample
 +JOIN base_site ON base_sample.site_id = base_site.id
 +JOIN base_calculatedage ON base_calculatedage.sample_id = base_sample.id
 +WHERE base_sample.what LIKE "%oulder%"
 +AND base_site.what LIKE "%oraine%"
 +AND base_sample.site_id IN
 +(SELECT base_sample.site_id FROM base_sample GROUP BY base_sample.site_id HAVING COUNT(*) > 3)
 +AND (base_sample.lat_DD > 35 AND base_sample.lat_DD < 48)
 +AND (base_sample.lon_DD > -123 AND base_sample.lon_DD < -117)
 +AND (base_calculatedage.t_LSDn > 10000 AND base_calculatedage.t_LSDn < 35000)
 +ORDER BY base_sample.lat_DD
 +
 +</code>
  
 ---- ----
  
 \\ \\