Solved

# How can I retrieve these rows?

Posted on 2014-04-29
198 Views
I have a table that looks like this:

sortorder    statusid   workqueueroleid
1                    NULL          NULL
2                    NULL          NULL
3                    NULL          NULL
4                    30                10
5                    30                10
6                    NULL          NULL
7                    NULL          NULL

What would my select statement look like if I wanted to retrieve all the rows above row #4. In other words, I don't want rows 5, 6 or 7. I only want the rows 1,2 and 3.

How would I do it?
0
Question by:brucegust
• 2
• 2
• 2
• +6

LVL 65

Assisted Solution

Jim Horn earned 105 total points
Use the WHERE clause to filter the rows returned:
``````SELECT sortorder, statusid, workqueueroleid
FROM your_table
WHERE sortorder <= 3
``````
0

LVL 34

Assisted Solution

Brian Crowe earned 50 total points
What is your logic for defining the cut-off?  Is it the first non-null value? Is it just the sortorder value as Jim defines above?
0

LVL 22

Accepted Solution

plusone3055 earned 99 total points
JH logic is correct. an alternative way would also be

SELECT    sortorder
,statusid
,workqueroleid
WHERE    sortorder IN(1,2,3)

This alternative would be if you wanted SPECIFIC row numbers versus where JH would show everything less than or equal to 3

variety :)
0

LVL 22

Assisted Solution

plusone3055 earned 99 total points
his logic
WHERE sortorder <= 3

means  where the sortorder is less than or equal to 3

my logic  IN(1,2,3)

means when the sort order number is 1 or 2 or 3
0

LVL 30

Assisted Solution

Marco Gasi earned 49 total points
if you wanto only the first three records you can use LIMIT:

SELECT    sortorder ,statusid ,workqueroleid FROM  table LIMIT 3
0

LVL 31

Assisted Solution

awking00 earned 98 total points
If you're trying to retrieve all of the rows that contain null statusids and workqueueroleids that exist prior non-null values based onthe sort order, then

select sortorder, statusid, workqueueroleid from
(select sortorder, statusid, workqueueroleid,
row_number() over (order by statusid desc, workqueueroleid desc, sortorder) rn
from table1) as x
where rn = sortorder;
0

LVL 108

Expert Comment

@MarqusG: I think LIMIT is a MySQL construct, not MS SQL.  But that aside, if we know the data criteria we can help craft a WHERE clause.  I just have this nagging feeling that the question has some subtext that we cannot find in the literal text.
0

LVL 30

Expert Comment

Oh, I didn't make attention to the topic! But I should... :)
0

LVL 65

Assisted Solution

Jim Horn earned 105 total points
brucegust - As you can tell, we have multiple excellent answers for you.  It would help if you could confirm for us that they work, and if not give us some more details.
0

LVL 31

Assisted Solution

awking00 earned 98 total points
brucegust, what would you want to retrieve if the rows were like this?
sortorder    statusid   workqueueroleid
1                    NULL          NULL
2                    NULL          NULL
3                    NULL          NULL
4                    NULL          NULL
5                    30                10
6                    30                10
7                    NULL          NULL
0

LVL 69

Assisted Solution

ScottPletcher earned 49 total points
SELECT *
FROM dbo.tablename
WHERE
sortorder < (SELECT MIN(sortorder) FROM dbo.tablename WHERE statusid IS NOT NULL)
0

LVL 14

Assisted Solution

Vikas Garg earned 50 total points
Hi,

You can try this using top and orderby

SELECT top 3 sortorder, statusid, workqueueroleid
FROM your_table
WHERE statusid is null and workqueueroleid is null
order by sortorder
0

Author Comment

Guys, as always, thanks for the assistance. I apologize for not staying tuned in. I wound up going with a different approach that involved a nested table and it worked great.

I'll distribute points based on the obvious time and trouble you took to help. Thanks again!
0

## Featured Post

### Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This article discusses four methods for overlaying images in a container on a web page
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.