Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql statement order by

Posted on 2014-02-03
14
Medium Priority
?
403 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
[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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

688 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