Order By Asc Then Desc Links?

Aug 5, 2007

hi, i have headers at the top of my list and would like for people to click the pubs link and it orders the pubs alphabetically descending and when they click again to ascend also to do this with towns and addresses and postcodes?!

can someone please help?

J x

View 2 Replies


ADVERTISEMENT

How Do I Get DESC Order?

Sep 21, 2007



Hey 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

View 4 Replies View Related

Creating Index In DESC Order

Apr 15, 2003

Does anyone have a general rule or guide on when to use this SQL 2000 option when creating indexes? I was thinking generally on nonclustered indexes where the column would be unique and incremental and usually filtered on by range and often used in the order by clause. Such as columns of datetime or integers datatypes. Thanks.

View 1 Replies View Related

Paging And Dynamic Sort Order (ASC/DESC)

Apr 14, 2007

Hi all,I have a SQL statement that allows paging and dynamic sorting of thecolumns, but what I can't figure out without making the SQL a dynamicstring and executing it, or duplicating the SQL statement between anIF and ELSE statement.Following is the SQL statement;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_search]@search VARCHAR( 80 ), @startRow INT = 1, @endRow INT = NULL, @postcode AS CHAR( 4 ) = NULL, @suburb AS VARCHAR( 40 ) = NULL, @stateIdentity AS TINYINT = NULL, @fromLatitude AS REAL = NULL -- latitude the user is located in, @fromLongitude AS REAL = NULL -- longitude the user is located in, @sort TINYINT = 1ASBEGINSET NOCOUNT ON;DECLARE @calculateDistance BIT;SET @calculateDistance = 0;-- get the longitude and latitude if requiredIF ( NOT @postcode IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_postalcodeWHERE(postalcode = @postcode)SET @calculateDistance = 1ENDELSE IF ( NOT @suburb IS NULL AND NOT @stateIdentity IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_localityWHERE(locality = @suburb)AND(stateIdentity = @stateIdentity)SET @calculateDistance = 1END/*ELSE IF ( @fromLatitude IS NULL AND @fromLongitude IS NULL )BEGINRAISERROR( 'You need to pass a valid combination to this storedprocedure, example: postcode or suburb and state identity or longitudeand latitude', 18, 1 );END*/SELECT D1.[row], D1.[totalRecordCount], D1.[classifiedIdentity], D1.[title], D1.[summary], D1.[price], D1.[locality], D1.[state], D1.[postcode], D1.[addedLast24], D1.[dateStamp], D1.[t2Rank], D1.[t3Rank], D1.[tRank], D1.[distance], F.[originalName], F.[extension], F.[uniqueName]FROM(-- derived tableSELECT ROW_NUMBER() OVER ( ORDER BY CASE @sort WHEN 0 THENCAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) )WHEN 1 THEN C.title WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN ( C.locality + ' ' +C.state ) WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC ) AS row, COUNT( * ) OVER() AS totalRecordCount, C.[classifiedIdentity], C.[title], C.[summary], C.[price], C.[locality], C.[state], C.[postcode], CASE WHEN ( C.[dateStamp] >= DATEADD( day, -1, GETDATE() ) )THEN 1 ELSE 0 END AS addedLast24, C.[dateStamp]/* , t1.RANK AS t1Rank */, t2.RANK AS t2Rank, t3.RANK AS t3Rank, /* COALESCE( t1.RANK, 0 ) + */ COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS tRank, CASE @calculateDistance WHEN 1 THEN CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) ELSE 0 END AS distanceFROM [tbl_classified] AS CINNER JOINtbl_locality LONC.localityIdentity = L.localityIdentity/* LEFT OUTER JOINCONTAINSTABLE( tbl_category, title, @keyword ) ASt1ON FT_TBL.categoryIdentity = t1.[KEY] */LEFT OUTER JOINCONTAINSTABLE( tbl_classified, title, @search ) ASt2ON C.classifiedIdentity = t2.[KEY]LEFT OUTER JOINCONTAINSTABLE( tbl_classified, description,@search ) AS t3ON C.classifiedIdentity = t3.[KEY]WHERE ( /* COALESCE( t1.RANK, 0 ) + */COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) ) != 0) AS D1LEFT OUTER JOINtbl_classified_file CFOND1.classifiedIdentity = CF.classifiedIdentityLEFT OUTER JOINtbl_file FONF.fileIdentity = CF.fileIdentityWHERE( row >= @startRow )AND( @endRow IS NULL OR row <= @endRow )ENDThe part I'm having trouble with is making the sort order in thefollowing line dynamicORDER BY CASE @sort WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) ) WHEN 1 THEN C.title WHEN 2 THENCAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude,@fromLongitude, L.latitude, L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN( C.locality + ' ' + C.state ) WHEN 4 THEN CAST( C.price ASCHAR( 10 ) ) END ASCany help would be greatly apprecaited.Thanks

