October 2, 2013 at 7:45 am
Where can i place my ORDER BY statement to sort ALL the data from the table, not just the 'from' and 'to' records?
Everywhere i try to move it, i get the above error.
Thanks for any help!
USE [JobPortal9_10_13]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- Description: <Description,[Get All Jobs Searched Structured SQL],>
--
-- =============================================
alter Procedure [dbo].[zianGetAllJobsSearched10_1_13FAST]
-- Add the parameters for the stored procedure here
@Title varchar(250),
@Industry int,
@Industry2 int,
@Industry3 int,
@Date int,
@JobTitle int,
@JobType int,
@Experience int,
@education int,
@State int,
@City int,
@Salary int,
@MaxSalary int,
@fromRec int,
@toRec int,
@OrderBy VARCHAR(50),
@OrderByDirection VARCHAR(10)
WITH RECOMPILE
AS
DECLARE @ActualDate DateTime = cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20));
IF (@Title = '')
SET @Title = NULL;
WITH EMPLOYMENT_OPPORTUNITIES_CTE(id,title,contactperson,lastmodified,description,workexperiencetypeid,
workexperience,jobtypeid,AcademicExperienceTypeId,
industryid,industryid2,industryid3,salaryminid,salarymaxid,
--jobTitle,
city,state,
PostalCode,positions,lastmodified2)
--,deadline)
AS(
SELECT
e.id,title,contactperson,lastmodified,e.description,workexperiencetypeid,
isnull((select we.[Name] from workexperiencetypes we where we.id=e.workexperiencetypeid),'') as workexperience,
isnull((select jot.[Name] from jobtypes jot where jot.id=e.jobtypeid),'') as jobtype,
isnull((select edu.[Name] from Degree edu where edu.Id=e.AcademicExperienceTypeId),'') as education,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid),'') as industryname,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid2),'') as industryname2,
isnull((select ind.[Name] from industries ind where ind.id=e.industryid3),'') as industryname3,
salaryminid,salarymaxid,
--isnull((select jt.[Name] from jobTitles jt where jt.id=e.jobtypeid),'') as jobTitle,
isnull((select ci.[Name] from cities ci where ci.id=c.cityid),'') as city,
isnull((select r.[AbbreviatedName] from regions r where r.id=c.regionid),'') as state,
isnull((select comp.[PostalCode] from Companys comp where comp.Id=c.id),'') as PostalCode,
positions,
substring(cast(e.LastModified as varchar(20)),1,12) as lastmodified2
--ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RowNumber
from EmploymentOpportunities e With (nolock)
--left outer join companys c on e.officeid=c.id
--inner join companys c on e.officeid=c.id
inner join companys c on e.companyid=c.id
where (@Title IS NULL or title = @Title)
and (@Industry = 0 OR e.industryid = @Industry)
and (@Industry2 = 0 OR Industryid2 = @Industry2)
and (@Industry3 = 0 OR Industryid3 = @Industry3)
and (@Date = 0 OR lastmodified >= @Date)
and lastmodified is not null and lastmodified > @ActualDate
--and e.LastModified >''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
--and (@JobTitle = 0 OR title = @JobTitle)
and (@JobType = 0 OR jobtypeid = @JobType)
and (@Experience = 0 OR workexperiencetypeid = @Experience)
and (@Education = 0 OR academicexperiencetypeid = @education)
and (@State = 0 OR c.RegionId = @State)
and (@City = 0 OR c.CityId = @City)
and (@Salary = 0 OR SalaryMinID >= @Salary)
and (@MaxSalary = 0 OR SalaryMaxID <= @MaxSalary)
)
SELECT
id,title as Title,contactperson as ContactPerson,lastmodified,description as Description,workexperience,
jobtypeid as jobtype,academicexperiencetypeid as education,industryid as industryname,industryid2 as industryname2,industryid3 as industryname3,
salaryminid as salary,salarymaxid as maxsalary,
--jobtitle as jobTitle,
city + ', ' + state + ', ' + PostalCode as Location,
positions as Positions,
--deadline,
rownumber as RowNumber,
(select COUNT(*) from EMPLOYMENT_OPPORTUNITIES_CTE) as TotalCount FROM (
SELECT
id,title,contactperson,lastmodified,description,workexperience,
jobtypeid,academicexperiencetypeid,industryid,industryid2,industryid3,salaryminid,salarymaxid,
--jobtitle,
city,state,PostalCode,
--Location,
positions,
--deadline,
ROW_NUMBER() OVER (ORDER BY LastModified DESC ) RowNumber
FROM EMPLOYMENT_OPPORTUNITIES_CTE
) p1
WHERE RowNumber BETWEEN @fromRec AND @toRec
ORDER BY
CASE WHEN @OrderBy = 'LastModified'
AND @OrderByDirection = 'D'
THEN LastModified END DESC,
CASE WHEN @OrderBy = 'LastModified'
AND @OrderByDirection != 'D'
THEN LastModified END,
CASE WHEN @OrderBy = 'City'
AND @OrderByDirection = 'D'
THEN City END DESC,
CASE WHEN @OrderBy = 'City'
AND @OrderByDirection != 'D'
THEN City END,
CASE WHEN @OrderBy = 'State'
AND @OrderByDirection = 'D'
THEN State END DESC,
CASE WHEN @OrderBy = 'State'
AND @OrderByDirection != 'D'
THEN State END,
CASE WHEN @OrderBy = 'Title'
AND @OrderByDirection = 'D'
THEN Title END DESC,
CASE WHEN @OrderBy = 'Title'
AND @OrderByDirection != 'D'
THEN Title END
OPTION(Maxdop 8)
October 2, 2013 at 8:20 am
ORDER BY operates at the outermost level of a query and on the output rows. Where you've placed it in your sproc looks correct.
It doesn't make logical sense to order data which isn't output. What colour is your Gulfstream Jet? 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2013 at 8:36 am
You should take a look at this article. It explains how to do this type of query and keep the performance acceptable.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
--EDIT--
NVM. I missed the RECOMPILE in there when I read it at first.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 2, 2013 at 8:47 am
Thanks for the feedback !
So how does Ebay do it?
If you go to the Ebay Motors section, they only display 25 records, but when you use the 'sort by' feature, it sorts ALL 65k records, and returns a new group of 25 based on the sort criteria.
I am trying to do the same thing.
October 2, 2013 at 8:49 am
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2013 at 9:05 am
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply