Solved

sql statement order by

Posted on 2014-02-03
14
384 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 39

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 35
fault SQL backup files that wont restore - how common 3 20
sql server query 18 36
Error when creating a table from a function 6 17
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

840 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