Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The following may or may not be the right approach for your specific situation, but it can be a useful technique to have at your disposal.</p> <p>You can add some code to your select statement to help yourself answer questions like these 'downstream' (either via added criteria or via SSRS). See <a href="http://sqlfiddle.com/#!3/27b91/5/0" rel="nofollow">this modification of your SQL Fiddle</a>:</p> <pre><code>select pid, firstName, lastName, dateOfBirth, obsName, obsValue, obsDate, rowRank, CASE rowRank WHEN 1 THEN 1 ELSE 0 END AS countableRow from ( select Person.pid, Person.firstName, Person.lastName, Person.dateOfBirth , Obs.obsName, Obs.obsValue, Obs.obsDate, ROW_NUMBER() OVER (PARTITION BY Person.pid, Person.firstName, Person.lastName, Person.dateOfBirth ORDER BY Obs.obsDate) AS rowRank from Person join Obs on Person.pId = Obs.pId ) rankedData </code></pre> <p>The <code>rowRank</code> field will create a group-relative ranking number, which may or may not be useful to you downstream. The <code>countableRow</code> field will be either 1 or 0 such that each group will have one and only one row with a 1 in it. Doing <code>SUM(countableRow)</code> will give you the proper number of groups in your data.</p> <p>Now, you can extend this functionality (if you wish) by dumping out actual field values instead of a constant scalar like 1 in the first row of each group. So, if you had <code>CASE rowRank WHEN 1 THEN dateOfBirth ELSE NULL END AS countableDOB</code>, you could then, for example, get the total number of people with each distinct birthday using just this dataset.</p> <p>Of course, you can do all those things using methods like @Russell's with SQL anyway, so this would be most relevant with specific downstream requirements that may not match your situation.</p> <p><strong>EDIT</strong></p> <p>Obviously the countableRow field there isn't a one-size-fits-all solution to the types of queries you want. I have added a few more examples of the <code>PARTITION BY</code> strategy to <a href="http://sqlfiddle.com/#!3/27b91/10" rel="nofollow">another SQL Fiddle</a>:</p> <pre><code>select pid, firstName, lastName, dateOfBirth, obsName, obsValue, obsDate, rowRank, CASE rowRank WHEN 1 THEN 1 ELSE 0 END AS countableRow, valueRank, CASE valueRank WHEN 1 THEN 1 ELSE 0 END AS valueCount, dobRank, CASE WHEN dobRank = 1 AND dateOfBirth IS NOT NULL THEN 1 ELSE 0 END AS dobCount from ( select Person.pid, Person.firstName, Person.lastName, Person.dateOfBirth , Obs.obsName, Obs.obsValue, Obs.obsDate, ROW_NUMBER() OVER (PARTITION BY Person.pid, Person.firstName, Person.lastName, Person.dateOfBirth ORDER BY Obs.obsDate) AS rowRank, ROW_NUMBER() OVER (PARTITION BY Obs.obsName, Obs.obsValue ORDER BY Obs.obsDate) AS valueRank, ROW_Number() OVER (PARTITION BY Person.dateOfBirth ORDER BY Person.pid) AS dobRank from Person join Obs on Person.pId = Obs.pId ) rankedData </code></pre> <p>Lest anyone misunderstand me as suggesting this is always appropriate, it obviously isn't. This isn't a better solution to getting specific answers using additional SQL queries. What it allows you to do is encode enough information to simply answer such questions in the consuming code all in a single result set. That's where it can come in handy.</p> <p><strong>SECOND EDIT</strong></p> <p>Since you were wondering whether you can do this if race data is stored in more than one place, the answer is, absolutely. I have revised the code from my previous SQL Fiddle, which is now <a href="http://sqlfiddle.com/#!3/898b4/6" rel="nofollow">available in a new one</a>:</p> <pre><code>select pid, firstName, lastName, dateOfBirth, obsName, obsValue, obsDate, rowRank, CASE rowRank WHEN 1 THEN 1 ELSE 0 END AS countableRow, valueRank, CASE valueRank WHEN 1 THEN 1 ELSE 0 END AS valueCount, dobRank, CASE WHEN dobRank = 1 AND dateOfBirth IS NOT NULL THEN 1 ELSE 0 END AS dobCount, raceRank, CASE WHEN raceRank = 1 AND (race IS NOT NULL OR obsName = 'RACE') THEN 1 ELSE 0 END AS raceCount from ( select Person.pid, Person.firstName, Person.lastName, Person.dateOfBirth, Person.[race] , Obs.obsName, Obs.obsValue, Obs.obsDate, ROW_NUMBER() OVER (PARTITION BY Person.pid, Person.firstName, Person.lastName, Person.dateOfBirth ORDER BY Obs.obsDate) AS rowRank, ROW_NUMBER() OVER (PARTITION BY Obs.obsName, Obs.obsValue ORDER BY Obs.obsDate) AS valueRank, ROW_NUMBER() OVER (PARTITION BY Person.dateOfBirth ORDER BY Person.pid) AS dobRank, ROW_NUMBER() OVER (PARTITION BY ISNULL(Person.race, CASE Obs.obsName WHEN 'RACE' THEN Obs.obsValue ELSE NULL END) ORDER BY Person.pid) AS raceRank from Person left join Obs on Person.pId = Obs.pId ) rankedData </code></pre> <p>As you can see, in the new Fiddle, this properly counts the number of Races as 3, with 2 being in the Obs table and the third being in the Person table. The trick is that <code>PARTITION BY</code> can contain expressions, not just raw column output. Note that I changed the join to a left join here, and that we need to use a CASE to only include obsValue WHERE obsName is 'RACE'. It is a little complicated, but not overwhelmingly so, and it handles even fairly complex cases gracefully.</p>
    singulars
    1. This table or related slice is empty.
    1. POSSRS 2008 R2 - evaluating running total only on change of group
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDominic P
    1. USDominic P
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI don't think I can use this for all my answers, but it definitely covers some of them -- thanks! I suspect it will fall down on the ones where I have to say "if race is supplied in the Person table, then true, else if race is supplied in the Obs table, then true, else false".
      singulars
      1. PO
      1. USSarekOfVulcan
    2. CO@SarekOfVulcan, I don't think there's any reason it should fail there, with a little additional manipulation. I believe this method should allow you to isolate any data you need to isolate, even if it is spread across more than one table, and mark it in the result set as needed. (Do you have Race in your Person table, too? I didn't see it there...)
      singulars
      1. PO
      1. USDominic P
    3. COI'll investigate. :-) And yes, I have Race in there -- while capturing everything in one location would be ideal, there are historical reasons for multiple locations (such as, earlier versions not quite caught up with OMB's ideas on Race and Ethnicity....)
      singulars
      1. PO
      1. USSarekOfVulcan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload