Databases
StackOverflow2013
Postsdbo
POFinding a single record in a table step-by-step by different requests
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFinding a single record in a table step-by-step by different requests
    primarykey
    data
    text
    <p>There is a table in a databse, let's call this table Document. This table has fields:</p> <ul> <li>MajorVersionNumber</li> <li>MinorVersionNumber</li> <li>ReleaseDate</li> </ul> <p>There are rules to determine the order of versions and their expiration dates. I'll give the rule of finding expiration date of a version in C# 3 because it looks more easy to read then in English.</p> <pre><code> var nextMinorVersion = Versions.FirstOrDefault((version) =&gt; (version.majorVersionNumber == currentVersion.majorVersionNumber) &amp;&amp; (version.minorVersionNumber == currentVersion.minorVersionNumber + 1)); if (nextMinorVersion != null) return nextMinorVersion.ReleaseDate; var nextMajorVersion = Versions.FirstOrDefault((version) =&gt; (version.majorVersionNumber == currentVersion.majorVersionNumber + 1) &amp;&amp; (version.minorVersionNumber == 0)); if (nextMajorVersion != null) return nextMajorVersion.ReleaseDate; return null; </code></pre> <p>Now this rule must be implemented in SQL for MS SQL Server 2005 and 2008. I tried and could make up only very cumbersome, inefficient and ureadable expressions. Taking into account how trivial it looks in C#, I think I can't do it as easy just because I'm not deft with SQL.</p> <p>I'm looking for a way to do it in SQL with relatively the same complexity as in C#.</p> <p>Sorry for such a narrow question, I don't know how to generalize this. Suggestions on generalizing the question and it's title are also very appreciated.</p> <p><strong>UPDATE</strong> For those who are not deft with C# 3 I'll try to explain the rule in pseudocode:</p> <pre><code>if exists nextMinorVersion so that nextMinorVersion.majorVersionNumber = currentVersion.majorVersionNumber and nextMinorVersion.minorVersionNumber = currentVersion.minorVersionNumber + 1 then expirationDate = nextMinorVersion.ReleaseDate else if exists nextMajorVersion so that nextMajorVersion.majorVersionNumber = currentVersion.majorVersionNumber + 1 and nextMinorVersion.majorVersionNumber = 0 then expirationDate = nextMajorVersion.ReleaseDate else expirationDate = null </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDmitry Tashkinov
    1. USDmitry Tashkinov
    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. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFinding a single record in a table step-by-step by different requests
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFinding a single record in a table step-by-step by different requests
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you explain the rule in plain sentence or pseudo-code? I'm not that deft with LINQ ;-)
      singulars
      1. POFinding a single record in a table step-by-step by different requests
      1. USAndy Shellam
    2. COI just realized that there's no LINQ in my sample code, just C# 3 extension methods. Edited the question to correspond to this ;) .
      singulars
      1. POFinding a single record in a table step-by-step by different requests
      1. USDmitry Tashkinov
 

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