Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

How can I retrieve these rows?

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
brucegust
Asked:
brucegust
  • 2
  • 2
  • 2
  • +6
10 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Use the WHERE clause to filter the rows returned:
SELECT sortorder, statusid, workqueueroleid
FROM your_table
WHERE sortorder <= 3

Open in new window

0
 
Brian CroweCommented:
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
 
plusone3055Commented:
JH logic is correct. an alternative way would also be

SELECT    sortorder
              ,statusid
              ,workqueroleid
FROM      YOUR TABLE
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
plusone3055Commented:
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
 
Marco GasiFreelancerCommented:
if you wanto only the first three records you can use LIMIT:

SELECT    sortorder ,statusid ,workqueroleid FROM  table LIMIT 3
0
 
awking00Commented:
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
 
Ray PaseurCommented:
@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
 
Marco GasiFreelancerCommented:
Oh, I didn't make attention to the topic! But I should... :)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
awking00Commented:
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
 
Scott PletcherSenior DBACommented:
SELECT *
FROM dbo.tablename
WHERE
    sortorder < (SELECT MIN(sortorder) FROM dbo.tablename WHERE statusid IS NOT NULL)
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
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
 
brucegustAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now