Select Distinct For Different Rows
I have the following tablecolumns: [col1], [col2],[col3] and [NAME].
I want to select the name column for each row where [col1]='07'.
The problem is that there are several rows where [col1] contains '07' and also the name is the same. [col2] and [col3] contain different data for these double rows...however, I cant use the [col1] and [col2] values in my query because I dont know what values they contain beforehand.
So now, when I execute my query and add the DISTINCT key I still get all the double rows!
I hope this explains my problem, help is really appreciated...
ow, btw: deleting the double rows is not an option....
View Complete Forum Thread with Replies
Related Forum Messages:
Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Hi, I have the following script segment which is failing: CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude)) INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'." Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values. The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why. Any ideas?
View Replies !
Select Distinct Rows From Duplicate Rows....
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
View Replies !
Select Distinct Rows
Hi, I'm having a little bit of trouble trying to figure out how to do this query, right now I have: SELECT I.AppItemId, P.ProductID, P.PartNum, P.Relist, I.AppUserId FROM ProductsToRelist I join Products P on P.ProductID = I.AppSKU WHERE P.Relist = 1 and I.AppStatus = 5 and Not I.AppItemId is Null and it returns something like this: AppItemId ProductID PartNum Relist AppUserId 2786 -32730 SELECT_OOS11 2787 -32729 SELECT12 2788 -32727 SELECT_OOS11 4269 -30987 SELECT_OOS12 1665 -30987 SELECT_OOS11 2433 -30987 SELECT_OOS11 4272 -30984 SELECT11 2436 -30984 SELECT11 2793 -32708 SELECT11 But I only it want it to return 1 record for each ProductID like so: AppItemId ProductID PartNum Relist AppUserId 2786 -32730 SELECT_OOS11 2787 -32729 SELECT12 2788 -32727 SELECT_OOS11 4269 -30987 SELECT_OOS12 4272 -30984 SELECT11 2793 -32708 SELECT11 ProductID is the primary key for the Products table, and a product can be in the ProductsToRelist table many times but each row would have a unique AppItemId. I know that I need to use Distinct or a different kind of join, but I'm not sure which. How would you suggest to do this? Thanks
View Replies !
Getting The Right Distinct Rows
I have a database for a CMS I have made, which has a column called ‘tag’ everytime a page is updated it inserts another row in to the table with the same tag but with a updated date. i use this method so i have a version history What I want to get out is rows that have distinct tag columns and is also the newest row associated with that ‘tag’.
View Replies !
Selecting Distinct Rows
Hi, I want to select the 8 most saled products from large orders table... the problem is that when i use the "distinct" sentence (something like this- "SELECT TOP 8 distinct id, products, productid FROM tbl_orders ORDER BY id") I get back the distinct of any columns.... (and any ID is distinct, of course), but if i don't include the id's in the distinct sentence, i can't order by id's. can i get the last orders, only by distinct product, and not by distinct id, and order them by the id's?
View Replies !
Distinct -- But Across Multiple Rows?
This is a simplified version of a problem I am having. I have large number of stores. These stores get items in varying quantites. I need to work out the various combinations of items and quantities, these will be used to create boxes of items that will then get shipped to stores. So, given that I have the following declare @stores table( store varchar(10), item varchar(10), quantity integer ) insert into @stores select 'Store A', 'item 1', 1 union select 'Store B', 'item 1',1 union select 'Store B', 'item 2',1 union select 'Store C','item 1',1 union select 'Store C','item 2',3 union select 'Store D', 'item 1', 1 union select 'Store E', 'item 1',1 union select 'Store E', 'item 2',1 I need to work out that I need the following 3 different boxes, which is almost like a distinct, but spaning mulitple rows... Box Item Quantity --- ---- -------- Box 1 item1 1 Box 2 item1 1 Box 2 item2 2 Box 3 item1 1 Box 3 item2 3 And then I need to back track to say that Store A gets Box 1 Store B gets Box 2 Store C gets Box 3 Store D gets Box 1 Store E gets Box 2 I think I may need some more levels of abstraction to get the details, but even using loops I am having trouble working out when I have seen a combination before and have a box to use or when I need to create and assign a new box. Any thoughts/suggestions/pointers as to where on earth I start with this...
View Replies !
SQL Counting Number Of Non-distinct Rows?
Hi, I have a table that for ease has this data in:R1, R2, R....z---------------------A | 12A | 22A | 30B | 0B | -1B | -3C | 100I want to generate a table for each distinct row in R1, gives a countof all the rows with data correspondingFor the above table I would getA | 3B | 3C | 1Im probably being stupid but cannot see this at the moment... pleasehelp.Thanks
View Replies !
Help With A Query (Selecting Distinct Rows As Well As How To Use NOW())
Hi All, I'm a beginner in SQL and would like some help with writing a query that needs to: a) Return the latest time that an event happened (along with the event), and also b) Determine if this event occurred more than 30mins ago. For example, Table EVENT consists of the following data: EVENT DateTime, A 16/1/08, 14:03:55 B 16/1/08, 14:30:27 A 16/1/08, 17:42:18 I would like the results for the first part of query to be: EVENT DateTime, A 16/1/08, 17:42:18 B 16/1/08, 14:30:27 I have tried creating a query based off this thread, but for some reason it kept complaining that the EVENT column in Table EVENT didn't exist. For the part b), I have no clue as to what I should do apart from that I would need to use NOW(). Any help would be appreciated. MonkeyMark
View Replies !
Return Rows That Arent Distinct
I am trying to create a query that will find all the records that have the same value multiple times in the a column called phonenumber. How do i return disticnt records having count greater than 1
View Replies !
Distinct Random Rows Using NewID()
I have 2 tables, Artists and Artworks. I have a query: SELECT TOP (4) dbo.Artists.ArtistID, dbo.Artists.FirstName + ' ' + dbo.Artists.LastName AS FullName, dbo.Artworks.ArtworkName, dbo.Artworks.Image FROM dbo.Artists INNER JOIN dbo.Artworks ON dbo.Artists.ArtistID = dbo.Artworks.ArtistID ORDER BY NEWID() This query returns random images, but the artists are sometimes repeated. I would like to have DISTINCT Random Artists returned, each with a random image. I tried various subqueries, but I just get error messages. Any help would be appreciated. Thnks, Paolo
View Replies !
Selecting Distinct Top 3 Rows From Database Using Join
Hi guys, Just trying to select a set of Articles from a SQL Server Database. The Articles all have a Category ID which is stored in another table (as an Article could be in more than one Category). I want to select the Top 3 Articles in a Category. At the moment I have as my SQL; "SELECT TOP 3 f.ArticleID, f.Heading, f.Summary, f.WrittenDate, f.ArticleURL FROM feedTable f LEFT JOIN Categories c ON f.ArticleID = c.ArticleID WHERE c.CategoryID=" + CategoryID + " AND c.ArticleID<>" + id + " ORDER BY c.CategoryID" Which seems to work to an extent in that I do get three articles in the same Category appearing. However, there are sometimes duplicates appearing, so I need to incorporate a DISTINCT clause to the above. I'm not sure where to put this in though. Any ideas? Thanks.
View Replies !
Selecting Distinct Rows ?? From Over 10 Mill Records
hi, I have a table that contains 11,169,000 rows that was downloaded from the main frame. There are alot of duplicate records in that table. I ran a query select * from tbl.... it still running and running and running ... it never stoped.... what seems to be the problem.... There are no primary keys or index in that table... so my question , how would I deal with such table ... I want to run certain reports from that table and it seems that all my attempts failed? anyone can help
View Replies !
TSQL - Avoid Duplicated Rows - Using Distinct / Group By
Hi guys, need some help here please... The code below shows 4 rows. The first two rows are almost identical, but the two of them exists in the same table as different rows. Row number 1 is also related to Row number 3 and Row number 2 is also related to Row number 4 The problem is that I have to use only one of then (Rows number 1 or 2) togheter with row 3 & 4. I thought using GROUP BY RECEIPTJURNALMATCH.JURNALTRANSID, but getting error. Thanks in advance, Aldo. Code Snippet SELECT RECEIPTJURNALMATCH.JURNALTRANSID AS 'R.JURNALTRANSID', RECEIPTJURNALMATCH.MATCHNUM AS 'R.MATCHNUM', JURNALTRANSMOVES.ACCOUNTKEY AS 'J.ACCOUNTKEY', JURNALTRANSMOVES.SUF AS 'J.TOTAL', STOCK.REMARKS AS 'S.REMARKS' FROM RECEIPTJURNALMATCH INNER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID LEFT OUTER JOIN STOCK ON RECEIPTJURNALMATCH.STOCKID = STOCK.ID WHERE JURNALTRANSMOVES.ACCOUNTKEY IN ('123456') Below the results: R.JURNALTRANSID R.MATCHNUM J.ACCOUNTKEY J.TOTAL S.REMARKS 89634 16702 123456 1155 ×¢×—: ;5752 89634 16703 123456 1155 ×¢×—: ;5752 89637 16702 123456 400 NULL 89639 16703 123456 155 NULL
View Replies !
Help With SQL Statement, Pulling Back Duplicate Rows On DISTINCT Keyword
Hello everyone, I'm working on a SQL statement that I "thought" worked fine until I noticed I was getting a duplicate row. Below is the SQL statement from the stored procedure: SELECT DISTINCT number AS 'RteNum', leg_orig AS 'Origin', leg_dest AS 'Dest', AcEquipment.EquipmentDesc AS 'EquipType', SUBSTRING(trailer_option, 1, 1) AS 'TrailerOption', leg_depart_time_local AS 'DeptTime', leg_arrive_time_local AS 'ArrTime', dev.fnConvertEffectiveDaysToDaysOfWeek(SUBSTRING(leg_effective_local, 2 ,7)) AS 'EffectiveDays', TruckEditor.EffectiveDays as 'NewEffectiveDays' FROM lhif_prod JOIN AcEquipment ON AcEquipment.EquipmentType = lhif_prod.Equipment_Type LEFT JOIN dev.TruckEditor ON TruckEditor.Origin = lhif_prod.leg_orig AND TruckEditor.Dest = lhif_prod.leg_dest AND TruckEditor.RouteNum = lhif_prod.number AND TruckEditor.DeptDate = lhif_prod.leg_depart_date_local WHERE leg_depart_date_local BETWEEN @DateStart AND @DateEnd AND Type_Code = 'T' AND leg_orig = @LocID ORDER BY RteNum, Dest, DeptTime Here is what comes back from this query:ABE00 ABEA ABER CTV5 H 1855 1915 MTWT--- NULLABE01 ABEA ABER CTV5 H 1941 2001 MTWT--- NULLABE02 ABEA ABER CTV5 H 2045 2105 MTWTF-- NULLABE03 ABEA ABER CTV5 H 2059 2119 MTWTF-- NULLABE04 ABEA ABER CTV2.5 H 2245 2305 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- NULLABE11 ABEA ABER WALKIN H 2045 2100 MTWTF-- MT-TF--ABE12 ABEA ABER WALKIN H 2109 2124 MTWTF-- NULLEF038 ABEA EWRHB 53BULK H 0100 0245 -TWTFS- NULLEF085 ABEA EWRHA CTV5 H 1955 2140 MTWT--- NULLEF106 ABEA EWRHB CTV5 H 1901 2046 -----S- NULLEF140 ABEA ABER CTV5 H 0550 0610 M------ NULLEF166 ABEA EWRRA CTV5 H 2230 0010 MTWT--- NULLEF366 ABEA EWRRA CTV5 H 2230 0010 ----F-- NULLEF543 ABEA EWRRA CTV5 H 2200 2345 MTWTF-- NULL The 2 rows in bold are the issue right now. There should only be 1 row (the 2nd one where the last column is not null). I'm not sure why it returns both columns when I'm doing a join on there to add that last column. Can anyone help me out with this? I'm not very strong in SQL, so if I'm overlooking something, I'd appreciate any help you can provide. Thanks.
View Replies !
Please Help Me: SQL SELECT DISTINCT
I have a table myTable (ID, Year, Name, Note)data in this table:ID Year Name Note 1 2008 Petter hdjhs2 2008 Nute jfdkfd3 2007 Suna dkfdkf4 2007 Para jfdfjd5 2009 Ute dfdlkf Please help me to Select DISTINCT [Year]]ex:1 2008 Petter hdfdfd3 2007 Suna fdkfdk5 2009 Ute fkdfkdfd Thank!
View Replies !
SELECT Distinct Help
Hello Everyone Hopefully someone can help me create a SQL statement for this. I need the ff: fields Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE, Specialty Let say I have a table. Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE. Specialty1000 999 Mike James Plano 75023 Internal Medicine1000 998 Mike James Allen 75021 Internal Medicine3333 700 John Smith Arlington 70081 Dermatologist3333 701 John Smith Dallas 72002 Dermatologist2222 630 Terry Walker Frisco 75001 Optalmologist2222 632 Terry Walker Dallas 76023 Optalmologist4444 454 Tim Johnson San Anontio 72500 Internal Medicine 4444 464 Tim Johnson Frisco 72660 Internal Medicine I want to select only "one" instance of the provider it doesnt matter what is selected either the first address or the second address. It should show Prov_ID, Record_ID, PROV_NAme, LOC_city, LOC_Zip_CODE. Specialty1000 999 Mike James Plano 75023 Internal Medicine3333 700 John Smith Arlington 70081 Dermatologist2222 632 Terry Walker Dallas 76023 Optalmologist4444 464 Tim Johnson Frisco 72660 Internal Medicine And yes, the table is not Normalized..Is there anyway I could get away with it without having to normalize? Thanks Lorenz
View Replies !
Select Not Distinct?
Is their a way to select all items from a table that are not distinct? Meaning, I want to know which items in a column occur more than once. Example: Suppose we have a table with student names, ss# and address. I want to display only records where their is more than one studen with the same name. So for example their could be ten people with the name of "Mike" in a class? Ralph
View Replies !
SQL Select DISTINCT?
OK I have a Forum on my website make up of 3 tablesTopisThreadsMessageI show a list of the 10 most recent Changed Threads. My Problem is that my Subject field is in the messages Table, IF I link Threads to Messages then try to use Select Disticnt I get mutliple Subject fields as the messsges are not unique (obvisally) So I want to get the top 10 Threads by postdate and link to the Messages table to get the Subject headerAny help? Or questions to explain it better?
View Replies !
SELECT DISTINCT
I don't know what the correct syntax is to do what I want with the DISTINCTfunction (if it's actually possible).I have a query which displays a variety of fields from a variety of tables(pretty standard).However, I only want to show records where the contents of one particularcolumn in the query are unique - I do not want to perform the function onthe entire record because other fields in the records may be duplicated foras reason.
View Replies !
Select Distinct Help?
Can you have "Select Distinct" in Union Query,because that is what I am trying to do and this is the error message I get. "The text, ntext, or image data type cannot be selected as DISTINCT." I would need to do that because i have duplicate records,because these records are getting written into the db when templates are generated and sometimes if they double click it generates two and writes that many results as well, so that is why I was thinking that select distinct would solve my problem. Thanks for your help This is the query in question: SELECT Distinct 'O' AS Origin, a.RecordID, a.RelocateID, a.SupplierID, a.DateIn, a.DateOut, a.NoOfDays, a.AgreeAmt, a.PaymentMethod, a.AccomType, a.Reason, a.InvRecvd, a.RelocateeTempAccomTS, a.BedConfiguration, a.NumberOfPax, a.AdditionalItems, a.Currency, a.TotalAmount, a.EnteredBy, a.LastModifiedBy, a.ReferenceNumber, a.Location, a.Comments, a.ArrivalTime, a.PONumber,CommissionRate, ISNULL ((SELECT TOP 1 ExchangeRateToUSD FROM luCurrencyExchangeRates c WHERE a.Currency = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL ((SELECT TOP 1 ExchangeRateToUSD FROM luCurrencyExchangeRates c WHERE 'AUD' = c.CurrencyID AND a.DateIn >= c.ActiveDate), 1.0) AS AUDExchangeRate, a.WhenConfirmed, e.RequestID AS RequestID, e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause, dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc FROM dbo.RelocateeTempAccom a LEFT OUTER JOIN dbo.luDecisionMaker ON a.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT OUTER JOIN dbo.viewZYesno ON a.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN dbo.docRequests e ON '{RelocateeTempAccom.RecordID}=' + CONVERT(VARCHAR a.RecordID) = e.WhereClause WHERE (ISNULL(a.Cancelled, 0) = 0) UNION ALL SELECT Distinct 'D' AS Origin, RecordID, RelocateID, DTASupplierID AS SupplierID, DTADateIn AS DateIn, DTADateOut AS DateOut, DTANoOfDays AS NoOfDays, DTAAgreeAmt AS AgreeAmt, DTAPaymentMethod AS PaymentMethod, DTAAccomType AS AccomType, Reason, InvRecvd, RelocateeDTATS AS RelocateeTempAccomTS, BedConfiguration, NumberOfPax, AdditionalItems, Currency, DailyTotal AS TotalAmount, EnteredBy, LastModifiedBy, ReferenceNumber, Location, Comments, ArrivalTime, PONumber,CommissionRate, ISNULL ((SELECT TOP 1 ExchangeRateToUSD FROM luCurrencyExchangeRates d WHERE b.Currency = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS ForeignExchangeRate, ISNULL ((SELECT TOP 1 ExchangeRateToUSD FROM luCurrencyExchangeRates d WHERE 'AUD' = d .CurrencyID AND b.DTADateIn >= d .ActiveDate), 1.0) AS AUDExchangeRate, WhenConfirmed, e.RequestID AS RequestID, e.DocumentID AS DocRequestID, e.RequestWhen AS RequestWhen, e.WhereClause AS WhereClause, dbo.luDecisionMaker.DecisionMakerName AS DecisionMadeBy, dbo.viewZYesno.Description AS CommissionableDesc FROM dbo.RelocateeDTA b LEFT JOIN dbo.luDecisionMaker ON b.DecisionMaker = dbo.luDecisionMaker.DecisionMakerID LEFT JOIN dbo.viewZYesno ON b.Commissionable = dbo.viewZYesno.[Value] LEFT OUTER JOIN dbo.docRequests e ON '{RelocateeDTA.RecordID}=' + CONVERT(VARCHAR, b.RecordID) = e.WhereClause WHERE ISNULL(Cancelled, 0) = 0
View Replies !
Select Distinct
Hi, I wonder if anyone here can shed some light on why the query below produces duplicate EmailAddress values even though we specify the DISTINCT clause. SELECT DISTINCT(EmailAddress) SubscriberID, FirstName, Surname, SubscriberID FROM TestMailingList ORDER BY EmailAddress Thanks.
View Replies !
Select Distinct
Hi! I have 4 tables and they have a common column (eg. regionid). These 4 tables have data overlapping with the others. Some data exist in a table but not on the others. What I want to do is to do a select that will display all distinct regionid from these tables. It should be total of all the tables but will suppress any duplicates with the others. Note that UNION is working but I can't use that. Why ? because UNION is not supported or maybe not working properly with RDB database. I'm doing an appliaction for heterogenous datasource. Any tips, hints or info will be appreciated. thanks in advance. zrxowm Table REGION1 : RegionID RegionDescription ----------- -------------------------------------------------- 10 Place1 11 Place11 1 Eastern 2 Western 3 Northern 4 Southern (6 row(s) affected) Table REGION2 : RegionID RegionDescription ----------- -------------------------------------------------- 21 Place21 22 Place22 1 Eastern 2 Western 3 Northern 4 Southern (6 row(s) affected) Table REGION3 : RegionID RegionDescription ----------- -------------------------------------------------- 33 Place33 31 Place31 1 Eastern 2 Western 3 Northern 4 Southern (6 row(s) affected) Table REGION4 : RegionID RegionDescription ----------- -------------------------------------------------- 41 Place41 42 Place42 1 Eastern 2 Western 3 Northern 4 Southern (6 row(s) affected)
View Replies !
Select Distinct
Can I run Select distinct on one fieldname only while I'm selecting more than one fielname, like Select Distinct col1, col2, col3 from table I need distinct on col1 only and not on the other 2 columns, is it possible. Thanks
View Replies !
Select Distinct
Does anyone know why this does not work? SELECT DISTINCT tb2.column20 tb2.column20, tb1.column10, tb2.column21, tb2.column22, tb3.column30 FROM table1 tb1, table2 tb2, table3 tb3 WHERE tb1.column11 = 'P' AND tb2.column23 = 'P' AND tb1.column12 = tb2.column24 AND tb2.column25 = tb3.column31 ORDER BY tb2.column20 Its supposed to return only the distinct entries in tb2.column20
View Replies !
Select Distinct
I may be new at this but I can't find any explanation why SELECT DISTINCT(Stno), Grade shows distinct occurrences for each Stno-Grade combination rather than just distinct occurences of Stno. What is the solution?
View Replies !
Select Distinct Help
select distinct ISNULL (a.account,'') as "Account", ISNULL (c.address1,'') as "Address", ISNULL (c.city,'') as "City", ISNULL (c.state,'') as "State", ISNULL (c.postalcode,'') as "Zip Code", ISNULL (a.mainphone,'') as "Phone", a.userfield1 as "GID", s.division from sysdba.account as a join sysdba.address as c on a.addressid = c.addressid join sysdba.staff as s on a.accountid = s.accountid where a.type like '%client%' and a.userfield1 is not null and (s.division like '%HR%' or s.division like '%db%') and s.type = 'client' So what happens now is that if an account is listed in two division I get two distinct rows returned, but each with the same GID column. When I try to push this to a new database that has GID as the primary key I get duplicate on that column and it errors out. I need to be able to get only a single row if the division is both HR and db. how to tackle this problem. Thanks!
View Replies !
Select Distinct
Hi members, Is there a way to count the number of data with distinct column a and column b (combination)?? ex col A Col B 1 1 1 2 1 1 2 1 3 3 3 3 4 3 should give 5. The ones in red are duplicates that I want to eliminate. Thanks,
View Replies !
Select Distinct???
select ExpenseCodeID, [Group], SubGroup, GLAccount,ExpenseCode, ProjType from BridgeFinance..OPS_ExpenseCodes Order By ExpenseCode I have this query only thing wrong with it is that I dont know how to only select different values from my expenseCode column that looks like below....I dont want to select "Employee Only Meals" as many times as it appears in the table just once do i want to select it....any help with how i should write my query would be great! thanks! Administrative contract work Cell phone Courier/Shipping Employee only Meals Employee only Meals Employee only Meals Employee only Meals Employee only Meals Employee trans/parking Health Club Memberships Home Office Expenses IT equipment-non capitalizable
View Replies !
SELECT DISTINCT
Hi Just a question I have a query that selects profile data for members, if I don€™t do a select distinct it gives me a lot of correct values, (unique values) of members i.e. only one record per member, but every now and then I get duplicate values for one member, multiple times. Why does this occure? I know SELECT DISTINCT is there to remove duplicates, but without SELECT DISTINCT why would this €œmistake€? happen? Any help would be greatly appreciated. I.e. 2 | 3 | John | Slack | Philips |5 1 | 2 | Jason | Limrick | Jones | 3 1 | 2 | Jason | Limrick | Jones | 3 1 | 2 | Jason | Limrick | Jones | 3 1 | 2 | Jason | Limrick | Jones | 3 1 | 2 | Jason | Limrick | Jones | 3 2 | 3 | Jane | John | Parker |4 Why would it create duplicate records if the values are the same? Kind Regards Carel Greaves
View Replies !
Select Distinct
I have a select query Select distinct a,b,c,d from xyz I would like to know what the syntax is if I want only a,b,c to be distinct and not d. I tried something like Select (distinct a,b,c),d but getting error what is the correct query to do this. Please help.
View Replies !
SELECT DISTINCT
Hello, When I try the SELECT DISTINCT like this: USE CHEC SELECT DISTINCT [DATE_CONVERSION_TABLE_NEW].MONTH, DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth, DAT01.[_@550] AS LoanType, DAT01.[_@051] AS Branch, DAT01.[_@TP] AS ProdTypeDescr, SMT_Branches.[BranchTranType] AS TranType, --SMT_Branches.[AUCode] AS AuCode, COUNT(*) AS Totals FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] --ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE] ON DAT01.[_@040] = [_@040] INNER JOIN SMT_BRANCHES ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType] WHERE DAT01.[_@040] Between '06/01/2006' And '06/30/2006' And SMT_BRANCHES.[BranchTranType] = 'RETAIL' AND DAT01.[_@051] = '540' --And SMT_Branches.[AUCode] = '1882' And DAT01.[_@TP] = '115' And DAT01.[_@550] = '3' GROUP BY DAT01.[_@051], DAT01.[_@550], DAT01.[_@TP], SMT_Branches.[BranchTranType], --SMT_Branches.[AUCode], [DATE_CONVERSION_TABLE_NEW].MONTH, DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH, DAT01.[_@051], DayofMonth ASC --SMT_Branches.[AUCode] ASC --COMPUTE sum(count(*)) I get the same result set as before. What do I need to change? Kurt
View Replies !
Help With A Distinct Select?
Hi, I am new to this forum so hello to everyone! I need some help getting unique records from a query, I have a large amount of nested selects and i want to only display distinct records, I have a unique identifier (party ID) but the code was written by someone else (who is on holiday!) and i need to work out where to insert the disctinct select (if at all? - open to a better way?) this query should pull back records and then the results are pasted in to excel, however would a DTS solve the issue with duplicates?? Any help more than appreciated! Heres the code... CREATE PROCEDURE dbo.negative_surplus_report AS SELECT dbo.Cubit_Override_ID.UserName AS [User], dbo.Cubit_Customers.RecordDateTime AS Date, dbo.Cubit_Customers.Customer_Status AS [Customer Status], dbo.Cubit_Customers.Call_Prompted_By AS [Call Prompted By], dbo.Cubit_Outcomes.Outcome_Description AS [Outcome], ISNULL(dbo.Cubit_EPH.Total_Balance, 0) AS [Egg Debt], ISNULL(dbo.Cubit_Debt.Income_Total, 0) AS Income, ISNULL ((SELECT SUM(Balance) FROM Cubit_Debt_Card INNER JOIN Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Card Debt], ISNULL ((SELECT SUM(Balance) FROM Cubit_Debt_Loan INNER JOIN Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Loan Debt], ISNULL(dbo.Cubit_Spending.Out_Mortgage, 0) AS [Mortgage Payment], ISNULL(dbo.Cubit_Spending.Out_Rent, 0) AS [Rent Payment], ISNULL(dbo.Cubit_Debt.Mortgage_Balance, 0) AS [Mortgage Balance], ISNULL(dbo.Cubit_Debt.Property_Value, 0) AS Property, ISNULL(dbo.Cubit_Customers.Party_ID, '') AS [Party ID], ISNULL(dbo.Cubit_Customers.Cubit_Cust_ID, '') AS [Cubit ID], ISNULL(dbo.Cubit_Spending.Out_Total, 0) AS Outgoings, ISNULL(dbo.Cubit_EPH.Total_Monthly_Pmt, 0) AS [Egg Payments], ISNULL ((SELECT SUM(Monthly_Pmt) FROM Cubit_Debt_Card INNER JOIN Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Card Paymements], ISNULL ((SELECT SUM(Monthly_Pmt) FROM Cubit_Debt_Loan INNER JOIN Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID), 0) AS [External Loan Payments], dbo.Cubit_Debt.Income_Total - (SELECT SUM(Monthly_Pmt) FROM Cubit_Debt_Card INNER JOIN Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) - (SELECT SUM(Monthly_Pmt) FROM Cubit_Debt_Loan INNER JOIN Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) - dbo.Cubit_Spending.Out_Total - dbo.Cubit_EPH.Total_Monthly_Pmt AS Surplus, dbo.Cubit_Override_ID.Mandate_Level FROM dbo.Cubit_Customers INNER JOIN dbo.Cubit_Managers ON dbo.Cubit_Customers.Manager_ID = dbo.Cubit_Managers.Manager_ID INNER JOIN dbo.Cubit_Areas ON dbo.Cubit_Managers.Area_ID = dbo.Cubit_Areas.Area_ID LEFT OUTER JOIN dbo.Cubit_EPH ON dbo.Cubit_Customers.Cubit_Cust_ID = dbo.Cubit_EPH.Cust_ID LEFT OUTER JOIN dbo.Cubit_Spending ON dbo.Cubit_Spending.Cust_ID = dbo.Cubit_Customers.Cubit_Cust_ID INNER JOIN dbo.Cubit_Outcomes ON dbo.Cubit_Customers.Outcome_ID = dbo.Cubit_Outcomes.Outcome_ID LEFT OUTER JOIN dbo.Cubit_Additional_MI_Data ON dbo.Cubit_Customers.Cubit_Cust_ID = dbo.Cubit_Additional_MI_Data.Cubit_Cust_ID INNER JOIN dbo.Cubit_Override_ID ON dbo.Cubit_Customers.Input_By_NTID = dbo.Cubit_Override_ID.NT_ID LEFT OUTER JOIN dbo.Cubit_Debt ON dbo.Cubit_Customers.Cubit_Cust_ID = dbo.Cubit_Debt.Cust_ID WHERE (dbo.Cubit_Areas.Area_ID IN (2, 3, 4, 11, 12)) AND (dbo.Cubit_Customers.Non_Relevant_Call = 0) AND (dbo.Cubit_Customers.Spending_Assessed = 1) AND (dbo.Cubit_Customers.Debt_Assessed = 1) AND (dbo.Cubit_Debt.Income_Total > 0) AND (dbo.Cubit_EPH.Total_Monthly_Pmt < 999999) AND (dbo.Cubit_Debt.Income_Total - (SELECT SUM(Monthly_Pmt) FROM Cubit_Debt_Card INNER JOIN Cubit_Debt ON Cubit_Debt_Card.Debt_ID = Cubit_Debt.Debt_ID WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) - (SELECT SUM(Monthly_Pmt) FROM Cubit_Debt_Loan INNER JOIN Cubit_Debt ON Cubit_Debt_Loan.Debt_ID = Cubit_Debt.Debt_ID WHERE Cubit_Debt.Cust_ID = Cubit_Customers.Cubit_Cust_ID) - dbo.Cubit_Spending.Out_Total - dbo.Cubit_EPH.Total_Monthly_Pmt < 0) AND (dbo.Cubit_Customers.RecordDateTime >= '04/11/2006') ORDER BY dbo.Cubit_Areas.Area_ID, dbo.Cubit_Override_ID.UserName, dbo.Cubit_Customers.RecordDateTime, Cubit_Customers.Cubit_Cust_ID Thanks! Matt SQL newbie!
View Replies !
Using Distinct And * In Select
Bahrudeen writes "Hi.. hw to use Select query for both distinct and * (eg) select * , distinct(building_id) from g_building where (condition) i want all information with distinct building id.. give a solution advance thanx..."
View Replies !
Select Distinct
Hi. I am trying to create a view where it will find out the sum of hours for each employee, for each month and year. SELECT DISTINCT EmpId, SUM(Hours) AS Hours, YEAR(WeekStartDate) AS startyear, MONTH(WeekStartDate) AS startmonth FROM dbo.BankHours_History GROUP BY EmpId, WeekStartDate
View Replies !
MSSQL - DTS Package - Find Distinct Rows - Output To TXT File - ActiveX
Hello All,I am trying to create a DTS package.I have two tables tbl_A and tbl_B with similar data/rows but noprimary keys.tbl_A is master.I would like this package to query tbl_A and tbl_B and find1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_Athat are not present in tbl_B and3)all rows in tbl_B that are not present in tbl_A, and then just showthose rows.Can this be done with a simple UNION?Perhaps this could produce a temp Table that can be dropped once theDTS package exists successfully.The 2nd part after all the above rows are retrieved is that I wouldlike to add an addional Column to the retrieved data called STATUSwhich has 3 possible values(letters) at the end of each row...M (modified) means that row exists in tbl_B but has 1 or moredifferent columnsA (add) means this row exists in tbl_A but not in tbl_BD (delete) means this row exists in tbl_B but not in tbl_AI'm hopping this DTS package would output a nice comma seperated TXTfile with only...1) rows from tbl_A that are different in tbl_B (STATUS M)2) rows from tbl_A that are not present in tbl_B (STATUS A)3) rows from tbl_B that are not present in tbl_A (STATUS D)Can a DTS package in MS SQL be used to perfom all of the above tasks?I would very much appreciate any help or any advise.Thanks in advance :-)
View Replies !
MSSQL - DTS Package - Find Distinct Rows - Output To TXT File - ActiveX?
Hello All,I am trying to create a DTS package.I have two tables tbl_A and tbl_B with similar data/rows but noprimary keys.tbl_A is master.I would like this package to query tbl_A and tbl_B and find1)all rows in tbl_A that are different in tbl_B, 2)all rows in tbl_Athat are not present in tbl_B and3)all rows in tbl_B that are not present in tbl_A, and then just showthose rows.Can this be done with a simple UNION?Perhaps this could produce a temp Table that can be dropped once theDTS package exists successfully.The 2nd part after all the above rows are retrieved is that I wouldlike to add an addional Column to the retrieved data called STATUSwhich has 3 possible values(letters) at the end of each row...M (modified) means that row exists in tbl_B but has 1 or moredifferent columnsA (add) means this row exists in tbl_A but not in tbl_BD (delete) means this row exists in tbl_B but not in tbl_AI'm hopping this DTS package would output a nice comma seperated TXTfile with only...1) rows from tbl_A that are different in tbl_B (STATUS M)2) rows from tbl_A that are not present in tbl_B (STATUS A)3) rows from tbl_B that are not present in tbl_A (STATUS D)Can a DTS package in MS SQL be used to perfom all of the above tasks?I would very much appreciate any help or any advise.Thanks in advance :-)
View Replies !
How Do I Use Order By When I Use Select Distinct.
Hi I have a query which returns some rows.. what happens if i use a select distinct instead of a select.. this is my sproc DECLARE @Counter TABLE( PlanId int, FundId int, ClientFundName varchar(110), DisplayOrder int IDENTITY(1,1), IsDefault bit, IsPortfolioFundOnly bit ) INSERT INTO @Counter ( PlanId, FundId, ClientFundName, IsDefault, IsPortfolioFundOnly ) SELECT 5923, f.FundId, d.FundName, CASE WHEN d.FundDefault IS NULL THEN 0 ELSE 1 END, CASE WHEN Lower(p.FundType) = 'modfundonly' THEN 1 ELSE 0 END FROM PlanDetail d INNER JOIN Statements..Fund f ON d.CUSIP = f.CUSIP OR d.Ticker = f.Ticker OR d.Ticker = f.ClientFundId OR d.CUSIP = f.ClientFundId -- Do an internal join on the PlanDetail table to get the value of the FundType to derive whether --fund can only be chosen as part of a portfolio. LEFT JOIN PlanDetail p ON d.FundName = p.FundName AND d.PortfolioName = p.PortfolioName WHERE d.PlanNumber IS NOT NULL AND p.PortFundPercent IS NULL GROUP BY f.FundId, d.FundName, d.FundDefault, --d.PlanNumber, --d.Cusip, -- d.Ticker, --d.RowNumber, p.FundType ORDER BY Min(d.PlanNumber), Min(d.RowNumber) any help will be appreciated. Thanks Karen
View Replies !
Select Distinct Records
Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid Description 1 T1 2 T2 So I need the distinct values. How to solve this? Thanks, Miguel
View Replies !
Select Distinct Question
Is there a way to do a Select Distinct on a single column in a result set? Example: Select Distinct(PersonID) PersonID, FirstName, LastName From People
View Replies !
SELECT DISTINCT F1, F2, F3, F4 FROM 'table Name'
Newbie question SELECT DISTINCT F1, F2, F3, F4 FROM 'table name' returns distinct rows for whole table. Is there a way to just return distinct rows from say column F1 instead of all the fields. I suppose i could just do SELECT DISTINCT F1, but also would like to display other fields. Thanks in advance
View Replies !
Distinct In Select Statement
Hey there, is there a way I can use command such as distinct in a select statement to do the following. Lets say I want to do a search of products based off their location and I want to list the companies that will have products in that area. I only want to list the company once, but if I’m searching by products in the area I might come up with 15 results for that company. I have not written the code yet for this, I’m just planning ahead. I’m programming using VB so I guess I would do something like this. State = Trim(Request.QueryString("State")) SelectStatement = "Select * From Products Where State='" & _ State & "'" This would of course give me hypothetically speaking a list as long as the amount of products in one given area. Is there a way to cut this down and only list the company once? Any help would be greatly appreciated. Thanks in advance.
View Replies !
Very Slow Distinct Select
My table looks like this:char(150) HTTP_REF,char(250) HTTP_USER,char(150) REMOTE_ADDR,char(150) REMOTE_HOST,char(150) URL,smalldatetime TIME_STAMPThere are no indexes on this table and there are only 293,658 records total.When I do a select like this it takes forever:SELECT COUNT(DISTINCT REMOTE_ADDR)Takes 2 minutes. Is there anyway to speed that up?Thanks
View Replies !
No Distinct In A Select Into Stement ?
Dear MSSQL experts,I use MSSQL 2000 and encountered a strange problem wqhile I tried touse a select into statement .If I perform the command command below I get only one dataset which hasthe described properties.If I use the same statement in a select into statement (see the secondselect) I get several datasets with the described properties like Ididn't use distinctIs there any posiibility to use destinct in a select into statementselect distinct IDENTITY (int) as ID, Title1 as Title1, Title2 asTitle2, Title3 as Title3,AggregationTitle1 as AggregationTitle1, AggregationTitle2 asAggregationTitle2,AggregationTitle3 as AggregationTitle3, AggregationTitle4 asAggregationTitle4from Variables where Title1 is not NULL or Title2 is not NULL orTitle3 is not NULL orAggregationTitle1 is not NULL or AggregationTitle2 is not NULL orAggregationTitle3 is not NULL or AggregationTitle4 is not NULL;This is the same with select into :select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 asTitle2, Title3 as Title3,AggregationTitle1 as AggregationTitle1, AggregationTitle2 asAggregationTitle2,AggregationTitle3 as AggregationTitle3, AggregationTitle4 asAggregationTitle4into VarTitles from Variables where Title1 is not NULL or Title2 isnot NULL or Title3 is not NULL orAggregationTitle1 is not NULL or AggregationTitle2 is not NULL orAggregationTitle3 is not NULL orAggregationTitle4 is not NULL;Hope anyone can help.Best regards,Daniel WetzlerI
View Replies !
SELECT DISTINCT Problem
Dear GroupI'm having trouble with the clause below. I would like to select onlyrecords with a distinct TransactionDate but somehow it still listsduplicates. I need to select the TransactionDate once as smalldatetime andonce as varchar as I'm populating a drop-down with Text/Value pairs. So Ican't just use 'SELECT DISTINCT TransactionDate FROM...'I'm grateful for any hints.SELECT DISTINCT (TransactionDate), CONVERT(varchar(10),TransactionDate,104)AS LabelTransactionDate FROM i2b_keytransactionlog WHERE ProgClientID =@ProgClientID ORDER BY TransactionDate ASCThanks for your time & efforts!Martin
View Replies !
SELECT DISTINCT With JOIN
Hi everyoneHave a problem I would areally appreciate help with.I have 3 tables in a standard format for a Bookshop, egProductsCategoriesCategories_Productsthe latter allowing me to have products in multiple categories.Everthing works well except for one annoying little thing.When an individual product (which is in more than one topcategory) is addedto the Shopping Cart it displays twice, because in my select statement Ihave the Category listed. I realise I could remove the TopCategory from thestatement and that makes my DISTINCT work as I wanted, but Id prefer to havethe TopCategory as it saves me later having to another SQL query (Im alreadydoing one to allow me not to list category in the Statement .... but If Ican overcome this one ... then I can remove this as well).Here is my table structure (the necessary bits)productsidProduct int....categoriesidcategory intidParentCategory inttopcategory int...categories_productsidCatProd intidProduct intidCategoryWhen I run a query such asSELECT DISTINCT a.idProduct, a.description,a.descriptionLong,a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,c.topcategoryFROM products a, categories_products b, categories cWHERE active = -1 AND homePage = -1AND a.idProduct = b.idProductAND c.idcategory=b.idcategoryAND prodType = 1 ORDER BY a.idProduct DESCThis will return all products as expected, as well as any products which arein more than one TopCategory.Any ideas how to overcome this would be greatly appreciated.CheersCraig
View Replies !
SQL Syntax For Distinct Select
I'm trying to order a varchar column first numerically, and secondalphanumerically using the following SQL:SELECT distinct doc_numberFROM doc_lineWHERE product_id = 'WD' AND doc_type = 'O'ORDER BY CASE WHEN IsNumeric(doc_number) = 1THEN CONVERT(FLOAT, doc_number)ELSE 999999999END,CASE WHEN IsNumeric(doc_number) = 1THEN 'ZZZZZZZZZ'ELSE doc_numberEND;When try executing this statement, I get the following error:Server: Msg 145, Level 15, State 1, Line 1ORDER BY items must appear in the select list if SELECT DISTINCT isspecified.If I take the "distinct" out, it works just fine, except for the fact that Iget many duplicates.Does anyone have any suggestions?Thanks,Frank
View Replies !
SELECT Distinct Problem - HELP!!!!
I'm trying to select a recordset from a table without getting duplicates on only one column and can't figure out how to do it. Here is the table structure: Tablename: ev_textmessageusers Columns: id (unique int) | clientid (int) | email (nvarchar) | groups (nvarchar) | datecreated (datetime) What i'd like to do is: SELECT DISTINCT(email), [and then the other columns - not distinct] FROM ev_textmessageusers WHERE clientID = 1 But this obviously can not be done. I've tried to do it with a GROUP BY clause and I can't get that to work either. The groups and datecreated columns may or may not be unique - but I still want to get their values returned in the recordset. Thanks for your help!!!
View Replies !
|
TRACKER
