How To Get Top 2 Rows For All Composite Key Combinations?
Jul 9, 2006
The requirement in to write a query which will return top 2 rows (in terms of lst_updt_timestamp column) for every combinations of cust_alias_nm, carrier_cd, acct_nbr columns.
Here I wrote a query which selects top 1 only.
Please help me to write to get the top 2nd along with the top 1st row.
select A.cust_alias_nm_id,
A.carrier_cd_id, A.acct_nbr_id,
sum(A.pd_clm_amt) clm_amt,
sum(A.pd_med_amt) med_amt,
sum(A.pd_exp_amt) exp_amt,
A.lst_updt_timestamp
from bal_load_stg A
group by A.cust_alias_nm_id, A.carrier_cd_id, A.acct_nbr_id, A.lst_updt_timestamp
having A.lst_updt_timestamp
in (
(select max(lst_updt_timestamp) from bal_load_stg B
where
A.cust_alias_nm_id = B.cust_alias_nm_id
and A.carrier_cd_id = B.carrier_cd_id
and A.acct_nbr_id = B.acct_nbr_id))
I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.
The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold
1. multiple columns need to be used for joins and I think this might degrade performance? 2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.
A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.
I would like to have some tips, recommendations and alternatives for what I should do in this case.
I was using the code in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1371094&SiteID=1) to create a console application which can build the SSIS package dynamically and run the package.
I'm not clear on what all could be the possible key / value combinations for the SetComponentProperty Method. From the examples I have seen its either SqlCommand or OpenRowSet. But I'm not sure about the "AccessMode" with values either 0 or 2. Is there any reference where I get more information on these?
Code Block // Set the custom properties of the source. srcDesignTime.SetComponentProperty("AccessMode", 2); srcDesignTime.SetComponentProperty("SqlCommand", "Select * from devdb..empl_karun");
And I want to end with a table that has every unique combination of these records (the order being reversed would be considered 'unique' in this context)
I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type): ****************************************************************** SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1" WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999')) GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"
********************************************************************** Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)
How would I write a query to produce all combinations for a situation such as the following?
Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.
Adjective table looks like e.g.
ID Adj 1 good 2 so so 3 bad
Noun table looks like e.g.
ID Noun 1 apple 2 orange 3 banana
And the result set would look like
This is good apple. This is so so apple. This is bad apple. This is good orange. This is so so orange. This is bad orange. This is good banana. This is so so banana. This is bad banana.
I would take a stab at this myself and post even something that doesn't work...
We need to store land title information about properties in variousAustralian states, but each state maintains it's own land titleregistry and use different columns (well actually differentcombinations of the same columns). For example:Victoria store:TorrensUnitTorrensVolumeTorrensFolioQueensland store:TorrensCountyTorrensLotTorrensPlanTorrensParishTorrensUnitTorrensVolumeTorrensTitleRefThere are 11 different columns and they are used in 8 differentcombinations depending on the state.Since we need to store information about land in different states I seetwo possible solutions:1. A sparse table containing the 11 columns with a CHECK constraint toenforce the valid combinations.2. A table for each state containing only the columns relevant to thestate with a foreign key relationship to the table containing thecommon columns.I'm not sure if the data type and length is consistent between statesyet (waiting to find this out) but assuming that it is which of theseapproaches is going to be the most rigorous? I'm leaning towards (2)but I don't like the feel of a table per state.
Hello all, I have an odd requirement. I have a column with a systemgenerated username that is a 6 character, alphanumeric, field. Theseusernames are randomly generated by code. I need to create a storedprocedure that will return all combinations that are not alreadybeingused. Maybe the result of still trying to wake-up from a longweekend.But, I cannot think of an easy way to do this.Any help would be greatly appreciated.Best regards,rbr
I need to populate a table which have 10 columns with four values. Each row should be a different combination of these four values and the columns can be null too. In other words how can I get all the different combinations for the 4 values that can be in 10 buckets. The final result column based on these values will be generated manually.
For example , I have for grades (P,F, WP, WF) and I have 8 terms and two exams. 8 terms and two exams can have any of the above four values. Based on these grades and terms and exams I need to generate a table which wil be used to determine the student final status Pass/Fail.
What will be the best way to do this and how is it possible. Is there a T-SQL or C# program for this.
If I need to submit this in another forum please let me know.
I'm just now learning both SQL and ASP.NET, and I cannot seem to figure out how to build my data structure. I believe the answer to my problem is a composite key, but I cannot seem to get it to work. Here is an example. My database is of recorded dances, with exact locations within a ballroom. I believe I need 2 tablesTable #1 - DanceTableColumns: DanceID, Name, Description, TagsTable #2 - StepsTableColumns DanceID, StepID, longLocation, latLocation, Action, DescriptionWithin my ASP.NET application I want to be able to enter data about a dance, including metadata and a series of steps. The Dance and metadata content to be stored in DanceTable, and the series of moves stored in the StepsTable. I want the steps to be IDed as 1, 2, 3, 4...x with the first step being labled 1. and I want each dance to have it's own unique ID (DanceID). Right now I'm using "ExecuteNonQuery()" to add my data to my SQL database, and when I add new steps to the StepsTable SQL just finds the largest ID within StepID and increments it by one. So my steps are labeled as:Dance1:Step1, Step2, Step3, Step4Dance2:Step5, Step 6, Step7What I really want is (or I think what I want is) is a composite primary key.Dance1:Step1, Step2, Step3, Step4Dance2:Step1, Step2, Step3That way the StepID is used as both a primary key and it indicates the position within the dance. I guess I could just use a standard SQL table, let SQL auto generate StepID's and then add a new column called something like "StepNumber", but it just seems goofy to be generating a stepID and never using it. With composite keys (If I understand them) each step would have a unique key as a combination of the DanceID+StepID (AKA Dance 345, steps 1-10). I pull up data by searching for dances, and then sort by StepNumber, and those should all be unique...if I can figure out how to build them.
I have a table with a composite key formed by the unique combination of columns w, x, y, z I'm trying to write an INSERT statement along the following lines INSERT INTO myTable (SELECT w, x, y, z FROM someTable) t1 WHERE (this is the part I'm stumped on - where the unique combination of w, x, y, z is NOT in myTable already) Help would be appreciated. Can you use the NOT IN keyword on composite values?
How can I create a composite key (two primary keys for one table) using SQL Server 2005 Management Studio? Can I do it using the GUI, or do I have to write SQL code to do this? Thanks
Made an interesting discovery today - a column of data type Bit is not allowed to participate in composite key. Rather surprised.
I have a certain unique code that exhibits 2 unique states, which rendered the use of the boolean column, so the uniqueness goes <code>-1 and <code>-0. Is there any 'hack' so to speak to avoid using a Tinyint for the boolean column instead?
Suppose there is a composite index on a table, and in includes, for example 3 columns.
If I do a select using one of those three columns, will the select use the composite index? Will it aid in retrevial or should I create an index on the individual column.
The orininal design of my db (part of it...) is the following
A JOB has a Number and a Description. Each JOB can have one or two TASKS (min one, max two). Each TASK is identified by the JOB it belongs to and an Index (unique only for the same JOB). Each TASK has one an only one set of INFO1, one and only one set of INFO2, one and only one set of INFO3 etc.
(There is a reason to keep INFO1, 2 and 3 separate, because eachof them will be linked to different table. This might influence the answer to my real question.)
First of all, I wouldn't add any surrogate key for TASK, not to loose the logic behind; plus I'd put an ined on JonMum only, being Index equal to 1 or 2 only, so not selective.
The real question is about INFO1 (and 2, 3 etc.) table: should I leave JobNum and Index as PK (consider that the PK of INFo1 will be used as FK for another table), or should I use a surrogate key, like for eaxmple
C: INFO1 (Info1ID [PK], JobNum [FKb], Index [FKb], ...)
I don't really like this solution. Actually I'd prefer the following
C: INFO1 (Info1ID [PK], ...)
where Info1ID = JobNum + Index (+ = string concatenation).
I've a gerand table customers_orders table with customer_id and order_id. Whenever we have to find orders, for customer, this table is involved. Hey; i know u'll be angry y the heck this gerand exist but i've to blame the older dudes then. Now this table has composite clustered index; CUSTOMER_ID+ORDER_ID. The tables have grown over GB size; i see HASH INNER JOIN rather than MERGE for the GEREND and CUSTOMER table join.
Is it good to use composite clustered index; or should i clustered one the columns in the GEREND and other to normal index. What performance impact it could be.
ResID is PK, AdmID and SubID are Foreign Keys, right. Now What i want to ask/ learn that Am i suppose to combine ResID, AdmID and SubID as a composite PK or should i simply make the ResID as PK and other 2 be there as FK? When do we really need to use Composite PK and using it a good thing or not?
i have a master table with around 15 columns and i am trying to findthe appropriate primary keys and indexes for the table.To make the records unique, i need to include two datetime columns (start and end dates ) and two integer columns ( attributes of therecord ) to make up a composite primary key. Both of these fourcolumns are found in the WHERE clause of my queries.Is it acceptable in the view of performance and how should i createthe indexes?
This is really not a T_SQL question but there's no good category for it so I thought I'd just put it here.
I have a table that will contain a large amoutn of data in one field, and every piece of this data is uniquely identitied by six other attributes, that is, six other fields (e.g. user_ID, type_ID, year, country, state, item_ID). I can either make these six fields into a composite primary key, or add an additonal field (say an identity column) as the primary key and add a unique constraint on these six fields. What are the pros and cons of both designs? The one data field is of nvarchar(2000) type and the table is likely to have 50 million+ rows in a couple of years of real use.
This table is not referenced by any other tables so whatever the primary key is, there's no FK reference. However, there could be FK references to this table in the future. Does the FK possibility make a difference in the design considerations?
The table above is my users table. It allows for a user to be at multiple sites or multiple locations within a single site or multiple sites. Would it be wise to use a auto incrementing primary key instead of the 3 column composite key? The reason I ask is because if I am referencing this SU table (which I will be a lot), a lot more data would be replicated to the tables which have the foreign key to this table, right? But if I used a single incrementing column as the primary key, only a small integer would be used as the foreign key, saving space?Does this make sense?
Hi All, Can anyone tell me how to create a reference for composite key. For ex, I have created tblEmp table successfully. create tblEmp( empId varchar(100), RegId varchar(100), empname varchar(100),constraint pk_addprimary key(empId, RegId) ) And now, I am going to create another table which references the composite key.create table tblAccount( acctId varchar(100) primary key, empId varchar(100) references tblEmp(empId), RegId varchar(100) references tblEmp(RegId) ) But it gives error like Server: Msg 1776, Level 16, State 1, Line 1There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors. Could anyone please let me know how to create reference for composite key. Thanks in advance,Arun.
Hi What is the difference between Candidate key , composite key and alternate key. I went through many websites but I didn't get examples. There were only definitions. Can anyone please tell me the site or blogs that elaborate this concept RegardsKaran
I'm trying to create a composite Primary Key on a table. This is the SQL I've written:
CREATE TABLE BookingItems ( BookingID INT NOT NULL REFERENCES Bookings(BookingID), EquipmentTypeID INT NOT NULL REFERENCES EquipmentType(EquipmentTypeID), CONSTRAINT PK_BookingItems_id PRIMARY KEY (BookingID, EquipmentTypeID) )
Is this right? I'm trying to define a Primary Key made up of BookingID and EquipmentTypesID, which are both Foreign Keys as defined in the column definition.
