CODEWITHSUNDEEP
sqlsql-serverrow-number
marchemike
marchemike

Reputation: 3277

Using Row number to generate list from sql query

I'm trying to generate a script that gets the 2nd row of a certain id. My First query does it correctly. Howeve as I try to put a list of ID's I am only getting 1 result.

What should be adjusted with my Updated Query to allow multiple results to be shown?

Original Query

;WITH YourTable AS(
           SELECT TOP 2
           EHistory.*,
            ROW_NUMBER() over (order by SignonDate desc) as RowNumber
            FROM
               EHistory
              LEFT JOIN EShip
              ON EHistory.ShipCode = EShip.ShipCode
            WHERE EHistory.EmployeeID = 123456 and IsTRProcessed = 1 
)
SELECT * FROM YourTable WHERE RowNumber = 2;

Generates 1 result which is correct

Updated Query

;WITH YourTable AS(
           SELECT TOP 2
           EHistory.*,
            ROW_NUMBER() over (order by SignonDate desc) as RowNumber
            FROM
               EHistory
              LEFT JOIN EShip
              ON EHistory.ShipCode = EShip.ShipCode
            WHERE EHistory.EmployeeID IN (123456,234567,345678) and IsTRProcessed = 1 
)
SELECT * FROM YourTable WHERE RowNumber = 2;

Only Generates 1 result still

I am having a hard time thinking what should be modified to allow it to display multiple results.

sample results should generate:

<table>
<th>ID</th><th>Ship</th>
<tr>
  <td>123456</td><td>Ship 1</td>
</tr>
</table>

<table>
<th>ID</th><th>Ship</th>
<tr>
  <td>123456</td><td>Ship 1</td>
</tr>
<tr>
  <td>234567</td><td>Ship 2</td>
</tr>
<tr>
  <td>345678</td><td>Ship 3</td>
</tr>
</table>

Upvotes: 0

Views: 435

Answers (1)

Kapil
Kapil

Reputation: 987

Use partition by with Row_number() over(partition by ...).You might also need to remove Top 2 from select statement.

;WITH YourTable AS(
       SELECT 
       EHistory.*,
        ROW_NUMBER() over (partition by EmployeeID  order by SignonDate desc) as RowNumber
        FROM
           EHistory
          LEFT JOIN EShip
          ON EHistory.ShipCode = EShip.ShipCode
        WHERE EHistory.EmployeeID IN (123456,234567,345678) and IsTRProcessed = 1 
)
SELECT * FROM YourTable WHERE RowNumber = 2;

Upvotes: 2

Related Questions