Sorting Based On Foreign Key Feilds
Dec 1, 2006
shibu writes "I have a table 'manage_user_types' and its primary key is 'Category_ID'. There is another field 'Category_Parent_ID' which refers the 'Category_ID' in the same table. And one more field is 'Category_Name' which is related to 'Category_ID'. ie.when displaying the table in front end, 'Category_Name' is displayed under 'Category_Parent_ID'. I want to sort the table with 'Category_Name' when clicking the 'Category_Parent_ID' to sort. What should i do?"
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
I've been tasked with designing a database to store collectiveagreements. I need to store each sub-article of each article of eachagreement in it's own row within a table. I'm having difficultyfiguring out the best way to store each sub-article's identifier sothey sort properly.The majority of the collective agreements use numbers in a dottednotation, for example:1.11.21.3.11.3.21.3.31.4Some agreements mix letters with numbers, such as:A-1.11.1.A.1Any ideas how I should deal with this?
View 4 Replies
View Related
Feb 25, 2014
I need to sort based on the values sent from the input xml column resultdata
This is an value which i have in my database, sent from front end.
Currently the display is based on database storage level of master data. i will not be able to change the master data ordering. based on selection, i have to sort since i have an logic which stored the data into resultdata column
CREATE TABLE [dbo].[Table_Prod](
[ProdCode] [nchar](5) NULL,
[ProductName] [nchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Table_Prod] ([ProdCode], [ProductName]) VALUES (N'RD ', N'Bus ')
[Code] ....
Current Result
IdSalesMonthSeasonDeptDataProductInfoProdDataProductInfoDescription
1Jan Winter RO,HOHeadOffice,RegionalOfficeRD,AI,SA Bus,Ship,PlaneSales for the month of Jan
2May Summer SO,HOHeadOffice,SalesOfficeAI,RD,TR,SA Bus,Ship,Train,PlaneMay sales information
3Sep Rain HO,RO,SOHeadOffice,SalesOffice,RegionalOfficeSA,TR,RD Bus,Ship,TrainSales for the month of Sep in the rain season
Expected Result
IdSalesMonthSeasonDeptDataProductInfoProdDataProductInfoDescription
1Jan Winter RO,HORegionalOffice, HeadOfficeRD,AI,SA Bus,Plane,ShipSales for the month of Jan
2May Summer SO,HOSalesOffice, HeadOfficeAI,RD,TR,SA Plane,Bus,Train,ShipMay sales information
3Sep Rain HO,RO,SOHeadOffice,RegionalOffice,SalesOfficeSA,TR,RD Ship,Train,BusSales for the month of Sep in the rain season
View 1 Replies
View Related
Aug 24, 2007
Hi All,
Is there any command which will sort data in text file based on 3rd column (based on any column)?
Thanks
Sanjeev
View 1 Replies
View Related
Sep 21, 2007
Hi,
I have created a report using Report designer (Visual Studio, using Data Model as a data source), in the report I had created few datasets (with single filed) to populate the report parameters, lets say I have created a multi valued Parameter CustomerName and assigned field from a dataset,
result are coming correctly and combo box is getting populated but the customers are not in alphabetical order!
I want to sort it and need to specify it in report (please note that I am using Report Model as a data source and I can€™t sort the source table in the data base to get the result sorted)
Please let me know if anybody has done that or forward me if know some link which talks about it.
Thanks in advance.
Regards,
Jayant Jape
View 23 Replies
View Related
Mar 14, 2008
My SQL is very basic. How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db?
SELECT COUNT(*) AS Expr1, tblArticle.ArticleIDFROM tblArticle INNER JOIN tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID
This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.
View 3 Replies
View Related
Nov 5, 2007
Guys,
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table
My truncate script should be
TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE
IS there any automated way to figure out parent and child tables and generate truncate script for the same.
Thanks
View 3 Replies
View Related
Aug 2, 2007
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit
Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007
A 100 200 300 20 25 15
B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit
2)Sort report by lastMonth of Sale (here 7/1/2007) High to low
3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
thanks
View 1 Replies
View Related
Nov 5, 2007
Guys,
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table
My truncate script should be
TRUNCATE TABLE DEPARTMENTDETAIL
TRUNCATE TABLE EMPLOYEEDETAIL
TRUNCATE TABLE DEPARTMENT
TRUNCATE TABLE EMPLOYEE
Is there any automated way to figure out parent and child tables and generate truncate script for the same.
Thanks
View 1 Replies
View Related
Apr 28, 2004
hi all
having worked with access in the past and getting stumped by the fact a table could only hold 256 feilds....... does sql server have any such limit????
thanks
cq
View 7 Replies
View Related
Oct 22, 2004
Hi there!
There is a feild in my table that contains the city, state and zip, all in the same feild. I was wondering how would be the best way to devide all that info up into 3 feilds? i really don't know how I would go about it since there are cities that can be comosed of 2 or more words. Here is some sample data. I hope someone can help.
SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210
IRWINDALE CA91706048
PORTLAND OR97202901
PORTLAND OR972028901
ANAHEIM CA928071735
KENT WA98032
CRYSTAL LAKE IL60014611
View 2 Replies
View Related
Apr 18, 2007
Hi,
I am new to sql and is working with sql server managment 2005 +c# 2005.
My application needs to create a blockdiagram sort of thing say
if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.
there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly.
My problem starts in the front end where i need to draw the graphical representation of table addition.
In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.
I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)
How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.
Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.
Priyadarshini
View 1 Replies
View Related
Dec 3, 2004
Hello Group
I am having a little trouble with a search. I have 12 checkbox controls and 12 textbox controls. I want to be able to choose which textbox to query from. Most of the time the search may be from one or a couple of textboxes. My problem is I can get the SQL String right. Below is what I have for a string. I know I could write an SQL string for every possible combination and put in all 144 in a SELECT CASE, but I would think there is an easier way to do this. Is there anything that would let me build a SQL string depending what checkbox are checked true?
Dim SQL_SL As String = "SELECT * FROM BuildZone" & _
" WHERE Parcel = '" & strPercelS & "'" & _
" ORDER BY Parcel Asc" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldNameLast = '" & strNameLastS & "'" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldDateMonth = " & intDateYearS & "" & _
" AND fldDateYear = " & intDateYearS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldNameLast = '" & strNameLastS & "'" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldPermitNum = " & intPermitS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldStructureType = " & intStructureTypeS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldConstructionCost >= " & intCCost1S & "" & _
" AND fldConstructionCost <= " & intCCost2S & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldRange = " & intRangeS & "" & _
" AND fldTownship = " & intTownshipS & "" & _
" AND fldSection = " & intSectionS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldZoningDistricts = " & intZoneS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldTwsp = " & intTwspS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldRWD = " & intRWDS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldWW = " & intWWS & "" & _
" UNION" & _
" SQL_SL As String = SELECT * FROM BuildZone" & _
" WHERE fldAccessApplic = " & intAccessAppS & ""
Thanks
View 2 Replies
View Related
Jul 23, 2005
I was curious...Is there a way to select distinct on a combination of some fields andthe for each record returned also get the other fields of anarbitrarily chosen record matching the fields in the distinct record.For example, if I have a select distinct on say three fields:SELECT DISTINCT Code1, Code2, Code3but the table also has other fields, maybe Foo1 and Foo2, and I wantFoo1 and Foo2 to also be displayed. Since there may be multiplerecords that match a particular Code1, Code2, Code3, then I just wantone of those to be arbitrarily chosen.
View 3 Replies
View Related
Feb 11, 2007
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.
Any suggestions would help.
Thanks
View 1 Replies
View Related
Jul 10, 2015
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.
View 6 Replies
View Related
Jul 23, 2005
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View 26 Replies
View Related
Oct 7, 2006
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
View 1 Replies
View Related
Apr 10, 2006
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????
View 7 Replies
View Related
Mar 27, 2008
hi,
i need a query based on sorting.
i have date column and time as varchar in another column and other order details
datecol timcol ordername type
12/31/2007 1:00 pm order1 1
12/31/2007 5:00 pm order2 1
12/22/2007 6:00 pm order3 1
so i need to get query o/p as
12/22/2007 6:00pm
12/31/2007 1:00 pm
12/31/2007 5:00pm
based on date and time. pls help!
View 6 Replies
View Related
Jun 27, 2006
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
View 3 Replies
View Related
Oct 11, 2006
I have a text field in the form of numbers separated by periods like this: 1,1.2,1.2.1
When I sort, it works fine unless I get out to 10 digits.
Desired sort: 1,1.1,1.1.1,1.1.2,.....,1.1.9,1.1.10
Actual sort: 1,1.1,1.1.1,1.1.10,1.1.2,.....,1.1.9
The 10 is showing up smaller than the 2 because of the straight text comparison, any ideas on how to get around this?
View 6 Replies
View Related
Aug 17, 2006
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?
View 2 Replies
View Related
Jun 25, 2007
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
View 1 Replies
View Related
Dec 13, 2007
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).
View 2 Replies
View Related
Jan 7, 2008
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)
Thanks for any advice!
View 2 Replies
View Related
Feb 26, 2004
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?
View 5 Replies
View Related
Aug 2, 2004
Hi,
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:
AKKER 1
BAKKER 2
CARNIE 3
Can someone please help me?
Greetings,
Mark
View 3 Replies
View Related
Aug 11, 2004
i have a question that is there any methods to order by the column which's datatype is ntext with t-sql?
View 1 Replies
View Related
Apr 3, 2006
Anybody know how to accomplish lexigraphical sorting? Any how-tos, docs, tuts or if you just want to paste code
THanks!
View 2 Replies
View Related
May 24, 2006
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>
View 1 Replies
View Related
Sep 24, 2001
hi,
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
View 1 Replies
View Related
Jul 7, 2000
I have a table with a composite primary key (ie a,b, autoInteger)
that includes an auto-incrementing system generated integer.
How do I sort out the records such that if a and b above repeat, I only
get the records with the highest autoInteger?
Thanks for any suggestions
Shelly
View 1 Replies
View Related