View 1 Replies View Related

ORDER BY NOTESDATE DESC, CREATEDBY ASC --Not Sorting Properly

Mar 15, 2008

I am using sql statement like SELECT CREATEDBY,FIRSTNAME,BUSINESS,NOTES,NOTESDATE FROM BUSINESS ORDER BY NOTESDATE DESC, CREATEDBY ASC
But NotesDate is sorting descending order, but only sorting based on the date and month not on year
Please help me

View 4 Replies View Related

Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number

Aug 21, 2007

I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.

Say I have a table called myTable with columns col1,col2,col3,

Here's what I'm trying to do

with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then


case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else

case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)



Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.

Thanks.

View 7 Replies View Related

Nothing Like 'DESC'

Jan 22, 2004

hi

Like Oracle SQL, does SQL server has a 'desc' command which describes the tables of a database?


It would be very helpful instead of using Enterprise Manager

View 3 Replies View Related

How To Get Column Desc..

Oct 15, 2001

How and where to get table and column descriptions of all system
table and system objects.....etc.,

View 3 Replies View Related

How Do You Do ASC And DESC With Dynamic OrderBy

Mar 21, 2006

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

View 9 Replies View Related

Inserting The Data In DESC Column

Oct 17, 2007

I have a column name "DESC" in SQL Server table and I am getting an error when trying to insert data into this column. I cannot rename this column as it's not in my hand.

Please anybody reply me with good solution I am new to SQL.

Thanks

View 4 Replies View Related

Does DESC/DESCRIBE Have An Equivalent In Transact-SQL

May 7, 2008

Hi,
I have been training in SQL on MySQL, and now that I am on TSQL, I can't seem to find an equivalent to the command:





Code Snippet

DESC table_name;--OR
DESCRIBE table_name;

In MySQL, either command returns a list of the columns of the table, as well specifications about these columns (whether or not they can accept NULL values, their default values, etc.)

View 5 Replies View Related

Sorting GridView With ASC/DESC When Using Stored Procedure???

Feb 19, 2007

I have a stored procedure in my SQL 2005 Server database named Foo that accepts two parameters, @paramA and @paramB.In my ASP.NET page, I have these:<asp:GridView    id="gv"    runat="server"    AutoGenerateColumns="true"    DataSourceID="DS"    AllowSorting="true"    DataKeyNames="ID"/><asp:SqlDataSource    ID="DS"    runat="server"    ConnectionString="<%$ ConnectionStrings:CS1 %>"    SelectCommand="Foo"    SelectCommandType="StoredProcedure"    OnSelecting="DS_Selecting">    <asp:Parameter Name="paramA" Type="String" />    <asp:Parameter Name="paramB" Type="String" /></asp:SqlDataSource>In my setup, paramA and paramB are set in DS_Selecting(), where I can access the Command.Parameters[] of DS.Now, here's the problem. As you can see, the GridView allows for sorting. When you click on a header title to sort, however, the GridView becomes empty. My question is, how can I get the GV sorted and in the correct direction (i.e. asc/desc)? My first step in my attempt was to add another parameter to the SqlDataSource and sotred procedure Foo (e.g. @SortByColumn), then changed Foo appropriately:    ALTER PROCEDURE Foo        @paramA nvarchar(64),        @paramB nvarchar(64),        @SortColumn nvarchar(16) = 'SearchCount'    AS        SELECT * FROM Searches ORDER BY             CASE                WHEN @SortColumn='SearchCount' THEN SearchCount                WHEN @SortColumn='PartnerName' THEN PartnerName                ELSE ID            ENDThat works find and dandy. But wait--I want to get the correct ORDER BY direction too! So I add another parameter to the SqlDataSource and Foo (@SortDirection), then alter Foo:    ...        SELECT * From Searchces ORDER BY            CASE                /* Keep in mind that CASE short-circuits */                WHEN @SortColumn='SearchCount' AND @SortDirection='desc' SearchCount DESC                WHEN @SortColumn='SearchCount' SearchCount                WHEN @SortColumn='PartnerName' AND @SortDirection='desc' PartnerName DESC                WHEN @SortColumn='PartnerName' PartnerName                WHEN @SortColumn='ID' AND @SortDirection='desc' ID DESC                ELSE ID            END    ...But including DESC or ASC after the column name to sort by causes SQL to error. What the heck can I do, besides convert all my stored procedures into in-line statements inside the ASP page, where I could then dynamically construct the appropriate SQL statement? I'm really at a loss on this one! Any help would be much appreciated!Am I missing a much simpler solution? I am making this too complicated?

