i want to modify this table:
employees----address----phone1----phone2----phone 3
----------------------------------------------------------------
John A. keas 19 9515678
John A. keas 19 9515670
Mike B. highway 2 8652332
Mike B. highway 2 8656543
Mike B. highway 2 8723165
like this :
employees----address----phone1----phone2----phone 3
----------------------------------------------------------------
John A. keas 19 9515678 9515670
Mike B. highway 2 8652332 8656543 8723165
_________________________________________________
may seem silly,i know,but i am stuck here days now and i dont know with what to fill my loop.
thank you in advance.
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
Ok this is what I have $query = $query = "SELECT date, file name FROM comment WHERE file name ='', AND CorS='C', AND Approved='Y', ORDER BY file name DESC";
But what I want is to return all results based on the file name from the most recent to the oldest. Can anyone help????
here's a good one for you...I want to return the last 20 records I have modified. I have adatemodified field - excellent.So I run a query to select the top 20 when ordered by datemodifieddesc.But now I have these results I want them sorted by companyname.Is this possible?Yes I could use my GUI to do the second sort, but can it be done justin a query?ThanksTim
I have a gridview that has AllowSorting="true" however I need to implement my own sorting because I have DateTime and Integer data types in several of the columns and I don't want an int column sorted like 1,12,2,23,3,34,4,45,5,56, etc. So, I've added SortParameterName="sortBy" and adjusted my stored procedure to accept this. For only ASC sorting, I've got ORDER BY CASE WHEN @sortBy='' THEN DateCreated END, CASE WHEN @sortBy='DateCreated' THEN DateCreated END and so on. However, columns can also be sorted with DESC. I tried CASE WHEN @sortBy='DateCreated DESC' THEN DateCreated DESC END, but I get a syntax error on DESC. How can I do this?
I am trying to set up custom paging and sorting with my gridview. All is well but the sorting. The problem is with the stored procedure. If I pass in the value @sortExpression as, for example "discussions_Posts.post_time", i does not sort it at all. But if I replace the @sortExpression with discussions_Posts.post_time in the actual stored procedure, it gets sorted. how do I sort this query with a input parameter with values like "discussions_Topics.topic_title" or something? ALTER PROCEDURE discussions_GetTopicsSubSet@startRowIndex as int,@maximumRows as int,@sortExpression as nvarchar(50),@board_id as intASDECLARE @Topics TABLE(RowNumber INT,topic_id INT,topic_title VARCHAR(50),topic_replies INT,topic_views INT,topic_type INT,topic_time DATETIME,post_id int,post_time DATETIME,Topic_Author_UserName nvarchar(256),Topic_Author_ID uniqueidentifier,Post_Author_Username nvarchar(256),Post_Author_ID uniqueidentifier)--DECLARE @TopicsFrom Datetime--SELECT @TopicsFrom = CASE @TopicsDays WHEN '1' THEN DATEADD(day,-1,getdate()) WHEN '2' THEN DATEADD(day,-7,getdate()) WHEN '3' THEN DATEADD(day,-14,getdate()) WHEN '4' THEN DATEADD(month,-1,getdate()) WHEN '5' THEN DATEADD(month,-3,getdate()) WHEN '6' THEN DATEADD(month,-6,getdate()) WHEN '7' THEN DATEADD(year,-1,getdate()) ELSE DATEADD(year,-1,getdate()) END-- populate the table CAST(getdate() as int)INSERT INTO @TopicsSELECT ROW_NUMBER() OVER (ORDER BY @sortExpression), discussions_Topics.topic_id, discussions_Topics.topic_title, discussions_Topics.topic_replies, discussions_Topics.topic_views, discussions_Topics.topic_type,discussions_Topics.topic_time, discussions_Posts.post_id, discussions_Posts.post_time, user_1.UserName AS Topic_Author_Username, user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_IDFROM discussions_Topics INNER JOIN discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_idWHERE (discussions_Topics.board_id = @board_id ANDdiscussions_Topics.topic_type NOT LIKE '1' )SELECT * from @TopicsWHERE RowNumber BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
This is more of a SQL question than a .NET question, but if you could indulge me, I'd appreciate it. I have a table that has 2 columns of particular interest for the purposes of this question. One is a foreign key to another table (int), the other is a name (varchar(50). I want to sort the results set in a specific way. I want to sort it in such a way that all entries that have the foreign key = 0 come first (sorted ASC by name) then I'd like all the other results with foreign key column > 0 to be sorted ASC by name. I was trying to be cute and tried an order by statement like this: "ORDER BY (foreignKey > 0), name" but it's a syntax error (as I initially thought it might be). I know I could probably do a stored procedure that will create a temporary table and I could insert a new column to help put these in order, and I also know I could put all the results into an array, then sort the array in code, but I was just wondering if there was a simpler, slicker way (tricky SQL query perhaps).
Hi, I hope I can explain what I am trying to achieve (in MS SQL Server): The results set that shows Names and Dates (plus others, but not relevant here), that needs to be sorted by Date and then by name. I have tried: ORDER BY date, surname, forename (gives dates in right order but not names) and ORDER BY surname, forename, date (gives names in right order but not dates)
I noticed when looking at the execution path that the SQL sort (order by) was taking up 16% of the total for the query. However the execution time for the sort was like .007 ms in SQL so I doubt it was really that much overhead.
Is it more effecient to sort a dataset within my asp.net program?
i have a simple table with NAME and a SORTORDER. Now i want to sort my table on the NAME desc and then insert a number into the SORTORDER. The number has to correspond with the number the NAME has in the list.
EXAMPLE
Initial situation: BAKKER 0 AKKER 0 CARNIE 0
Now my question is how can i update the table with a sp in wich te outcome is:
I'm trying to sort by domain user id, I can pull the user id fine but now I want to sort my SELECT by that name. How do I put the value of getUserIdentity into my SELECT statment. Thanks <script language="C#" runat="server"> protected string getUserIdentity() { return HttpContext.Current.User.Identity.Name.ToString().Replace("DOMAIN\", ""); } protected void Page_Load(Object sender, EventArgs e) { username = getUserIdentity(); SqlConnection myConnection = new SqlConnection("Data Source=CLIENTELE;Initial Catalog=forms;Integrated Security=True"); SqlDataAdapter myCommand = new SqlDataAdapter("SELECT * FROM formTable WHERE userID = @username ORDER BY status DESC", myConnection); DataSet ds = new DataSet(); myCommand.Fill(ds, "names"); MyDataGrid.DataSource=ds.Tables["names"].DefaultView; MyDataGrid.DataBind(); } </script>
i am trying to figure out how to sort the data in a column in an ascending way when the column contains numeric and char data. In the example I want to sort by category,lookup value. However, as you can see the top two rows are sorted right but the others are all messed up. I would really appreciate any hints you can give me. thanks Rozina
Category ID lookupvalue
CPU Type4CPU Type 1 CPU Type4CPU Type 2 CPU Number51 CPU Number516 CPU Number52 CPU Number532 CPU Number54 CPU Number564 CPU Number58
I have a view of a table which contains users. the SQL is:
CREATE VIEW dbo.vwTravellers AS SELECT TravellerID AS ID, LTRIM(RTRIM(LastName)) + ', ' + LTRIM(RTRIM(FirstName)) + ' - DOB:' + LEFT(RTRIM(DOB), 11) AS Value FROM dbo.tblTravellers
I would like to be able to sort the view by LastName, Firstname, but it doesnt allow a SORT BY in the view. Does anyone know of a cheeky little trick I could use to get around this?
Cheers Guy
I wish I'd listened in that SQl Server training course I attended :(
I am trying to sort a matrix by column. I first tried adding an interactive sort to the column heading, but it either gave me a run-time error, or there was not response. I then tried adding sorting to the row groups, and that doesn't seem to work either. I feel I have done every combination of groupings and sort expressions. Has anyone been able to accomplish this? Thanks, Kris
I'm not sure why this is happening, but I'm trying to sort/order on this special field/column that I have created to allow precise ordering. However, for all of the values that are returned, if there are any NULLS, they are returned first/ahead of the values of what I want to be returned first. This field/column I created is called 'POW' (stands for Priority on Web). The values are just integers, 1-whatever, and we try to make sure that they're all unique. So for example, if we have a 1 in that field, then that row should be returned first, and if another row has 2, then it should be next. However, this is not working exactly - all of the NULL values are returned FIRST, then the proper values in the correct order. I want the NULLS to be last, because it may take some time for someone to go into each record, and assign it a unique value. My current SQL statement reads:
mySQL = "SELECT SignTypes.Id, SignTypes.SignType, SignTypes.SignDescription, SignTypes.FinalSignWidth, SignTypes.FinalSignHeight FROM SignTypes JOIN PropLocSigns ON SignTypes.Id = PropLocSigns.SignTypeID WHERE PropLocSigns.PropLocID = " & Session("PropID") & " AND (RestrictOnWeb = 0 OR RestrictOnWeb IS NULL) ORDER BY SignTypes.POW; SELECT @@ROWCOUNT"
So if someone knows of a way to put NULLS last, and still have my proper ordering, that would be great! I would think that there's some simple way of doing this, but not being an expert, I don't know. Thanks for the help!
hi all..just a quick question about sorting a query. let us say that i am running a select statement that involves 3 or 4 tables and one of the columns in one of the tables is a divisions column. based on the records, there is a specific division that i want to appear first in the list. its not alphabetically possible nor is it the keys associated with it allow it to be selected first. how would i go about pulling that particular record from the divisions column to be listed first in my query? thanks alot
I have an appointment table that stores the appt time as varchar. For example, 1:00 PM or 9:00 AM. When I say "ORDER BY Appointment.Time", it puts 1:00 PM before 9:00 AM. How do I make it put 9:00 before instead? Could you please help me out.
Please note, I store time as varchar because it is much more convenient for me through out the application that way.
i want to modify this table: employees----address----phone1----phone2----phone 3 ---------------------------------------------------------------- John A. keas 19 9515678 John A. keas 19 9515670 Mike B. highway 2 8652332 Mike B. highway 2 8656543 Mike B. highway 2 8723165
like this : employees----address----phone1----phone2----phone 3 ---------------------------------------------------------------- John A. keas 19 9515678 9515670 Mike B. highway 2 8652332 8656543 8723165
_________________________________________________ may seem silly,i know,but i am stuck here days now and i dont know with what to fill my loop. thank you in advance.
I am trying to get data as it is available in the table but every time I run sql statement it shows in asending order. How can I avoid this asending order situation?
I have three tables: one that holds users, on that holds products, and one that holds votes on products (fields are userid, productid, and the vote they made (1-5)). I am trying to come up with a query that orders the product result set by number of votes both descending and ascending for a particular user. If a user has not voted, then there will not be anything in the vote table for that user. I am not even sure where to begin on this as it is way out of my SQL experience.
This is a normal query I use to just pull them out ordered by name.
SELECT PRODUCTS.PRODUCT_ID, PRODUCTS.P_NAME, USERS.U_USERNAME FROM [PRODUCTS] INNER JOIN [USERS] ON USERS.USER_ID = PRODUCTS.USER_ID WHERE STATUS_ID = 1 ORDER BY PRODUCTS.P_NAME DESC
Can anyone help or point me in the right direction?
