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...
Im running updates on a table. I would like to only update the table with new records. Right now, Im running a query that will only update the records that were added yesterday. Here is my code:
FROM GRAB where BKDATE = convert(varchar(8), getdate()-1, 112)
I would like to Update like this (incorrect syntax):
FROM GRAB where FCN is distinct
I know the above is incorrect.... what would be correct?
I created an ouput text file that was perfect until specs were revised today to add quarterly tax and deduction summations for each employee. (Expected turnaround time for new spec: today!)
So my employee record now has 8 records (which I expected) because I had to join it to the pay_summary table. Like this:
SELECT COALESCE(CONVERT(char(1),e.record_status),'') + COALESCE(CONVERT(char(4),'97 '),'') and many more fields from employees as e JOIN emp_taxes as t on e.employee_no = t.employee_no LEFT OUTER JOIN pay_summary AS p ON e.employee_no = p.employee_no
Where dateadd(d, 0, datediff(d, 0, p.dated)) BETWEEN '20061231' AND '20070401'
and e.record_status not like 'D' and t.tax_authority_type = 'F' and e.company_no = '2' and e.employee_no = t.employee_no
order by e.employee_no
There are 6 or so fields I intend to sum as records to go into the file. In another language, in order to get one record per employee_no, the syntax would be like this:
sort on employee_no footing at employee_no report field_a field_b field_c subtotal field_e field_f subtotal
in which case fields c and f are numeric and summed from the multiple pay_summary records.
What is the (most efficient) SQL command to give only one record per employee while summing all the detail records?
I'm struggling with this one. We have a Street database that can contain multiple entries for one record in our main table. We don't wish to pass duplicate rows back to our end user so I wish to get distinct recno's (the unique identifier for the main table) back from our Street Table.
We have a stored procedure that currently uses Select distinct Recno but I think this is slower than if I used a unique index with ignore_dup_key. Problem is that when I alter the stored procedure it looks like the functionality of the LIKE function is somehow altered. For example Like 'King%' fails to return anything but Like 'King St%' does. I suspect it is the fact that the error "Server: Msg 3604, Level 16, State 1, Procedure" is being generated. How do I code around this server side.
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.
I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:
SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index FROM dbo.csr_standards_cmsars ORDER BY ARSFamily
but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups). The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
I do an distinct query like this:query = "SELECT DISTINCT name, total = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"The query works fine but I need to select one more field called "info" so I just tried this:query = "SELECT DISTINCT name, info, total = COUNT(*) from products where name LIKE '%" & searchString & "%' GROUP BY name"but then I get the error message:"Column 'products.info' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"I want do group just by "name" and not "info"How can I select this "info" field to?? somebody know??
Have a query that uses TOP 10; but I'm not receiving unique results and haven't been able to figure out using DISTINCT with TOP. Is it possible to use TOP with DISTINCT?
CREATE PROCEDURE [dbo].[CastCompare] @ID int, @TopRecords int, @Type int
AS
DECLARE @SQLSelect nVarchar(1000)
SET @SQLSelect = 'SELECT TOP ' + CAST(@TopRecords AS Varchar(10)) + ' * FROM TblCastList,TblCast where TblCastList.ProductResellerID=TblCast.ResellerID and TblCastList.ProductID=' + CAST(@ID AS Varchar(10)) + ' and TblCastList.Type=' + CAST(@Type AS Varchar(10)) + ' order by TblCast.[name] asc '
Exec sp_executesql @SQLSelect GO
how Can I add distinct command to query ? I tried something but none of them is work.
I want to add distinct TblCastList.[ProductResellerID]
I want to write a query to see the Authors and their last articles with no distinct values. Like AuthorImage - AuthorFullName - ArticleTitle - ArticleDate
If anyone knows the solution i will be glad . Thanks from now on
SELECT DISTINCT PC.ContentID, PC.GeoUnitID, PC.ContactID, CT.ID, CT.PageTitle, C.FirstName, C.LastName, C.MainPhoneNO, C.EmailAddress FROM ProductContacts PC INNER JOIN Content CT ON PC.ContentID = CT.ID INNER JOIN Contacts C ON PC.ContactID = C.ContactID WHERE PC.GeoUnitID = @GeoUnitID ORDER BY CT.PageTitle ASC
The problem I'm having is two rows have the same ContentID with a different ContactID. I want to only return a single row for each ContentID.
SELECT NotYetPublished, Available, Catalogx, pubdate, pubdate_full, NYP, Fulfillment, Title from finaldata where available <> 'Out-of-Print' or (totalnew <>0 and totalused <>0 and totalcollectible<>0) order by NYP desc, pubdate_full desc, Fulfillment desc, Title
I only want to return records that do not have the same Catalogx. I can't use distinct since some of the other data is different.
I'm kind of newbie and I like to know if you can solve me a question,
The fact is I'm trying to gather some information and I don't know how. What I want to do is retrieve the values of five columns, the first one is the primary key, but the others must be unique (all together)
This is probably easy but I can't work it out. I have this statementSELECT DISTINCT TOP 100 PERCENT dbo.CIF_PlaceReference.NameFROM dbo.CIF_Departures INNER JOINdbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint= dbo.CIF_PlaceReference.PlaceIDORDER BY dbo.CIF_PlaceReference.NameThis results in a column of placenames which is OK. There are also multiple'time of day' values against each placename however I only want to returnthe one nearest to the current time. If I do this...SELECT DISTINCT TOP 100 PERCENTdbo.CIF_PlaceReference.Name,dbo.CIF_Departures.Sta rtTimeFROM dbo.CIF_Departures INNER JOINdbo.CIF_PlaceReference ON dbo.CIF_Departures.EndPoint= dbo.CIF_PlaceReference.PlaceIDORDER BY dbo.CIF_PlaceReference.Name.... I get multiple place names.Any ideas?
Hello, I have a reference table. This table has an effective date and end date, and I need to get a single set of values at a point in time. Suppose the following hotel rating values are the values that I want to pull out of the table: 1 Star2 Star 3 Star 4 Star 5 Star This value has other information associated with it (benchmarks and such) and so it's possible that one of the 2 star entries gets end-dated, and then a new 2 Star entry gets created with an effective date and no end date. How can I query so that I only get one 2 Star entry at any point, and get the point in time where the date is between the effective and end date, but only pull back a single set of entries, one distinct rating value. So, suppose the first entry is end-dated 7/1, if I look at old data, I want to see the old benchmarks and so I want the previous entry end-dated 7/1; otherwise, I want to see the new end-date, with the new 2 star entry with no end date. How do I do that? Make sense?
Select A.SNO , A.Name , B.Picfrom Student A Left Join Picture BWhere A.SNo = B.PNoNo SNo Name Pic1 000 Andy /Doc/andy.jpg2 001 Andy /Doc/andy2.jpg3 002 VIVI /Doc/vivi.jpg4 003 VIVI /Doc/Vivi2.jpg The same person will only show 1 pic Pathlike this resultNo SNo Name Pic1 000 Andy /Doc/andy.jpg3 002 VIVI /Doc/vivi.jpgcan you please help? I trid use Group By but it not works..thank you
I have a table with following fields tdate custcode prodcode
table is filled with full year data and i want following result
I want count of distinct custcode in every past three months.
for example Result like this
month tjan tfeb tmar tapr tmay ..... tdec
prod1 prod2 . . prod5
And data under tmar should be count of distinct custcode of (jan,feb and mar) for corresponding prod code is required. Under tapr, count of distinct custcode of (feb,mar and apr) for corresponding prod code is required.
Can any1 help me please.
I am using MS SQL 2005 and above table is a big table (approx 10 million records)
I am trying to append data from a source table to a destination table, the only difference between the tables is that a primary key has been established on the destination table. Problem is, the source table has a handful of duplicate values in the column that corresponds to primary on destination table; so when I run the query I get a primary key conflict. What I'd like to do is select distinct values from the source table to avoid the conflict, but am having trouble getting it to run. Here is the statement-
INSERT INTO cust_master SELECT DISTINCT cust_master_temp.* FROM cust_mast_temp LEFT OUTER JOIN cust_master ON cust_master.temp.CUST_NUMBER = cust_master.CUST_NUMBER
I am developing a web site for my buddies and I to track the winnings fom my home poker games.
Table that keeps track of games goes like:
PlayerID PokerGameID curWon
I want to sort by top money maker nd show total won to date
I have tried to use:
Select Distinct Sum(G.curWon) AS curCash, P.playerID FROM GameReults As G, Players As G WHERE G.PlayerID=G.PlayerID Group By G.curWon, G.PlayerID
I can not getit to sort from highet to lowest and it itrates through each record. I only want it to show distinct players and their winnings. Can you please help?
Select top 500 email, custnumber from MailingList order by email
There may be some duplicate email addresses with different custnumbers. What would the query look like to only show records with unique email addresses, but still return the unique custnumbers in the query.
I'm trying to query the pubs database for last title for each author.The query returns each title for each author. How can I operate on maxof title?Here's what I'm trying:select distinct au_id,max(title_id)as title from titleauthorgroup by au_id,title_idorder by au_iddito*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I need p.phonenumber in there but then it messes up my DISTINCT ON m.number because I end up with dup m.numbers if I run just the inner select statement during testing.
But then I need the phonenumber in that subquery so that I can do list.phonenumber
ahh!
UPDATE Master SET master.homephone = list.phonenumber FROM MASTER m INNER JOIN (SELECT DISTINCT p.number, topphone.phonenumber FROM phones p INNER JOIN (SELECT top 1 phonenumber from phones) as topphone ON topphone.number = p.number WHERE p.phonetypeID = 1 AND ISNULL(p.good, 0) <> 0 AND LEN(p.phonenumber) = 10 ) AS list ON m.Number = list.Number WHERE m.homephone IS NULL OR m.homephone = ''
Error:
Msg 156, Level 15, State 1, Procedure Skiaa, Line 66 Incorrect syntax near the keyword 'top'. Msg 156, Level 15, State 1, Procedure Skiaa, Line 88 Incorrect syntax near the keyword 'top'.
I want to update m.homephone with the p.phonenumber based in the filtering critiria I have specified but this simple task is turning out to be a pain.
I'm familiar with MySQL and recently started working with MSSQL. I have a general understanding of how the DISTINCT command works, but would like to expand on that a bit.
I need to do a DISTINCT query on a record's 'name' column, but i need all of the values for each of those returned records. So, I need the query to only apply the DISTINCT command to the 'name' column, not the other columns of the received records.
Can someone explain a bit how to word this
Many thanks in advance.
EDIT: Upon reflection, I realize now that this request is counter to the way DISTINCT works. D'oh. Move along, nothing to see here. :(
