Solved

sql statement order by

Posted on 2014-02-03
14
389 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 65

Accepted Solution

by:
Jim Horn earned 500 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 65

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 65

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 65

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 65

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
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.

738 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