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))


The output looks like:
cust_alias_nm_id carrier_cd_id acct_nbr_id clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000


The desired output should look like:

cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000


All rows present in the table are
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 100 1234567890 400.00 400.00 400.00 2005-05-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000





Thanks in advance.

View 3 Replies


ADVERTISEMENT

Composite Primary Keys Versus Composite Unique Indexes

Feb 20, 2007

Hello,

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.

View 1 Replies View Related

Combinations

Jan 14, 2008

Hi,

I have a table with 2 columns (S.No,Name) with rows
1,Raja
2,Ramu
3,Rane

I need to generate all 2 pair combinations of the names(i,e)

(Raja -Ramu)
(Raja -Rane)
(Ramu -Rane)

Can some one give me a generalized query for this problem?

Thanks,

Prakash.P

View 4 Replies View Related

SetComponentProperty - Possible Key / Value Combinations

Oct 12, 2007

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");



Thanks

View 5 Replies View Related

Selecting All Unique Combinations

Jul 31, 2004

I want to start with a table that has 4 records:

-Self
-Supervisor
-Peer
-Direct Rep

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)

-Self , Supervisor
-Supervisor , Self
-Self , Peer
-Peer , Self
-Self , Direct Rep
-Direct Rep , Self
-Peer , Direct Rep
-Direct Rep, Peer

How would I do this in an SQL Query? Thanks for your help!

View 1 Replies View Related

Summing Different Combinations Of Selections

May 7, 2008

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)

Thanks in advance
I.Shaame

View 9 Replies View Related

Produce All Combinations For A Situation

Sep 6, 2013

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...

View 1 Replies View Related

Count Of Unique Combinations

Oct 26, 2007

Let's say I have a table MyTable with two colums, One and Two. If I wanted to return unique combinations of these two fields I can do

select distict One, Two from MyTable

How do I return the count of such unique combinations? The following of course does not work but you get the idea.

select count(distinct One, Two) from MyTable

Thanks.

View 6 Replies View Related

Finding All Unique Combinations Of Values?

Mar 29, 2013

how to find all possible combinations of values, for example:

My table includes:

Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);

I need the output to be like this:

apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry

I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.

View 5 Replies View Related

Normalization Question Regarding Column Combinations

Jul 23, 2005

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.

View 4 Replies View Related

Need A Routine For Making All Combinations With Given Characters

Jan 11, 2008

I need a function or a routine in sql for making all possible combinations of strings with given letters.

means if I give a string 'ab' the function should return

'a,b,ab,ba' if we give 'abc' it should return 'a,b,c,ab,ac,ba,bc,ca,cb,abc,acb,bac,bca,cab,cba'.

return data can be a single list or the list elements can be printed to screen one by one.

I need to use this routine for inserting bulk sample values for a few tables.
Also have limited time to make one by myself.

Please help.

View 4 Replies View Related

Selecting Alphanumeric Combinations That Do Not Exist Already Intable?????

Mar 24, 2008

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

View 4 Replies View Related

Transact SQL :: Query To Get List Of Permutation And Combinations

Aug 27, 2015

Below query:

Declare @table table
(
consumerID varchar(10),
customerNumber varchar(10)
)

Insert into @table
Select 1,123
union all

[Code] ...

--Expected output concatenation of consumer numbers all permutation and combinations having same

customernumber
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7

View 4 Replies View Related

Algorithm To Populate A Table With Finite Value Combinations

Jul 2, 2007

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.

View 3 Replies View Related

Advanced Select Based On Multiple Field Combinations

May 31, 2006

I have developed an ASP.NET form with 12 different fields that will allow end users the cability to query 3 tables that are relational to one another.

I was curious what is the best way to perform this in a Stored Procedure?

Can I use a UNION -- Not sure if this is the best choice

or account for evey kind of WHERE clause based off IF statements on the data that is passed through the parameters?

View 3 Replies View Related

Composite Key?

Sep 23, 2006

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.

View 1 Replies View Related

Composite Key NOT IN Query?

Nov 9, 2006

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?

View 2 Replies View Related

Add Composite Key Thru SQL Server GUI?

May 29, 2008

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

View 7 Replies View Related

Composite Key Cannot Allow Bit Column

Apr 19, 2004

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?

View 5 Replies View Related

Use Of Composite Indexes

Jun 10, 2005

Hi,

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.

any thoughts on this would be appreciated.



thanks

View 2 Replies View Related

Creating A Composite Key

Sep 24, 2004

Can someone help me create a composite key on ms sql server through the enterprise manager?

Thanks,
Laura

View 1 Replies View Related

Surrogate Or Composite Key?

Aug 21, 2004

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.

A: JOB (JobNum [PK], JobDescription, ...)
B: TASK (JobNum [PK] [FKa], Index [PK], TaskDescription, ...)
C: INFO1 (JobNum [PK] [FKb], Index [PK] [FKb], ...)
D: INFO2 (JobNum [PK] [FKb], Index [PK] [FKb], ...)

(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).

Any suggestion?
Thanks

View 3 Replies View Related

Really Need Help In Composite Index

Feb 11, 2005

Hi folks, i need an advise.

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.


Howdy!

View 2 Replies View Related

Composite Primary Key

Mar 2, 2004

WHile designing a Database should one go for composite Primary Keys.
Or what are the Pros and Corns of Composite Primary Keys

Thanx

View 5 Replies View Related

How To Define Composite Key?

May 11, 2004

Hello, everyone:

I need to define composite PK and FK for a ERD. Could someone offer the methods that work with,
1. T-SQL
2. ERD

Thanks a lot.

ZYT

View 4 Replies View Related

When To Use Composite Primary Key

Feb 24, 2014

I've been facing this situation since long but today i am asking here. Suppose i have a following tables;

AdmissionInfo, AdmID, AdmDate, AdmFee etc.

SubjectInfo i.e. SubID, SubName, SubStatus etc.

The Result table is like this:

ResID, AdmID, SubID, TheoryMarks, PracticalMarks, ObtMarks, TotalMarks, ResultTerm, SubPercentage.

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?

View 1 Replies View Related

Composite Primary Key

Jul 20, 2005

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?

View 5 Replies View Related

Composite Primary Key Or Not?

Jul 3, 2006

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?

View 13 Replies View Related

Composite Primary Key

Aug 4, 2007

Hello,

Does composite primary key affect performance on the table that contains the composite primary key or tables that references this table?

When composite primary key should be used?

View 2 Replies View Related

Best Way To Reference A Table With A Composite Key?

Nov 24, 2006

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?   

View 1 Replies View Related

How To Create Reference For Composite Key

Oct 29, 2007

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. 

View 4 Replies View Related

Candidate,composite And Alternate Key

Jun 6, 2008

 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 

View 2 Replies View Related

Creating Composite Primary Key

Feb 2, 2004

Hi

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.

Thanks
Jon

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved