?
Solved

sql statement order by

Posted on 2014-02-03
14
Medium Priority
?
412 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
ID: 39830622
sorry can you elaborate the statement
"should sort by the state listed first"
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39830623
select  top 100 percent * from k4kReviewsForSite2013 where state = @stateAb order by state
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39830637
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 39830679
> 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
ID: 39830712
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 66

Expert Comment

by:Jim Horn
ID: 39830740
>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
ID: 39830792
@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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39830810
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
ID: 39830820
can you write it out how I should do it? using my second example?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39830833
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
ID: 39833164
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
ID: 39833973
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
ID: 39834002
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
ID: 39834478
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

607 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