Using Row number to generate list from sql query

Question

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>

@Mansoor got it. I'll add the sample data and expected output — marchemike
– marchemike, Commented Apr 25, 2017 at 7:30

Kapil · Accepted Answer · 2017-04-25 07:35:35Z

2

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;

edited Apr 25, 2017 at 7:35

answered Apr 25, 2017 at 7:29

Kapil

1,0876 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Row number to generate list from sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related