Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If it's for SQL Server 2005 or later, you can do:</p> <pre><code>SELECT t.top_level_cat_id, t.category_id, t.post_id, t.post_date FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY top_level.cat_id ORDER BY p.post_id) AS row_num, top_level.cat_id AS top_level_cat_id, child_category.cat_id AS category_id, p.post_id, p.post_date FROM Post p INNER JOIN Category AS child_category ON child_category.cat_id = p.cat_id INNER JOIN Category AS top_level ON top_level.cat_id = child_category.cat_parent ) AS t WHERE t.row_num &lt;= 15 </code></pre> <p>Here's T-SQL I used for creating test tables:</p> <pre><code>CREATE TABLE Category (cat_id INT, cat_parent INT); --top level INSERT INTO Category VALUES(1, 0); INSERT INTO Category VALUES(2, 0); -- child categories INSERT INTO Category VALUES(3, 1); INSERT INTO Category VALUES(4, 1); INSERT INTO Category VALUES(5, 2); CREATE TABLE Post(post_id INT, cat_id INT, post_date DATETIME); INSERT INTO Post VALUES(1, 3, GETDATE()); INSERT INTO Post VALUES(2, 3, GETDATE()); INSERT INTO Post VALUES(3, 3, GETDATE()); INSERT INTO Post VALUES(4, 3, GETDATE()); INSERT INTO Post VALUES(5, 3, GETDATE()); INSERT INTO Post VALUES(6, 3, GETDATE()); INSERT INTO Post VALUES(7, 3, GETDATE()); INSERT INTO Post VALUES(8, 3, GETDATE()); INSERT INTO Post VALUES(9, 3, GETDATE()); INSERT INTO Post VALUES(10, 3, GETDATE()); INSERT INTO Post VALUES(11, 3, GETDATE()); INSERT INTO Post VALUES(12, 3, GETDATE()); INSERT INTO Post VALUES(13, 3, GETDATE()); INSERT INTO Post VALUES(14, 3, GETDATE()); INSERT INTO Post VALUES(15, 3, GETDATE()); -- these records won't appear INSERT INTO Post VALUES(16, 3, GETDATE()); INSERT INTO Post VALUES(17, 4, GETDATE()); INSERT INTO Post VALUES(18, 4, GETDATE()); INSERT INTO Post VALUES(19, 5, GETDATE()); INSERT INTO Post VALUES(20, 5, GETDATE()); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow do i query this complex query in T-SQL / Linq?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAlexander Shirshov
    1. USAlexander Shirshov
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow do i query this complex query in T-SQL / Linq?
      singulars
      1. PTQuestion
    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. VTAcceptedByOriginator
    1. COThanks to your solution I found out a way to implement this on my own query. I've created a stored procedure and it work great. :P
      singulars
      1. PO
      1. USDucDigital
 

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