Hi !
I have some table with some "name" field in it which looks like
"Blah blah blah Part 1"
"Blah blah blah Part 2"
"Blah blah blah Part 3"
when I query it
select * from mytable orderby name
it doesn't actually work in a "proper" way.
I keep on getting something like
"Blah blah blah Part 2"
"Blah blah blah Part 1"
"Blah blah blah Part 3"
Do you know what should I do to make it work?
I have a SP that accepts parameters, but the problem is that it is not ORDERING Data as required, It is returning ORDER BY NULL instead of order by my parameter "NAME "here is the output from SQL 2000 DB
'ELP B4 I jump out of LondonBridge
**********************************
SELECT * FROM #TempTable WHERE ID > 0 AND ID < 6 AND EmployerID = 54 AND Job_no = 40
ORDER BY NULL
****************************************
SELECT @MYSTATEMENT = ' SELECT * FROM #TempTable
WHERE
ID > '+ convert(varchar(20),@FirstRec) +'
AND
ID < '+convert(varchar(20),@LastRec) +'
AND EmployerID = '+ convert(varchar(20),@EmployerID)+'
AND Job_no = '+convert(varchar(20),@Job_no)+'
ORDER BY ' +
CASE WHEN @WhichColumn= 'name' AND @sortby= 'asc'
THEN 'name'
ELSE 'NULL'
END
+
CASE WHEN @WhichColumn='name' AND @sortby='DESC'
THEN ' desc'
ELSE ''
END
EXEC (@myStatement)
Hi !
I have some table with some "name" field in it which looks like
"Blah blah blah Part 1"
"Blah blah blah Part 2"
"Blah blah blah Part 3"
when I query it
select * from mytable orderby name
it doesn't actually work in a "proper" way.
I keep on getting something like
"Blah blah blah Part 2"
"Blah blah blah Part 1"
"Blah blah blah Part 3"
Do you know what should I do to make it work?
In the followinf portion of my SQL Statement, I cannot figure out how to specify ASC or DESC for the OrderBy... If I Put OrderDate ASC after the THEN, I get an error and if I put @SortDir after the END I get an error... Can Anyone help with this?
WHERE OrderID IN(SELECT OrderID FROM #rsltTable WHERE ID >= @l_FirstRecord AND ID <= @l_LastRecord)ORDER BYCASE @OrderByWHEN 'OrderDate ASC' THEN OrderDateWHEN 'OrderDate DESC' THEN OrderDateEND
Thank You,Jason
Lets say I have a table PortDates with 3 columns like this:PORT ARRIVALDATE SAILINGDATENewYork 12-15-2004Rio 10-12-2004Hamburg 10-14-2004etc.I want to sort the ports on dates from both date columnsSELECT Port FROM PortDatesORDER BY (ArrivalDate JOIN SailingDate)Now, that doesn't work of course, but what should I write to get it to workso I can order by the dates as if they were one merged column?Join Bytes!
View 7 Replies View Related
when i tried to put an orderby in view...i am getting an error...
we can't have a orderby in view?.
can any one help me to sort a table on the basis of one colomn without using ORDER BY Desc.
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
Well my problem lies in that I am generating reports with the data I retrieve from my sql database. However my problem resides in the fact that I am generating one report at a time and if I want to grab each entry in the order in which they were produced its no problem.
IE - Using PowerBuilder 10.0 as an IDE for my application to generate reports.
select i_id into :insp_id from inspection where i_id = :index order by i_id asc using sqlca;
But now if I want to grab them in alphabetical order from another table I have problems.
this is the code I am trying to use maybe I am just thinking it through wrong.
select s_id into :insp_id from section where s_id = :index order by s_name asc using sqlca;
any help is appreciated. Is there a way to grab each row in alphabetical order?
Hi,
I have a requirement where i have to pass OrderBy clause to an SP as an parameter. I dont want to use dynamic sql query so i am running into problem.
Below is my procedure..
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create Proc [dbo].[USP_GetData]
@sortBy varchar(100),
as
BEGIN
SELECT *
FROM
(SELECT CC.C1, CC.C2, CC.C3, CC.C4,
ROW_NUMBER() OVER(ORDER BY @sortBy) as RowNum
FROM T1 CC
) as e
END
The thing is, if i execute the procedure as
EXEC USP_Get_Data ('C1 asc'), it runs without any error but it gives me unsorted result.
I am not able to figure out why.
Any help in this will be appriciated.
Regards,
Salim.
Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.
Existing procedure:
ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)
select @Cond=''
if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)
if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow
exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int
select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl
set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,
tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;
SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')
RETURN
can anybody help me pls. ITs very urgent.
Thanks in advance.
Hi.I really need some advice on fine-tuning a stored procedurethat is the meat of the search logic on my site. Customersare allowed to save searches, which dumps the search logicin a table called SavedSearches for later access to the search.My problem started with the ORDERBY condition used for zipcodesearches. The condition did something like:"order by CASE WHEN userID=67 THEN 1 WHEN userID=103 THEN 2 WHENuserID=102 THEN 3 WHEN userID=81 THEN 4"Of course, this fails when a customer described in the saved searchresults deletes his profile.I have since attempted to brace against this problem by adding aUserPrecendence table with the following columns: email_address,up_order (or, user precedence order), and userID.Since I have made the precedence changes, I have been unsuccessful ingetting any results (data) back from the query. I think it has to dowith the change but am not quite sure what I am doing wrong.I would appreciate it is somebody could take a look at my sproc withparticular attention to how precedence is handled in the ORDERBYcondition. Maybe you can see something I can not?As always, much appreciated.PS - In addition to the UserPrecedence change, I have attempted to addpaging - returning N amount of pages per request based on passed-inparamaters. I'd appreciate it if you could take a quick glance herealso just to make sure my logic is OK.----------------------------------------------------------------------------------------ALTER PROCEDURE [dbo].[sp_PeopleSearch]@pagenum INT = 1,@perpage INT = 10ASBEGINSET NOCOUNT ONDECLARE@ubound INT,@lbound INT,@pages INT,@rows INTSELECT@rows = COUNT(*),@pages = COUNT(*) / @perpageFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)IF @rows % @perpage != 0 SET @pages = @pages + 1IF @pagenum @pages SET @pagenum = @pagesIF @pagenum < 1 SET @pagenum = 1SET @ubound = @perpage * @pagenumSET @lbound = @ubound - (@perpage - 1)SELECTCurrentPage = @pagenum,PageSize = @perpage,TotalPages = @pages,TotalRows = @rows,UpperBoundary = @ubound,LowerBoundary = @lbound-- this method determines the string values-- for the first desired row, then sets the-- rowcount to get it, plus the next n rowsDECLARE@gender VARCHAR(50),@country VARCHAR(50),@orderby INTEGER,@low VARCHAR(50),@high VARCHAR(50),@photo VARCHAR(50),@sort INTEGERSET ROWCOUNT @lboundSELECT@gender = saved_sex,@country = saved_country,@orderby = saved_orderby,@low = saved_fage,@high = saved_tage,@sort = saved_sort,@photo = saved_photo_stringFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)ORDER BY CASE @sortWHEN 1 THEN tab1.registerDateWHEN 2 THEN tab3.edit_dateWHEN 3 THEN tab4.login_dateWHEN 4 THEN tab5.up_orderEND DESCSET ROWCOUNT @perPageSELECT COALESCE(tab1.emailAddress,tab2.user_name,tab3.email_address,tab4.email_address,tab5.email_address,tab6.email_address)id ,tab1.bday_day ,tab1.bday_month ,tab1.bday_year ,tab1.gender ,tab1.zipCode ,tab1.siteId ,tab1.userID ,tab2.photo_location ,tab2.photo_name ,tab2.photo_default ,tab2.no_photo ,tab3.headline ,tab3.about_me ,tab4.login_date ,tab4.login_isonline,tab5.up_order,tab6.saved_orderby,tab6.saved_sort,tab6.saved_fage,tab6.saved_tageFROM(select distinct emailAddressfrom Customers with(nolock) union select distinct user_namefrom CustomerPhotos with(nolock) union select distinct email_addressfrom EditProfile with(nolock) union select distinct email_addressfrom SavedSearches with(nolock) union select distinct email_addressfrom UserPrecedence with(nolock) union select distinct email_addressfrom RecentLogin with(nolock)) drvLeft Join Customers tab1 on (drv.emailAddress = tab1.emailAddress)Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserIDLeft Join CustomerPhotos tab2 on (drv.emailAddress = tab2.user_name)Left Join RecentLogin tab4 on (drv.emailAddress =tab4.email_address)Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)Left Join SavedSearches tab6 on (drv.emailAddress =tab6.email_address)WHEREtab1.gender = @genderAND tab1.country = @countryAND tab1.bday_year BETWEEN @low AND @highAND tab2.photo_default = 1 + @photo--and not tab2.no_photo = 1--firstName + '~' + lastName-->= @fname + '~' + @lnameORDER BY CASE @sortWHEN 1 THEN tab1.registerDateWHEN 2 THEN tab3.edit_dateWHEN 3 THEN tab4.login_dateWHEN 4 THEN tab5.up_orderEND DESCSET ROWCOUNT 0END
View 7 Replies View Related