I have a field in my table named x_CFSNUM that is filled with various 4 digit numbers- ex: 0067, 0068, 0097, etc. These are not unique.
I would like to write a query that will tell me the sum of the times the highest number occurs.
For Example: If these were the numbers: 0067, 0067, 0067, 0089, 0094, 0095- The query would produce the quantity of "3" because 0067 is listed 3 times.
I know this is confusing. Please let me know if you need any clarification.
I have a problem with a SQL SELECT query. As far as my research goes i figured out that UNIQUE is used when you have one column that you whant unique and DISTINCT is used when you have more than one column that you want to all be unique. But i have a query where i want just some of the columns to be unique and some not. Here is the full query (It's in a stored procedure):
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[rpt_ExecSum_Combined_4fields](@BondGroupID int) AS SELECT DISTINCT SUM(dbo.Bond.LoanAmount) AS SumOfBondValue, COUNT(dbo.Bond.BondID) AS CountOfBond, dbo.ProgressStepType.ProgressStepType FROM dbo.BondGroup INNER JOIN dbo.TransactionTable ON dbo.BondGroup.BondGroupID = dbo.TransactionTable.BondGroupID INNER JOIN dbo.Bond ON dbo.TransactionTable.TransactionID = dbo.Bond.TransactionID INNER JOIN dbo.ProgressStep ON dbo.TransactionTable.TransactionID = dbo.ProgressStep.TransactionID INNER JOIN dbo.ProgressStepType ON dbo.ProgressStep.ProgressStepTypeID = dbo.ProgressStepType.ProgressStepTypeID WHERE (dbo.BondGroup.BondGroupID = @BondGroupID)
GROUP BY dbo.ProgressStepType.ProgressStepType, dbo.ProgressStep.ProgressStepID HAVING (dbo.ProgressStepType.ProgressStepType = 'AIP (Approval in Principle) received') OR (dbo.ProgressStepType.ProgressStepType = 'Grants') OR (dbo.ProgressStepType.ProgressStepType = 'Attorney') OR (dbo.ProgressStepType.ProgressStepType = 'Feedback received from bank') ORDER BY dbo.ProgressStepType.ProgressStepType
********* With the DISTINCT it gives me a full set of unique values but what i want is this: Every CountOfBond may have more than one SumOfBondValue and they may be the same, but if there is more than one dbo.ProgressStepType.ProgressStepType the query should only read it once. In other words the dbo.ProgressStepType.ProgressStepType is a unique value and the rest not...
Hi, I have been asked to write some code that can check a large table for duplicate values in a non pk column. The table may have up to 1000000 rows. The PK column is an auto increment field. For performance reasons the column in question could not be set to unique values only for inserts, an algorithm is used to create unique no's before the insert but what I am doing is double checking that their have been no duplicates created accidently. If their are duplicates I need to know what rows they occurred on.
How would I get the unique email addresses and its associated row of data from a SQL Server table that has no unique fields defined? If there is a duplicate email address then only show the first one and not the other rows with the same email address. Example table and data UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997623 KAIS-22 A.KAIS-22@SampleISD.org997624 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.ZKAIS-22@SampleISD.org997627 KAIS-22 AX.KAIS-22@SampleISD.org Result UserID LastName Email997249 MCCO-49 S.MCCO-49@SampleISD.org997462 BATE-62 A.BATE-62@SampleISD.org997605 DENS-05 B.DENS-05@SampleISD.org 997622 KAIS-22 A.KAIS-22@SampleISD.org997625 KAIS-22 A.ZKAIS-22@SampleISD.org997626 KAIS-22 AX.KAIS-22@SampleISD.org Thanks
Hi - I've got a table in SQL server that has its unique ID field as binary data. I have a gridview displaying data from this table and I have set the datakey of this gridview as that binary data field.I have a 'Select' ciolumn in my gridview that, when selected, will display more details from the selected record. The problem is, how do I pass the selected id back to sql server bearing in mind that it's binary data? Here's what I'm doing at the moment:I have a function in my data accsess class that queries the database:Public Function getDetailsById(ByVal Id As System.Byte) As DataSet Dim con As New SqlConnection(conStr) Dim cmd As New SqlCommand("SELECT * FROM tableName WHERE Id=@Id", con) cmd.Parameters.Add("@Id", SqlDbType.Binary) cmd.Parameters("@Id").Value = Id Dim ds As New DataSet Dim adp As New SqlDataAdapter(cmd) adp.Fill(ds) Return ds End FunctionAnd then I call this function from my page with the gridview using: Dim da As New myDataAccess Dim ds As New DataSet ds = da.getDetailsById(GridView1.SelectedDataKey.Value)But I get the error:Conversion from type 'Byte()' to type 'Byte' is not valid.Any ideas where my data/code is going wrong? I'm not sure how to pass this sort of data around?Thanks
I am trying to create a query to return the latest record from a dataset. The code created so far returns multiple records, where I need a single record to be returned.
hilet me explain my need..following are the tables im using..tbl_company (company table - parent) id company 1 test 2 test123 tbl_dept (department table - master) id dept 1 dept1 2 dept2tbl_compdept (company departments table - child) cmpid deptid 1 1 2 1 2 2 wats my need is.. while the company is listing..by query using joins, result was like this..company depttest dept1test123 dept1test123 dept2i need company test123 should be listd only once..when i use group by or distinct means, all r listed..is there any way to filter out therepeating company list by just listing the company list only once..
I am having a bit of difficulty trying to work how to write a query. I am normally only used to doing SELECT, UPDATE and DELETE queries ... the simple ones.
However I have come across a query whereby things get a lot more complex.
My company has an employee table with a way to store data that I have not been used to.
EMPLID, NAME, DATELASTMODIFIED, EmployeeStatus. (There are lots more but N/A)
The problem that I have is that there can be more than one row for an employee id. Therefore I cannot uniquely identify an employee id. Instead what I have discovered I have to use is to find an employee with the last modified date higher than the other rows with the same employee id.
The query I am trying to achieve is:
Code:
Count all employees that have an employee status of 'Employed'.
(Therefore counting only one employee per id rather than counting the same employee more than once)
The trouble is, translating this into a SQL query. I have tried to have a go but don't know how to query:
Code:
SELECT COUNT (DISTINCT EmplId)[ FROM Employeetable AS e WHERE e.LASTDATEMODIFIED <And Stuck> AND EmployeeStatus = 'Employed'
So I thought hmm lets pseudo code this, but then I get stuck into compelixty:
Code:
SELECT * FROM EmployeeTable WHERE EmployeeStatus = 'Employed'
int counter = 0; ForLoop (EmplId) { LastDate Date if(LASTMODIFIEDDATE > LastDate) { LastDate = LASTMODIFIEDDATE } //How do I return the value? Do I count the value? }
Has anyone come across this issue before? Can anyone help it quite fundemental to what I have to query so help on this is highly appreciative.
select * from bigtable where product_code in ('abc','def','hij','klm')
I know that each of those product_codes will return a large data set. What I'd like to do is to run the same query but constrain the result set to a max rownum of 200.
I'm fairly new in SQL. Been trying for months to create the right script for this particular case but still cannot give me 100% result as required.
SCENARIO :
I am required to query from 2 tables for those unique record that meets both conditions below:- 1. Status is 1 @ max (trans_id), paychnl = CC 2. Status is 2 @ max (trans_id), paychnl = A or B
FYR, 2 tables and respective columns to query are as below:- table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FYI, status refers to the paychnl method status:- ==> 1 means the current paychnl method ==> 2 means the previous paychnl method
paychnl method can be multiple because it will be defined as 2 for all the histories' paychnl chosen earlier, but 1 should only be unique as it is the latest paychnl chosen for each unique ID. however, it may appear more than once when it's taking those in earlier TRANSDATE, so here we would need the max trans_id as it will show the latest updated TRANSDATE.
Apart from that, I need only those most recent paychnl to be A or B and the latest paychnl is CC so, this been indicated by the same max trans_id for the same ID.
Aft trying so many times on this MAX command but failed to get any result, I only managed to come up to this part only. please refer below:-
table PTFF --> col ID, TRANS_ID,TRANSDATE,EFFDATE,TRANSCODE table CHFF --> col STATUS,PAYCHNL
FROM DBO.PTFF PTFF JOIN DBO.CHFF CHFF ON CHFF.ID = PTFF.ID WHERE PTFF.TRANSDATE BETWEEN 130501 AND 130831 AND PTFF.TRANSCODE='T522' AND (CHFF.STATUS=1 AND CHFF.PAYCHNL='CC' OR (CHFF.STATUS=2 AND (CHFF.PAYCHNL='A' OR CHFF.PAYCHNL='B')))
However, the script above returns :-
1. All those records with STATUS 1 regardless paychnl is A or B in most recent status 2, 2. Expected results also appear ==> 1 same ID with status 1 while paychnl=CC and status 2 while paychnl=A or B 3. Also duplicates of expected results but for different TRANSDATE and not at MAX TRANS_ID
Hi Respected, I am working in .net, and according my client he want to see all record from table uniquely, and does not want to give order also. for doing this i did use DISTINCT, but my problem is when i used DISTINCT the query return sorted records that is why, i need guide which will help to make DISTINCT and physical stored record from the table. thanks. bay regards Rajat.
I am trying to calculate the maximum sales per store where multiple stores exist in the same region (i.e there must be doubles of that row) in order to determine the most profitable store per that region.
E.g. Store Sales A 500 B 200 B 100 C 400 C 800 D 100 D 200 D 700
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?
I have an Orders table which has the following fields:
OrderID (PK, int, auto increment, not null) CustomerID (FK, int, null) PaymentDate (datetime, null) UserID (uniqueidentifier)
(and other irrelevant fields)
Basically, for a specific PaymentDate range (29th July 2014 - 26th August 2014, inclusive) I want to select all orders where they only appear once in the orders table based on the CustomerID, so I only want to know about them if they have a paid order (decided by PaymentDate not being null) in that date range, but also taking into account if they have ever had a paid order outside of that date range. I'll also be joining on to the aspnet_Users table to get the username assigned to that order.
ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).
The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
Hi everyone, I need urgent help to resolve this issue... As far as the performance goes which one is better.. Unique Index(col1, col2) OR Unique constraint(col1, col2) ? Unique constraint automatically adds a unique index and unique index takes care of uniqueness then whats the use of unique constraint ?
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
hi team, .Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today. Here is the code
Code Snippet create procedure SearchPartKeyword ( @Keyword varchar(250) = null, @Universal_Id varchar(10) = null ) as select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p inner join notes n on p.PartNumber = n.Identifier where n.Universal_ID = @Universal_ID and p.Description like @Keyword union select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p where p.Description like @Keyword
and the results: PartNo Description SO Notes LA DMM Legacy Folder Printer de90008 MAIN BOARD 1 DGF1 114688 0 0 0 de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
create table Test ( [recId] [int] identity(1, 1) not null, [code] [varchar](50) not null, [prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)), constraint [PK_Test] primary key clustered ( [recId] ) with fillfactor = 90 on [primary] ) on [primary] go
insert into Test (code, prime) values ('AVA', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('CAR', cast(1 as bit)) insert into Test (code, prime) values ('CAR', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(1 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit))
select * from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail insert into Test (code, prime) values ('RLW', cast(1 as bit))
in sql server 2000 i know how to make primary key using enterprise manager i want to make one of the columns foreign key,how to do that using enterprise manager.and what is the difference between both.
