akanksha shukla writes "the issue is that i want to sort the records stored in a table according to aparticular column in such a manner that the sorted reaults are permanantly commited onto the same table i.e. the results are reflected on the same table whose records are being used for sorting
i know that order by clause can provide me a view of records sorted on basis of a particular column but the changes are not being reflected onto the table .
i donot want to create a fresh table to reflect the changes.
kindly suggest some help"
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1) and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
I've a problem with the following stored procedure Select . It does compile and run, but doesn't return the sorted result I wanted, which was to have the records from tblPieces (alias Pcs) sorted by (in order) Pcs.fkBatchId, Pcs.fkProfileCode, Pcs.Colour. What happens instead, I think, is that the correct records are selected, but in the record creation order. CREATE PROCEDURE dbo.LoadOneBatch ( @BatchId int, -- the pkBatchId of the batch required @OnlyNew bit -- If true, only consider batches that haven't ever been loaded. ) AS SET NOCOUNT OFF SELECT Bat.pkBatchId, Pcs.* FROM tblBatches AS Bat JOIN ( SELECT Bat1.pkBatchId, Pcs1.fkProfileCode, Pcs1.Colour FROM tblBatches AS Bat1 JOIN tblPieces AS Pcs1 ON Pcs1.fkBatchId = Bat1.pkBatchId WHERE Bat1.pkBatchId = @BatchId GROUP BY Bat1.pkBatchId, Pcs1.fkProfileCode, Pcs1.Colour ) SubQ ON SubQ.pkBatchId = Bat.pkBatchId JOIN tblPieces AS Pcs ON Pcs.fkBatchId = Bat.pkBatchId WHERE (@OnlyNew = 1 And Bat.IsLoaded = 0 And Bat.IsCompleted = 0 And Bat.pkBatchId = @BatchId) Or (@OnlyNew = 0 And Bat.pkBatchId = @BatchId) GO EXEC LoadOneBatch @BatchId = 1, @OnlyNew = 0
The DDL for the two tables is:CREATE TABLE [tblBatches]( [Stamp] timestamp NOT NULL, [pkBatchId] int IDENTITY(1,1) NOT NULL, [BatchNo] varchar(50) NULL, [SubmitTime] [datetime] NULL, [FinishTime] [datetime] NULL, [IsLoaded] bit NULL, [IsCompleted] bit NULL, PRIMARY KEY ( [pkBatchId] ASC ) ) CREATE TABLE [tblPieces]( [Stamp] timestamp NOT NULL, [pkPieceId] int IDENTITY(1,1) NOT NULL, [fkBarId] int NULL, [fkBatchId] int NULL, [PieceNo] varchar(12) NOT NULL, [Descrip] varchar(48) NULL, [Position] real NULL, [LeadPrep] char(1) NOT NULL, [TailPrep] char(1) NOT NULL, [Length] real NOT NULL, [fkProfileCode] varchar(10) NOT NULL, [Colour] varchar(5) NOT NULL, PRIMARY KEY ( [pkPieceId] ASC ) )
The data records output are roughly:pkBatchId Stamp pkPieceIdfkBarIdfkBatchIdPieceNo Descrip PositionLeadPrepTailPrepLengthfkProfileCodeColour 1 0x00000000000036B21 NULL1 00000000000118960 /003/F>1 N2NULL / 913 6000 wht 1 0x00000000000036B32 NULL1 00000000000218960 /003/F<1 N2NULL / 913 6000 wht
I need to select unique records from a Table. I'm using Distinct Keyword for this purpose. But the result set is showing distinct records in sorted order. I do'nt want to sort records. I need the order in which they are created in table.
INSERT INTO #Activities VALUES('Field phase S14-04932-01') INSERT INTO #Activities VALUES('Phase reporting') INSERT INTO #Activities VALUES('Phase running') INSERT INTO #Activities VALUES('RD1')
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
Hi, i have a table with all employee bio-data in a completed project. Iam now working on another project with a table that needs the same data and here iam talking about 300 records that rarely change. Instead of re-entering this data in this new table, i want to import the data from the completed project into a table in this new project. Does any one have any idea how to achieve that or is there a better option to do the same. One more thing iam realising here is that iam going to use this same data in very many applications and some one from one department is going to enter this data in all these different applications. so i was wondering if there could be a way of having a central database that this guy can mantain and then i be able to use that table data in different applications that iam going to develop. I dont want to kill this unlucky guy with data entry tasks every time i deploy a new application. So basically, how can make one database application sever several different applications with its data.
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT Student.StudentSurname + ', ' + Student.StudentForename AS Fullname, HomeworkLogLine.HomeworkLogLineTimestamp, HomeworkLog.HomeworkLogDescription, ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
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.
I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores' storeid doc article storeid doc article ALL 0010 001 101 0010 001 ALL 0010 002 101 0010 002 ALL 0011 001 102 0011 002 ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001".
Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
I have a scenario where I have to Update a table with date when there are new records in another table
For example:
I load ODS table with the data from a file in SSIS. the file has CustomerID and other columns.
Now, when there is new record for any customerID in Ods, then Update the dbo table with the most recent record for every CustomerID(i.e. update the date column in dbo for that customerID). Also Include an Identifier that relates back to the ODS table. How do I do this?
USE [Testing] GO /****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
Hi friends,ID LASTNAME--------+----------------------------2FFF1XXX0CCC1DDD2BBB0EEE0GGG3III3HHH4ZZZ4ZZZplease help me in sorting this table. the table should be sorted basedon LASTNAME, and then the ID (only non-zero id should be sorted).ID LASTNAME--------+----------------------------2BBB2FFF0CCC1DDD1XXX0EEE0GGG3HHH3III4XYZ4ZZZshould be sorted based on last name and should be grouped with thesame ID, except for the ID as 0
Hi Friends,How can we insert records in sorted orderlike consider a tableNo Name Phone1 test1123451 test1234552 test2686383 test3676844 test4548084 test474594if i add a new record like this2 test234454it should go in specific order. so that the final table should looksomething like thisNo Name Phone1 test1123451 test1234552 test2344542 test2686383 test3676844 test4548084 test474594
I am using the default ASPNETDB.MDF database for a project in SSE 2005 and VWD 2005. I need to use the UserName field from the aspnet_Users table as a foreign key in another table I have in the database. I am doing this so that I can grab data generated from additional fields that I'm adding to the membership registration wizard. However, I am obviously missing some steps since the user name doesn't show up in my second table. What should I do besides creating a relationship between the two fields and tables? Should I be writing some sort of SQL statement to accomplish this?
Hi, I have a table whose Identifying column is not an integer but rather a manually entered id. (i.e. 106F, 106-09, G11 etc.) When sorted ascending, a G11 will come before a G2 in the list, 106-11 before 106-2, etc.I would like to insert a new column in the database or use some kind of function when sorting the database to ensure that the list in returned in the proper order. Any ideas?Much appreciated...
I am a little new at T-SQL. and I could use a suggestion on the best way to accomplish this task. I have an application where I must loop through a table in Microsoft SQL Server 2005, find the records that match and break them up into groups where the value of the Thickness field is in groups of ( <=8).
Lets say I have 100 records and the table contains sacks marked A, B, C, and D. Within Sack A is 6 records. Each of the 6 records has a thickness column with a value between 0.5 to 1.0 (my example shows 2) I have a column called bundle that I must update to show a 1 for the first group of (<=8) inches thick a 2 for the second group (<=8) inches thick and so on. Then repeat the process for sacks B, C and D.
What I need to do is sum the thickness until it gets to less than 8 and update bundle with a 1. The result would be 4 records and the table would then look like this:
Notice there are two records that are not bundles for sack A. I need to place them in a separate bundle called 2. The result will be as follows Order Number |Zipcode | Sack(A, B, C,or D)| Thickness |Bundle| 1 |19809 |A | 2 |1 | 2 |19809 |A | 2 |1 3 |19809 |A | 2 |1 4 |19809 |A | 2 |1 5 |19809 |A | 2 |2 6 |19809 |A | 2 |2 | 7 |19721 |B | 4 |NULL| 8 |19721 |B | 3 |NULL 9 |19721 |B | 2 |NULL 10 |19721 |B | 5 |NULL | 11 |19721 |B | 2 |NULL | 12 |19721 |B | 2 |NULL |
Now Sack A is complete so I must move on to Sack B. The first two records total to 7 and adding another record would move the thickness past 8 so I must update the bundle for only the first two records with a 1. The next two total to seven also and the following 2 total to four. We must set those bundles to 1, 2, 3 as follows
I have a table which visibility can be toggled by a text box. By default it is invisible. After it is made visible, clicking a sortable column header makes the table invisible. Does this mean sorting makes the table go back to its default visibility?
I have a field (varchar) in a list that contains numbers and letters. I want to sort this table but I have only two functions that will work to convert the values:
Val The Val function sorts the numbers in the string, but the letters are not sorted
CStr The CStr function sorts the letters, but the numbers are not sorted
As I said above, how do I put sorting + paging in a stored procedure.My database has approximately 50000 records, and obviously I can't SELECT all of them and let GridView / DataView do the work, right? Or else it would use to much resources per one request.So I intend to use sorting + paging at the database level. It's going to be either hardcode SQL or stored procedures.If it's hardcode SQL, I can just change SQL statement each time the parameters (startRecord, maxRecords, sortColumns) change.But I don't know what to do in stored procedure to get the same result. I know how to implement paging in stored procedure (ROW_NUMBER) but I don't know how to change ORDER BY clause at runtime in the stored procedure.Thanks in advance.PS. In case "ask_Scotty", who replied in my previous post, http://forums.asp.net/thread/1696818.aspx, is reading this, please look at my reply on your answer in the last post. Thank you.
I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
Hi, all I got one stupid question in mind. The idea is that I want my SQL database to install into another computer. The best way for me is I want to create a script file for creating tables, triggers, stored procedure. But I want all the SQL statement that insert all the records in to each table too.
Does anyone have a solution for me? Or there be other way? I am always waiting for ur suggestion.
I have a two tables containing customers invoices, one for the invoices header (ie: customer #, invoice date,... KEY: invoice # + invoice date) and another for the details of the invoices (ie: each invoice line details KEY: invoice # + line #). I need to periodically remove invoices older than a certain timeframe (ex: all invoices older than 48 months).
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank FROM RPL ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
I'm trying to delete some records from some tables in a SQL Server 2008 R2 database. There's a foreign key relationship between the two tables. To make things easier here's the definition of both tables:
-- Parent table CREATE TABLE [dbo].[PharmInvInItemPackages]( [InventoryInDetailID] [int] IDENTITY(1,1) NOT NULL, [InventoryInID] [int] NOT NULL, [ItemPackageID] [int] NOT NULL,
I have one table with 300,000 records and 30 columns.For example columns are ID, COMPANY, PhONE, NOTES ...ID - nvarchar lenth-9COMPANY - nvarchar lenth-30NOTES - nvarchar length-250Select * from databasewhere NOTES like '%something%'Is there a way to get results from this query in less then 1-2 secondand how?
