StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I think I've got it - something like:</p> <pre><code>SELECT TOP 1 ReleaseDate FROM Versions WHERE (MajorVersion = @CurrentMajor AND MinorVersion = @CurrentMinor + 1) OR (MajorVersion = @CurrentMajor + 1 AND MinorVersion = 0) ORDER BY MajorVersion, MinorVersion </code></pre> <p>In my test data below, any 1.x version also retrieves a 2.0 version (because it's an OR clause) which is where the TOP 1 and the ORDER BY comes in - it only selects the 1.x record if there is one.</p> <p>This also works if the versions are inter-twined (for example 1.3 was released after 2.0.)</p> <p>FYI here's my table definition:</p> <pre><code>CREATE TABLE [dbo].[Versions]( [MajorVersion] [int] NOT NULL, [MinorVersion] [int] NOT NULL, [ReleaseDate] [datetime] NOT NULL, CONSTRAINT [PK_Versions] PRIMARY KEY CLUSTERED ( [MajorVersion] ASC, [MinorVersion] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] </code></pre> <p>Insert some data:</p> <pre><code>INSERT INTO Versions VALUES (1, 0, '2009-01-01') INSERT INTO Versions VALUES (1, 1, '2009-01-10') INSERT INTO Versions VALUES (1, 2, '2009-01-21') INSERT INTO Versions VALUES (2, 0, '2009-02-01') INSERT INTO Versions VALUES (2, 1, '2009-02-20') INSERT INTO Versions VALUES (1, 3, '2009-03-01') </code></pre> <p>Try it out:</p> <pre><code>1.0 = 2009-01-10 1.1 = 2009-01-21 1.2 = 2009-03-01 1.3 = 2009-02-01 2.0 = 2009-02-20 2.1 = NULL (no rows) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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