CODEWITHSUNDEEP
sql
user3133366
user3133366

Reputation: 21

SQL query for TOP 3 results from different tables

I'm new to SQL queries and need help to form the TOP 3 results from different tables.

Here is the code for 1 result per table.

SELECT c.name AS board, a.id,
CASE a.parentid
WHEN 0
THEN a.id
ELSE a.parentid
END AS parentid, a.message, a.name, a.timestamp
FROM posts a
JOIN (
   SELECT MAX(id) AS lastid, boardid
   FROM posts
   WHERE is_deleted = 0
   GROUP BY boardid
) b ON a.id = b.lastid
AND a.boardid = b.boardid
JOIN boards c ON a.boardid = c.id

and output looks like this:

board   id  parentid    message     name    timestamp
=====================================================
board1  123 5           text        text    464587
board2  43  7           text        text    464885
board3  74  34          text        text    463488

and here is the result I'm after:

board   id  parentid    message     name    timestamp
=====================================================
board1  123 5           text        text    464587
board1  456 134         text        text    464657
board1  345 234         text        text    494587
board2  43  7           text        text    464885
board2  44  7           text        text    474885
board2  45  7           text        text    454885
board3  74  34          text        text    463488
board3  75  34          text        text    463499
board3  236 39          text        text    465677

Upvotes: 2

Views: 114

Answers (1)

Maciej Los
Maciej Los

Reputation: 8591

There are two ways:

1) using TOP() clause - http://technet.microsoft.com/en-us/library/ms189463.aspx

2) using ranking functions - http://technet.microsoft.com/en-us/library/ms189798.aspx

1)

SELECT TOP(3) Field1, Field2, ..., FieldN
FROM TableName

2)

SELECT Field1, Field2, ..., FieldN
FROM (
    SELECT  ROW_NUMBER() OVER(ORDER BY Field1) AS RowNo, Field1, Field2, ..., FieldN
    FROM TableName
) AS T
WHERE T.RowNo IN (1,2,3)

Upvotes: 1

Related Questions