CODEWITHSUNDEEP
sql-server
user1679941
user1679941

Reputation:

How can I group and select 1 row or if there's more than 1 just the last rows with a SQL SELECT

I have this SELECT that runs inside a SQL Server 2014 Stored Procedure. 

SELECT   AdminTest.Title       AS Title,
         COUNT(AdminTestQuestion.AdminTestQuestionId) AS Q,
         UserTest.UserTestId AS UId,
         UserTest.TestStatusId AS Status,
         UserTest.Sequence As Sequence
FROM     AdminTest 
JOIN     UserTest
ON       AdminTest.AdminTestId = UserTest.AdminTestId 
AND      UserTest.UserId = @UserId
JOIN     AdminTestQuestion
ON       AdminTest.AdminTestId = AdminTestQuestion.AdminTestId
GROUP BY AdminTest.Title, 
         UserTest.TestStatusId, 
         UserTest.UserTestId,
         UserTest.Sequence

Here is the data that the select above currently returns:

Title    Q      UId     Status   Sequence
TestA    25     null    0        1
TestA    25     1235    2        2
TestB    10     null    0        1
TestB    10     1237    2        2
TestB    10     1238    2        3
TestC    10     null    0        1

Here is the data that I want to get:

Title    Q      UId     Status   Sequence
TestA    25     1235    2        2 
TestB    10     1237    2        2 
TestB    10     1238    2        3
TestC    10     null    0        1

What I am stuck on is that I need to:

I know this is pretty confusing and I am thinking it's not possible to do with just a SELECT.

I would appreciate any advice on this. If needed I could follow the SELECT with a stored procedure and use a CURSOR but I am really not sure where to start with CURSORS.

Hope someone can advise.

Upvotes: 3

Views: 60

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

According to your comments you might try this:

The query takes all with a "Seq" higher than 1 or with a "Seq" of 1, but only if there is no higher "Seq":

;WITH MyQuery AS
(
    SELECT   AdminTest.Title       AS Title,
             COUNT(AdminTestQuestion.AdminTestQuestionId) AS Questions,
             UserTest.UserTestId AS UserTestId,
             UserTest.TestStatusId AS UserTestStatusId,
             UserTest.Sequence As Seq
    FROM     AdminTest 
    JOIN     UserTest
    ON       AdminTest.AdminTestId = UserTest.AdminTestId 
    AND      UserTest.UserId = @UserId
    JOIN     AdminTestQuestion
    ON       AdminTest.AdminTestId = AdminTestQuestion.AdminTestId
    GROUP BY AdminTest.Title, 
             UserTest.TestStatusId, 
             UserTest.UserTestId,
             UserTest.Sequence
)
SELECT *
FROM MyQuery
WHERE Seq>1
   OR (Seq=1 
       AND NOT EXISTS(SELECT 1 
                      FROM MyQuery AS innerQuery 
                      WHERE innerQuery.Title=MyQuery.Title
                        AND innerQuery.Seq>1)
      );

Upvotes: 1

Related Questions