Solved

sql statement order by

Posted on 2014-02-03
14
377 Views
Last Modified: 2014-02-10
I am trying to order my list that it should sort by the state listed first, and then the rest of the states after?

currently my sql looks like this:

 select  top 100 percent * from k4kReviewsForSite2013 where state = @stateAb order by id
0
Comment
Question by:rivkamak
  • 5
  • 5
  • 2
  • +2
14 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
sorry can you elaborate the statement
"should sort by the state listed first"
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
select  top 100 percent * from k4kReviewsForSite2013 where state = @stateAb order by state
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
select  top 100 percent *
from k4kReviewsForSite2013
ORDER BY
   -- First sort position
   CASE WHEN state = @stateAb THEN 0 ELSE 1 END,
   -- Second sort position
    stage

To view an example, in the article SQL Server CASE Solutions scroll down halfway to 'A typical practice is to pass a parameter to a stored procedure that is the desired sort order'.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
> sort by the state listed first, and then the rest of the states after?
> where state = @stateAb

btw, The first line above is about ordering all states, i.e. ORDER BY, and the second line above will filter the result set for the @stateAb, i.e. WHERE.

Let us know if these requirements need to be clarified.
0
 

Author Comment

by:rivkamak
Comment Utility
sorry, I think i wasn't clear enough
I have let's say 100 reviews, each with a state.
If I pass in parameter "NY", i want the ones with NY should show up first, then all the other reviews under it.

I really don't want to just have where state = @state, because I wAnt to show all reviews.
I kind of want it to be sorted by that @state paremeter somehow.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>If I pass in parameter "NY", i want the ones with NY should show up first, then all the other reviews under it.
My first comment should do this for you.
0
 

Author Comment

by:rivkamak
Comment Utility
@Jim Horn - the concept works on its own.

Now I am trying to add it to my stored procedure

This is what it looked like before and it worked that it set up a second column numbered and my stored procedure pulls 3 at a time.

with T1 AS (
 select  top 100 percent * from k4kReviewsForSite2013 where state = @stateAb order by id2
),T2 AS
(
 select row_number() over( order by (select 1)) rn, * from T1
)
select * from T2 where rn between @startNum and @endNum order by id2

Open in new window


when I change it to the sql that you gave., when I pull just 3 , it loses the order, and the t1 column is not sorted by state anymore. Would you have any idea why?

with T1 AS (
 select  top 100 percent * from k4kReviewsForSite2013
ORDER BY
   -- First sort position
   CASE WHEN state = @stateAb THEN 0 ELSE 1 END

),T2 AS
(
 select row_number() over( order by (select 1)) rn, * from T1
)
select * from T2 where rn between @startNum and @endNum order by id2

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
In the first block, the 'WHERE state = @stateAb' will filter the set for only the current state, which is not what you want.

In the second block, if you're using a Common Table Expression (T1), you don't put the ORDER BY in the cte, you put it in the query that uses the CTE and returns the set.
0
 

Author Comment

by:rivkamak
Comment Utility
can you write it out how I should do it? using my second example?
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Sure, but first explain to me what you're trying to do with the T2 query.  

Ignoring it ...
SELECT * 
FROM k4kReviewsForSite2013
ORDER BY
   -- First sort position
   CASE WHEN state = @stateAb THEN 0 ELSE 1 END, 
   -- Second sort position
   state

Open in new window

Also, is this a view or a SP?
0
 

Author Comment

by:rivkamak
Comment Utility
what I am trying to acomplish started out as this question:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_28181026.html
maybe i'm doing it wrong and I should be rewording the entire stored procedure.

what do you think?
0
 

Author Comment

by:rivkamak
Comment Utility
right now the procedure looks like this and it's not working properly.
the first select statement works on it's own, once I add the second column I lose the sort .
    -- Insert statements for procedure here
with T1 AS (
 /*select  top 100 percent * from k4kReviewsForSite2013 where state = @stateAb order by id2*/
select  top 100 percent * from k4kReviewsForSite2013 ORDER BY
   -- First sort position
   CASE WHEN state = @stateAb THEN 0 ELSE 1 END, idforstate

),T2 AS
(
 select row_number() over( order by (select 1)) rn, * from T1
)
select * from T2 where rn between @startNum and @endNum order by idforstate

Open in new window

0
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
try this

with T1 AS (
select  *,ROW_NUMBER() OVER( ORDER BY  CASE WHEN state = @stateAb THEN 0 ELSE 1 END, idforstate) RN from k4kReviewsForSite2013 where state = @stateAb
) 
select * from T1 where rn between @startNum and @endNum order by idforstate

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
select  top 100 percent * from k4kReviewsForSite2013 where state = @stateAb order by id
In case no one has pointed this out already, doing SELECT TOP 100 PERCENT ... ORDER BY is totally pointless as there is no guarantee the results will be sorted:  SQL Server is smart enough to know you want all rows and will ignore the ORDER BY clause.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now