View 2 Replies View Related

Sorting Desc On Amount Of Blog Comments

Aug 26, 2007

The following statement seems to ignore my sort expression....how can I fix this? Select TB.* FROM TblBlogs TB JOIN (select top 20 blogId, count(*) cfrom tblCommentsgroup by blogIdorder by count(*) desc) T20 ON TB.BlogId = T20.BlogId

View 3 Replies View Related

How To Find If Index Key Is ASC Or DESC From System Tables?

Apr 29, 2003

There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.

Thank you.
Regards,
Anuj Goyal

View 4 Replies View Related

How To Find If Index Key Is ASC Or DESC From System Tables?

Apr 29, 2003

There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.

Thank you.
Regards,
Anuj Goyal

View 2 Replies View Related

Problem With Where Clause When Multiple DESC Indexes

May 12, 2007

I have come across a problem executing a select with a multi-part where clause that only shows up if there are multiple indexes on the table. The situation using a simplified table is shown below

create table tblTest( utcTimestamp datetime NOT NULL, testType int NOT NULL)go

insert into tblTest (utcTimestamp, testType) VALUES('6/1/2003 0:0:0', 100)go

Now, without adding any indexes to the table, I can execute the following select and it works fine, returning the single row in 2003:

select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0' and testType = 100go

Furthermore, if I introduce a single descending index on just the utcTimestamp:

CREATE INDEX IX_tblTest_Timestamp ON tblTest (utcTimestamp DESC)go

the search still works.

HOWEVER, if I now introduce another index:

CREATE INDEX IX_tblTest_EntryType_Timestamp ON tblTest ( testType, utcTimestamp DESC)go

the search does **not** return the row.
However, if I change the where clause to remove the test of testType:

select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0'go

it works.

Also, strangely, if I populate the table with a number of records with different dates and execute the following search:

select * from tblTest where utcTimestamp > '1/1/2004 0:0:0' and testType = 100go

I get records from **earlier** than 1/1/2004 (i.e. like the sense of the compare is wrong)

Finally, as I was writing this report, I discovered that all of these problems go away if the DESC is removed from the indexes - so that's my workaround, but it still looks like a bug.

Thanks
Lionel

View 1 Replies View Related

ASC/DESC Clustered Index - Will It Make A Difference In This Scenario?

Sep 7, 2007

Imagine the following scenario-

Identity(1,1) column ID is primary key and only clustered index key.

Rows will be inserted regularly into this table, hundreds per day.

Queries will be mostly selecting on the most recent records.

In a year, the row will have half a million records or so and only the most recent records will be used. There will be a forward-rolling hot spot, of most recent records.

Does the direction of the ID column in the clustered index make a difference?

I'm thinking no, because query plan will go to that leaf in an index seek regardless of whether it is old or new, "bottom" or "top" of index, especially if the query is very specific on the ID.

I've read this

http://mattadamson.blogspot.com/2005/05/choosing-between-ascending-or.html

but it didn't address (or perhaps didn't need to) this sort of scenario.

View 3 Replies View Related

The Server Failed To Resume The Transaction. Desc:d200000003

Apr 18, 2007

Hi,

We recently migrated our application from SQL Server 2000 to SQL Server 2005 and we also migrated from WAS5 to WAS6.

We are getting the following exception when we call any of the stored procedures.

The server failed to resume the transaction. Desc200000003.

Basically we call these stored procedures frequently using the Java thread scheduler.

We didnt face any problem when we use IBM drivers

I guess this is the problem with the drivers.We use JDBC 2005 driver Version 1.1.

But we didnt face this problems when we use JDBC 2000 driver when we used to connect to SQL Server 2000

The Microsoft says that it has fixed the date problems as well as the transaction problems in the version 1.1.But still are facing the issues.

Can anyone of you please help me in getting the solution for the same?

View 4 Replies View Related

Live Links

Jul 31, 2001

How do you make a live link in SQL 7 to another database? I currently do such
things in Access 97 but how do you do it in SQL? I just want to keep a permanent live link to a table in another database.


Thank you in advance.

View 1 Replies View Related

Links To SSIS

Apr 7, 2008

Thanks in advance!

