Impossible? Sort On Dynamic Field (in Combination With Row_number)
Aug 17, 2007
I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.
I use custom paging in combination with ROW_NUMBER
So I want to sort items in one table based on the number of occurences of that item in another table...
I have the following query (which doesnt work):
SELECT *
FROM
(
select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,
m.title,m.usercode,m.mediaid,
mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)
FROM tblAlbums a
inner join tblMedia m on am.mediaid=m.mediaid
left join tblMediaComments mc on mc.mediaid=m.mediaid
WHERE a.showTo=1
group by m.title,m.usercode,m.mediaid
) as Info
WHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
I 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
Hi there, my situation is I have a table x with 3 filed a nvarchar(100), b smalldatetime, c text(16) . I want to create a view like this: select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%
So, I always get a message error said wrong datatype, how can i do, please help me.
Having a brainfart....I need a query that returns a record count, based on two distinct fields.For example:Order Revision Customer001 1 Bob001 2 Bob002 1 John003 1 John004 1 John005 1 Bob006 1 Bob006 2 BobThe query on the above data should return a count of orders, regardless ofthe revision numbers (each order number should only be counted once).So WHERE Customer = 'Bob', it should return OrderCount = 3TIA!Calan
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.
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library. I get this error when i used into storeprocedure : ROW_NUMBER() function is not recognized in store procedure. i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library. I need to add that function into MS SQL SERVER 2005 database library. Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
Hi,I'm trying to create a Stored Procedure that returns a recordset, but Iwant to be able to choose the ORDER BY clause in mijn parameter list ofthe Stored Procedure. Since CASE .. WHEN can only be used in the SELECTclause, I came up with the following:-- BEGIN SCRIPT --DECLARE @blah AS VARCHAR(20)SET @blah = 'DOSSIER_CODE'SELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)NrOfNotes,CASE @blahWHEN 'DOSSIER_CODE'THEN DOSSIER_CODEWHEN 'SCAN_DATE'THEN SCAN_DATEELSESCAN_DATEEND AS ORDERFIELDFROM MR_DOCSLEFT OUTER JOIN MR_USERSON MR_DOCS.USER_FID = USER_PIDLEFT OUTER JOIN MR_DOSSIERSON DOSSIER_FID = DOSSIER_PIDLEFT OUTER JOIN MR_NOTESON DOC_PID = MR_NOTES.DOC_FIDWHERE MR_DOCS.USER_FID = 1AND DOC_STATE IN (1, 3, 4)AND REMINDER_DATE <= getdate()AND MR_DOCS.isVisible = 1AND TREE_FID IS NULL-- Added by Tim Derdelinckx - 2005.06.20AND TODO_FID IS NULL-- Select documents that are scanned for this user (1),-- or moved to this user (3),-- or forwarded to this user (4),GROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODEUNIONSELECT DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODE, COUNT(NOTE_PID)NrOfNotes,CASE @blahWHEN 'DOSSIER_CODE'THEN DOSSIER_CODEWHEN 'SCAN_DATE'THEN SCAN_DATEELSESCAN_DATEEND AS ORDERFIELDFROM MR_DOCSLEFT OUTER JOIN MR_USERSON USER_FID = USER_PIDLEFT OUTER JOIN MR_DOSSIERSON DOSSIER_FID = DOSSIER_PIDLEFT OUTER JOIN MR_NOTESON DOC_PID = MR_NOTES.DOC_FIDWHERE BORROW_USER_FID = 1AND DOC_STATE = 5AND REMINDER_DATE <= getdate()AND MR_DOCS.isVisible = 1AND TREE_FID IS NULL-- Added by Tim Derdelinckx - 2005.06.20AND TODO_FID IS NULL-- or borrowed to this userGROUP BY DOC_PID, SCAN_DATE, DOC_STATE, isViewed, DOC_COMMENT,requestDelete, USER_FNAME, USER_NAME, DOSSIER_CODEORDER BY ORDERFIELD DESC-- END SCRIPT --But it doesn't seem to work correctly:When SET @blah = 'SCAN_DATE', it works just fine!When SET @blah = 'DOSSIER_CODE':I get an error: Server: Msg 242, Level 16, State 3, Line 3The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.Warning: Null value is eliminated by an aggregate or other SEToperation.Anyone any ideas about this? Or maybe another way of handling this (notwith CASE .. WHEN)?Thanks a lot,Tim@Allgeier*** Sent via Developersdex http://www.developersdex.com ***
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
I've seem some good posts similair to this, but I haven't been able to find my exact issue.
I have the following table:
ID Name Location Start End
1 Joe NY 2000 2001
1 Joe CA 2002 2004
1 Joe MA 2005 2008
2 Sue NJ 2003 2004
2 Sue FL 2004 2008
3 Bob CA 1999 2000
3 Bob WA 2001 2004
3 Bob OR 2005 2006
3 Bob MI 2007 2008
As you can see, the Location, Start and End dates can vary for each person and I don't know how many rows a single person might have.
The result I want, is a "pivot like" table.
ID Name Location1 Start1 End1 Location2 Start2 End2 Location3 Start3 End3 Location4 Start4 End4
1 Joe NY 2000 2001 CA 2002 2004 MA 2005 2008
2 Sue NJ 2003 2004 FL 2004 2008
3 Bob CA 1999 2000 WA 2001 2004 OR 2005 2006 MI 2007 2008
I assume I can first do a count of the maximum rows for an individual and that is greatest number of columns I would need. But doing that and trying to figure out the rest has really stumped me.
Any thoughts, ideas and suggestions would be greatly appreciated.
Hiive got a table and contains a surname text field. Why cant i do a select statement ORDER BY surname. I get an error saying i cant sort a text field! how do i go around it!thanks
I have a quick question that i have just a simple calc field.
Field.Value-Field.value then i want to sort by the calculated results. The expression is fine i know it is written wrong here but i cannot figure how to sort by the result of the expression. Thanks.
I want to sort on a field that I construct during a query....how can I solve this?Basically what i am doing is selecting mediafiles with comments in descending order. (so mediafile with most comments on top)Here's my (not working) query, because it throws the error that the mediaComments column is unknown....SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,mediaComments=(select count(*) from MediaComments where mediaid=m.mediaid)FROM Media m WHERE m.usercode>0group by m.title,m.usercode) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
I am creating a report using report builder and I am using several fields from various entities. Is there a way to sort the data in the report using a field that is not being displayed in the report? The "sort and group" option lists only fields that are selected for output in the report... but is there a way to do sorting using a field that is not displayed in the report?
OK I have a report that needs an interactive sort on a calculated field. I get the message: "Report items cannot be used in sort expressions"
That's the whole reason we purchased SS*S and are putting up a Data Warehouse, so we can rank and analyze our data. Surely there is a way to do this?? Thanks for any advice!
Version 2.2.32.7 1.0.0.0 2.0.0.0 1.2.0.0 2.1.8.8 1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly? Once sorted ascending, report should show
Version  1.0.0.0  1.2.0.0  1.4.11.0  2.0.0.0  2.1.8.8  2.2.32.7
Hi, i have a stored procedure like this: CREATE PROCEDURE dbo.spQuery1
@dynamicField nvarchar(10)
AS
SELECT id, productName, productDescription
FROM products
WHERE @dynamicField = 'True'
RETURN The products table has id, productName, productDescription, property1, property2, property3 fields So my query variable should be, for example, "property1" and the result should be every row with "property1" set to true... but it doesnt work with the sql like this... any help?
Hello all, I use Asp.net 1.1 vb script and MS SQL server 2000. I'm wondering if there's a way to put a database field into the title meta tag. For example if I have a detail page for cars which pulls the text info from the SQL data base and I want the field "name" in the title tag details.aspx?name=Hummer would have the Title meta tag as Cars..."Hummer" and details.aspx?name=Corvette would have the Title meta tag as Cars..."Corvette" so that for each car showing in details.aspx page it wouldn't have to have the same title tag for search engines
Can someone point me in the right direction to solve the following(basic) SQL problem below using SQL Server:Let's say I have a table like this that lists people's likes:CREATE TABLE likes (myname VARCHAR (60),travel BIT,eatingout BIT,disco BIT,swimming BIT);Let's say I put the following data inside this table:INSERT INTO likes VALUES ('Darren', 1,0,0,1)INSERT INTO likes VALUES ('John',1,1,0,1)INSERT INTO likes VALUES ('Peter',0,0,0,0)INSERT INTO likes VALUES ('Jill',0,0,0,1)Then what I want is to create a (view? Or function? – I am not sure),called ‘likes_details' that when I send this query:SELECT myname, likes FROM likes_detailsReturns the following:Myname likesDarrenTraveling and swimmingJohnTraveling, eating out and swimmingPeterDone not like anythingJillSwimming onlyPlease! Can anyone help!!Thank you in advance.
Hello - Have a project where we are going to build a form creation application. (ASP.NET). This will allow an administrator to build a form on the fly - this form will appear on the front end of the site.This is a fairly common thing. Are there any resources out there as to where to start designing the DB schema? I'm not looking to reinvent the wheel. Here's the basic objects I'm seeing:TablesFormsFormFieldNamesFormFieldTypesFormFieldJSAny tips on the right direction to go?ThanksRob
Hi,I am trying to create a stored procedures (SQL 7.0), to provide dataina crosstab format.(I'm using Crystal Reports 8.5, but the Crosstab capabilities areterrible, so I have to do as much as possible on the SQL side)I have a table [Occurrences] with the following fields:Year (int)Month (int)Occurs (int)Claims (int)I need a query to give me the following format:Acct_Month 2001 2002 2003Occurs Claims Occurs Claims Occurs ClaimsJanuary 120 180 132 196 110 140February 154 210 165 202 144 178March etc.......Catch! I need the Year field name to be the contents of the fieldYear in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2approach.I got the month name ok...Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+Convert(Char(4),Year))Is it possible to do this easely, without the use of cursors?Any help would be much appreciated.Luis Pinto
Some categories have sub-categories. Therefore, some entries will have null for parentID while others will point to another category.
I need to select all of the categories, but have them returned in the correct parent/child order (parents BEFORE children). I can't seem to get this to work correctly. Is this not possible or am I just being stupid?? :)
I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure
CREATE PROCEDURE [update_Alarm_1]
declare @datetime1 ?????
declare @datetime2 ?????
[SELECT Coming FROM Student_Log where datepart(hour, AL_Coming) = 9 and AL_State = 0 ] = @datetime1
[SELECT Coming FROM Student_Log where datepart(hour, AL_Coming) = 9 and AL_State = 1 ] = @datetime2
I need to store the output of two select statement in a Temporal Table inside a storeProcedure. and used them later in a Select statement in the same Procedure
CREATE PROCEDURE [update_Alarm_1]
declare @datetime1 ?????
declare @datetime2 ?????
[SELECT Coming FROM Student_Log where datepart(hour, AL_Coming) = 9 and AL_State = 0 ] = @datetime1
[SELECT Coming FROM Student_Log where datepart(hour, AL_Coming) = 9 and AL_State = 1 ] = @datetime2
First of all, here's a description of the three tables involved in the query:
clients
id name
services
id name
clients_services
id client_id service_id
As you can imagine, I have a list of clients and each client can have several services assigned to him. So, I have trying to come up with a query that would allow me to do display the following information inside a DataGridView without the use of any additional code:
I would like to be able to display all the services included in the services table and then, depending on the client that is currently selected, to have each of the services display a checkbox in the DataGridView showing the services currently assigned to that particular client. Something like this:
The services table has the following entries: Research, Consulting, Development, Data Protection and Design The selected client id is "2" That client has the following services assigned to him: Development and Design The DataGridView should then display:
I've created a database : hotelselect * from hotel worksselect * from hotel where dpt='5' doesn't work : generate a errorAnyone has a explanation ?create table hotel ([hoteID] int IDENTITY (5000,1) NOT NULL,[dpt] ntext NOT NULL) ON [primary]
We currently have a pull transactional subscription running from a remote 'PublicationServer' in another country to our local server.
After restarting our fire wall we get the following error message: "The process could not execute '{call sp_MSget_subscription_guid(16)}' on 'PublicationServer'."
This message is followed by another error message: "Another distribution agent for the subscription(s) is running.", although nothing was changed to the configuration and no SQL processes were started or restarted.
When we restart the distribution job manually it runs as if nothing happened.
Does anyone know what exactly caused the failure, and how to prevent it ?
I'm trying to create a merge between 2 tables, the first coming from Oracle and the second resident on SQL 2005. The result I'd like to retrieve would be a table composed from the original data contained in the SQL table, update with new or changed rows coming from Oracle (the 2 tables are identical, I'm building an update job..). The final table will be the same table used for data source.
I setup connections and everything seems to works, but when I link the Merge block with the 2 sources blocks, double clickin on the Merge Block, I notice that every SQL table's column is set to "ignore". When I try to change it, SSiS says:
Failed to set property "OutputColumnLineageID" on input column ...
What's wrong? I've set IsSorted property to True and each column with the righ position...
I've also another question: actually my tables hasn't a "last update" column, so I think the merge "block" can't just update the SQL existing rows but it will add only newest rows. Is that right? How can I easily implement an "update check" in your opinion?
Hello there!I am working with MS SQL Server 2000.I have a table function that takes an integer parameter and returns atable, and I can successfully use it like this (passing a literalas a parameter):SELECT * FROM MyTableFunction(1)or like this (passing a variable as a parameter):DECLARE @i AS intSELECT @i = 10...SELECT * FROM MyTableFunction(@i)Now, if I have another function -- a scalar function that returns aninteger, I cannot find a way to use it to specify the parameter valuefor the first function. For example, when I writeSELECT * FROM MyTableFunction( dbo.MyScalarFunction() )SQL Server issues the following complaint:Incorrect syntax near '.'.I am really perplexed: what I am doing wrong?Interestingly, if I re-write the second snippet asDECLARE @i AS intSELECT @i = dbo.MyScalarFunction()...SELECT * FROM MyTableFunction(@i)everything works just fine; however, this trick cannot be used as aworkaround because I need to pass result of one function as a parameterto another inside a view's code -- I cannot declare variables and writeany procedural code...Any ideas, especially workarounds, would be greatly appreciated.Thank you,Yarik.
