Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can I retrieve these rows?

Posted on 2014-04-29
13
Medium Priority
?
211 Views
Last Modified: 2014-05-07
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
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +6
13 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 420 total points
ID: 40030075
Use the WHERE clause to filter the rows returned:
SELECT sortorder, statusid, workqueueroleid
FROM your_table
WHERE sortorder <= 3

Open in new window

0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 200 total points
ID: 40030086
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

by:
plusone3055 earned 396 total points
ID: 40030091
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 22

Assisted Solution

by:plusone3055
plusone3055 earned 396 total points
ID: 40030094
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 31

Assisted Solution

by:Marco Gasi
Marco Gasi earned 196 total points
ID: 40030136
if you wanto only the first three records you can use LIMIT:

SELECT    sortorder ,statusid ,workqueroleid FROM  table LIMIT 3
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 392 total points
ID: 40030266
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 111

Expert Comment

by:Ray Paseur
ID: 40030284
@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 31

Expert Comment

by:Marco Gasi
ID: 40030298
Oh, I didn't make attention to the topic! But I should... :)
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 420 total points
ID: 40030302
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 32

Assisted Solution

by:awking00
awking00 earned 392 total points
ID: 40030369
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

by:Scott Pletcher
Scott Pletcher earned 196 total points
ID: 40030659
SELECT *
FROM dbo.tablename
WHERE
    sortorder < (SELECT MIN(sortorder) FROM dbo.tablename WHERE statusid IS NOT NULL)
0
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 200 total points
ID: 40031437
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

by:brucegust
ID: 40047673
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question