Sort Table Descending Without Orderby
Jun 24, 2008can 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
ADVERTISEMENT
Descending Sort On Index
Jul 23, 2005I have a unique index based on the following columns:ProjectID (int)MaterialCatalogID (int)Material catalogues are pretty much static but projects are dynamic andpeople are most likely to be working on the latest project so wouldusing a descending sort on the ProjectID in the index gain anyperformance?
View 2 Replies View RelatedOrder By Descending Date
Feb 1, 2007Here is my sp. I want it to order by descending date, but now it is 1st Feb it is putting this at the bottom, though January is still sorted fine. I need the dates to display in UK format dd/mm/yy
CREATE Procedure [dbo].[spRMU_CountNoDailyUsers]
AS
SELECT CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins
FROM tblUserLog
Where Log_Printed =0
GROUP BY CONVERT(varchar, Log_DateTime, 103) , Log_Username
ORDER BY CONVERT(varchar, Log_DateTime, 103) desc, No_Logins desc
GO
How To Get Dates In Descending Order.
Sep 21, 2007Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.
How do I keep it in DESC order for viewing? Here's the statement:
SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC
Help Me With SP OrderBy
Jan 18, 2005I 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)
OrderBy
Nov 14, 2005Hi !
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?
ORDERBY Name
Nov 14, 2005Hi !
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?
Ordering A Column In Descending With Numbers First
Oct 8, 2013I have a Column which has both numeric values and Alphabets in them.I want to sort them in descending with numbers first and then Alphabets later.
Example:
Source column:
2008
2005
2010
2013
All-Year
Month-year
Required Output:
Source Column:
2013
2010
2008
2005
Month-Year
All-year
Descending Dates In Sql 2005 Vs. Sql 2000
Jun 18, 2007I have been using Access 2003 as a front end to a SQL 2k database. Dates are sorted in a descending fashion. I recently upgraded the sql server database to 2005 and dates can no longer be sorted properly. I have set the database compatibility level to sql2000 within the sql2005 database engine but no luck. Any suggetions on how to fix this?
Thanks in advance.
Sorting A Column In Descending Order Which Contains Two Years
May 21, 2014I am trying to sort this simplied table:
ID - Time
1 2000-2001
2 2002-
3 2001-2003
4 1999
5 2005-2006
I want this as a result:
1999
2000-2001
2001-2003
2005-2006
2002-
Because the "-" means "continues", it the thing is still activated, so if it makes it easier, i could put the today's year afterwards during the query, if it ends with a - ...
Now, simply doing a
SELECT * FROM [table] ORDER BY TIME;
Sorts it "perfectly", apart from the "2002-" is just placed before 2005 and after 2001.
So, of course, it fails on all entries with a leading "-" ...
Right when I clicked "submit", of course, I can simply replace all entries with a time ending with "-", with the todays Year, so at least they will get at the end of the query...
Well, have to do a union, first sorting all without the "-", then sorting all with the "-", and that should be it...
T-SQL (SS2K8) :: Order By On Date Descending In A Column
Jun 18, 2014Below SQL gives the results of 2 columns and I need the DepartmentandDate column to be ORDER BY on date desc for that CID. Expected result is in the screenshot(attachment)...
IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(25))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(111, '2014-01-14 00:00:00.000','B is alphabet'),
[Code] ....
Automatically Sort Table When Table Is Opened In Project
Jul 20, 2005Hi all,We recently upsized two Microsoft Access Databases to SQL. We'reusing an ADP (2002) as the front end.All the conversion issues have been resolved, except for one:Whenever we insert a record into a table, the table isn't sorted byprimary key like I would expect. Instead, the record can be found atthe end of the table. This makes finding a particular record(especially as time goes on) very difficult.I've tried eliminating all indexes except for the primary key, andalso writing AFTER INSERT triggers, but the table still does not sortcorrectly.Any suggestions would be greatly appreciated!Matt
View 4 Replies View RelatedHow Do You Do ASC And DESC With Dynamic OrderBy
Mar 21, 2006In 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
Merged Columns In OrderBy
Jul 20, 2005Lets 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 RelatedCan We Include Orderby In View?.
May 5, 2008
when i tried to put an orderby in view...i am getting an error...
we can't have a orderby in view?.
Simple Select Statement OrderBy
Feb 4, 2008Well 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?
Passing Orderby Clause As Parameter In SP
Jan 24, 2008Hi,
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.
Query With Row Number,Union And Dynamic Orderby.
Dec 19, 2007Hi,
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.
Sort Table
May 10, 2006Hello,
Is there a way to re sort data in a table? based on ID.
The
SORT TABLE tlb_Name ON ID Asc
is not working?
Thank you very much.
Table Will Not Sort
Oct 4, 2007I have quite a long SP which is made up of various tables, temp tables, etc.
I am creating 11 physical tables which i am using to create a DTS package to auto output to an excel spreadsheet (template for a report).
The problem is although i have used ORDER BY statements to sort the data in ascending order the data isnt sorted.
Heres an example of one of the table outputs
--Create Table of Managers
DELETE FROM Weekly_Term_Code_Output7
INSERT INTOWeekly_Term_Code_Output7
SELECT #Temp_Agent.Manager
FROM #temp5 INNER JOIN
#Temp_Agent ON #temp5.agent_login = #Temp_Agent.agent_login
WHERE (NOT (#Temp_Agent.Manager IS NULL))
GROUP BY #Temp_Agent.Manager
ORDER BY #Temp_Agent.Manager
ive also tried placing an order by statement in the output to excel in the DTS package but this also doesnt work?
Any ideas anyone??
Establishing Precedence In ORDERBY Condition Causing Problems.
Jun 11, 2007Hi.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 RelatedDynamic Sort Column And Sort Order Not Working
Aug 7, 2007I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
Simple Sort On A Table...
Jul 23, 2005I am adding this to my c#.Net app...How can I sort all rows in a table?After it is sorted, will it remain so after i do a cnSQL.Close();so that when I then do a:string strSQL = "SELECT InvoiceNumber " +"FROM tblPrintInvoice " ;Is it still sorted for me to do a:drSQL.Read?Thanks,Trint
View 2 Replies View RelatedETL Sort Is Very Slow On A Big Table
Feb 11, 2008Hi,
We have developped an ETL. For development we used small test files (10 000 rows) to test if it works correctly.
This runs in less then a minute
In Test we are using a file which contains all rows (7 million). We did twice a test and we first stopped the process after a week and the 2nd time we stopped the process after a weekend.
We are able to trace the problem to the point where it has to sort the tables.
The proces is pretty simple. We use two connectors to directly connect to the tables.
Then we have two blocks to sort the data. And then we have one block to merge the data.
Should we which to let SQL do the sorting ? Since it is in staging is has no index on that column. A select on the tables with an order by takes 3 minutes to return all those rows.
Any idea's ?
Also is there a page with the best practices for ETL ?
Constantijn
Sort Table By Month
Sep 10, 2007
I have a table report that lists the prior 12 months of sales data. I'd like the report to display in reverse order from current month back. When I sort decedning by month it puts the months in reverse alphabetic order. How can I sort by numeric order?
Thanks.
How To Sort A Table With A Parameter
Jan 8, 2007I have a "Sales shipments" report with several colums. I want a parameter for the user so he choose how to sort. I have two colums in my report I want to sort on: ItemNumber and DeliveryDate.
I added a Report Parameter UserSort with two labels (and values): Item (ItemNumer) and Date (DeliveryDate).
Then I tried to set the sorting
Table Items Custom Sort
Feb 15, 2007hello
i am currently trying to port a "Business Objects" report to SQL Server Reporting Services.
in B.O., there is a possibility to define the order of the table values by previewing the table values and moving them up or down.
Example : the dataset contains data for 3 items, so in the Custom Sort window, I will see these 3 items and I can order them by moving them up or down in a listbox.
is this possible to do in Reporting Services ?
Interactive Sort For A Grouped Table
Aug 24, 2007I'm trying out the interactive sort feature (and have searched high and low for this answer), but can not make the sorting work if I have the data in the table grouped. If I remove the group, the sorting works fine but the output is not acceptable due to the duplicated records in the list.
Can someone steer me in the right direction?
Sort COLUMN NAMES Of A Table
Apr 25, 2008Hi all,
Is there a way i can write a script that will sort the column names of my table?
Example:
Table 1 columns:
First_Name
Last_Name
Address
City
Country
I want to write a script that will sort them like this:
Address
City
Country
First_Name
Last_Name
SQL 2012 :: Temp Table Sort Order
Apr 3, 2013Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
View 5 Replies View RelatedSort Data Table Smallest To Largest
Dec 30, 2014i have table in sql and every month i am appending new data in this table, but i want to sort data Culumn Name "account_no' from smallest to largest, and whenever i append new data to this table it auto sort every time,
View 1 Replies View RelatedTable Interactive Sort With Page Break
Dec 30, 2007Hello
I have a table with a RowNumber Group which shows 10 rows in each page.
it works fine.
in the next step i put an Interactive sorting on one column.
i expect that the sorting is on the table no regarding the presentation which shows only 10 rows.
in reality what happens is that the table was sorted seperated for each part of the 10 rows table,
and no for the whole table.
does any one have a suggestion how to force the Interactive Sort , sorting he Whole table first
and only later to present 10 rows each page (use the group expression)
Thanks,
How To Write A SQL Query To Sort A Table With The Priority Column As Well?
Jan 16, 2005Hi,
Anyone can help me for the Sql query?
I want to sort a table with a priority column, e.g. in the following...
Table A
======
value
======
9
3
1
7
4
======
After sorting:
Table A
========
no value
========
1 1
2 3
3 4
4 7
5 9
========
Anyone can help me?
Thanks.
Daniel.