I am new to SQL Server Development. I will be working on a project which requires a good understanding of SSIS. What are the usual scenarios or tasks I will be given when working on SSIS? Please help!!

View 2 Replies View Related

Relationship Links

Apr 23, 2008

Is there a way of searching tables and linking to the relationship used.

I need to find a list of tutors and the session they teach (both available in a session table)

i then need to find the list of members that are enrolled onto each session.

In the session table the relationship allows me to view what members are on each session but i cannot find a code that allows me to see what tutors teach what session and what members attend these lessons.

I spoke to my tutor who did not offer too much help but did say it may be possible to use the "AND" function

View 6 Replies View Related

Links For Fields

Dec 14, 2005

Have good grasp of mdb's. Beginner with sqlHave sql db of charity organisations with contact details, all fairlysimple. Have one Field for web addresses entered www.xxxxx.co.uk anotherfor emails entered aaaa@bbbb. Unable to work out how to make these active.Its certainly not like mdb's!Created Page with dropdown list which when clicked gives details ofselected charity. Want the above 2 entries 'live'.Any help from the collective intelligence would be gratefully received.Graham

View 2 Replies View Related

Broken Links

May 19, 2008

I downloaded and registered Visual Studio Express Edition. I got this nice email back with links to demos, tutorials, and starter kits. Unfortunately, the links to most of the starter kits are broken. Can I still get these things?

View 1 Replies View Related

Links Between 2 Databases On The Same SQL Server

Aug 7, 2001

hello,

I look for establish between 2 databases on the same SQL server in a query ?

How can I do this ?

Thanks,
Nixo

View 1 Replies View Related

Links A Database From Other Server ...

Aug 7, 2001

hello,

I look for establish a link from a external database to my SQL database ...

How can I do this ?

Thanks,
NIxo

View 2 Replies View Related

Some Links Guide Datetime In Sql !

Feb 17, 2006

I need some links which guide datetime in sql statement !!
Such as : I have a datetime column in my table , I need to calculate
the total orders in the today, or yesterday ...
Any one know ?
Thank you very much .

View 5 Replies View Related

Got Some Great Replication Links????

Nov 24, 2006

can anybody give me some nice links with a detailed explaination of the various replication errors and solution





thanks in advance

Jacx

View 1 Replies View Related

Virtual Links In Database

Aug 13, 2007



Not sure where to place this question...

We have a bunch of SQL databases that are used for a similar number of IIS sites (we are talking of close to 500 sites), dealing with a .NET e-learning application

While we manage to create a virtual folder for the e-learning content, we do have one database for each site. So, when it comes to update the whole platform, we are talking about running one script per site, which may take about 5 seconds per user per site. With a total of 300.000 users and sites, it may come to more than 17 days running the update script.

Is there a way similar to that on IIS to create a symbolic link from one table in one database to another table in a diferent database in a diferent or same server?

Thanks,

AG



View 5 Replies View Related

Clickable Links/Parameters

Jul 6, 2007

I have a Summary page on a report and then a detail page. FOr eg on the summary pg, I have a field called 'Name'. eg summary will be on name ie 'A', B, C ETC.. When the user clicks on the summary name ie A, he should be able to directly go to that details for that particular name ie details of 'A'.

How can I achieve this, will be probably thr parameters, but not sure how.

View 6 Replies View Related

Problems Committing Transactions Over Db-links

Oct 26, 2005

I have a problem with copying data over a db-link. When running the transaction in Query Analyzer it works fine, but when running it via a package, I get the following error-message. Has anyone a solution to this problem ???

Server: Msg 7391, Level 16, State 1, Line 3
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

View 2 Replies View Related

Books Or Links For Report Services

Feb 15, 2007

Can someone tell me good books or links for Report services?

View 1 Replies View Related

Removing All Hypertext Links From A Column

Jun 28, 2007

Is this possible to do using regular expressions in sql 2005?

View 1 Replies View Related

Replication Looking For Errant Sysserver Links

Apr 24, 2007

We have replication successfully running but when I tried to set up a new test instance on completely different servers (Server A to Server B, say) the push agent to create the susbcription on Server B complains



'Could not find server 'Server C' in sysservers. Execute sp_addlinkedserver. . . .'



Server C was never configured as a subscriber for the publication in the first place. What's more, Server C is actually a working linked server and definitely is in sysservers for Server A.



I originally tried this on yet another server pair and got a similar message referencing a server name that no longer exists. I stopped messing with that one, figuring it was some historical naming issue I didn't want to mess with. But now I'm stumped and intrigued. Can anyone help?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved