Databases
StackOverflow2013
Postsdbo
POLINQ with Subquery/Group By/Join
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLINQ with Subquery/Group By/Join
    text
    copied!<p>Newbie to LINQ, and trying to write the following query...</p> <pre><code>select f.Section_ID, f.Page_ID, f.SortOrder, f.Type from ( select Section_ID, min(SortOrder) as minSortOrder from ContentPages group by Section_ID ) as x inner join ContentPages as f on f.Section_ID = x.Section_ID and f.SortOrder = x.minSortOrder; </code></pre> <p>Notes:</p> <ul> <li>'Section' has many 'ContentPages'</li> <li>Sections are ordered by a 'SortOrder' field</li> <li>ContentPages are also ordered by a 'SortOrder' field</li> </ul> <p>Table: Section<br> Section_ID....Name.......SortOrder<br> ....1.........One..........1......<br> ....2.........Two..........3......<br> ....3.........Three........2......</p> <p>Table: ContentPage<br> Page_ID.......Section_ID.......Title..............SortOrder<br> ....11.............1.......... Page One.............1......<br> ....12.............1...........Page Two.............3......<br> ....13.............2...........Page Three...........2......<br> ....16.............2.......... Page Four............4......<br> ....17.............2...........Page Eight...........5......<br> ....18.............1...........Page Ten.............6......</p> <p>The above query could possibly be written another way, so here's what I'm trying to do:</p> <ul> <li>I need to return a list of the first ContentPage within each Section (when sorted by ContentPage.SortOrder)</li> <li>Sort results by Section.SortOrder</li> <li>Show Section.Name (join on Section_ID?) in the result as well</li> </ul> <p>Last 2 points are not covered by the sql query above and are more of a 'nice to have'... </p> <p>Desired Result<br> Page_ID.......Section_ID...SectionName.....Title..............SortOrder<br> ....11.............1.........One......... Page One.............1......<br> ....13.............2.........Two..........Page Three...........2...... </p> <p>Any help is appreciated. Thanks!</p>
 

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