Difficult Select Distinct Query
Jul 20, 2005
Hi,
I have a table as following
aa Text1 aa, Join Bytes!, 15267
aa Text1 aa, Join Bytes!, 16598
aa Text1 aa, Join Bytes!, 17568
aa Text2 aa, Join Bytes!, 25698
aa Text3 aa, Join Bytes!, 12258
I have to write a query as follows ...
SELECT DISTINCT TOP 500 fldText, fldContact, fldItemid
FROM table
WHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')
In the example you can see the table has rows in which text and contact or
double but with different itemid's. Now my employer wants me to show only 1
row when text and contact or the same. He doesn't mind which itemid I show
.... but I have to show one.
I've an idea of how to do this using a cursor and a temporary table but I
guess that will be fatal for the performance because then I have to loop
through all selected rows, check each row with all other rows and store the
primary key in the temporary table if dedected it isn't double. Afterwards
I can execute ... SELECT ... FROM TABLE where primary key in (select
temp_primarykey from #temptable).
I hoped I could do everything in 1 "easy" SELECT but I should not know how?
Any ideas are much appreciated.
Thanks a lot.
Perre Van Wilrijk.
View 1 Replies
ADVERTISEMENT
Nov 9, 2006
when i use select :
select flattened
(select productid, $support from [model name].[table] )......
i have result with many record same.
so i write :
select flattened
(select distinct productid, $support from [model name].[table] )......
but when i run, it's error. ( don't know what error).
how can i do to get table record with not same record (loop)
note : it write in DTS and i use sql 2000
View 4 Replies
View Related
Feb 6, 2008
Hello, I want to do a DISTINCT SELECT query with more than one field, for example a ID field with a Type field, as if both fields make the primary key, like (ID 1 ,Type 1) ,( ID 1, Type 2) and (ID 2, Type 1) is ok but not (ID 1, Type1) and (ID 1,Type 1) if its not possible to do a distinct with more than one then what other techniques are possible to get the duplicate data out. the reason why I want to use distinct is that I can use that query to export that data to where both of these fields make the primary key.
Thanks in advance
View 5 Replies
View Related
Oct 26, 2006
I need to return the current case cost for every UPC in my table. In my current query I return case costs that have an effective date of today or earlier. The problem is that in my results, one UPC may have two or more case costs that were are effective <= GETDATE(). I can sort it by effective date (DESC) so I know the first of every UPC in my results will be the current effective case cost, but how do I modify my query so that in my result set I only get the first of every UPC?
Here is my query:
SELECT factCaseCosts.nUpcKey, factCaseCosts.dCaseCost, factCaseCosts.dtEffectiveDate
FROM factCaseCosts
WHERE (factCaseCosts.dtEffectiveDate <= GETDATE())
ORDER BY dtEffectiveDate DESC
Here is my current result set:
52023.762006-08-01 00:00:00
52023.762006-02-18 00:00:00
52123.762006-08-01 00:00:00
52123.762006-02-18 00:00:00
52230.362006-08-01 00:00:00
52230.362006-02-18 00:00:00
52323.762006-08-01 00:00:00
52323.762006-02-18 00:00:00
I only want the first 520 returned, the first 521 returned, the first 522 returned, and the first 523 returned.
How can I do this?
Thanks!
View 3 Replies
View Related
Feb 18, 2015
This is part of a query I have. I want to display the diagnosis code attached to a patient. Sometimes there are more than one which is why I have tried distinct. I am getting the message 'subquery returned more than 1 value...'
If I use MIN or MAX, its giving me the min possible diagnosis code alphabetically or the max, not the one actually assigned to the patient which is what I want.
(SELECT DISTINCT (Diagnosis.DiagnosisCode) FROM Diagnosis
LEFT OUTER JOIN CourseDiagnosis ON CourseDiagnosis.DiagnosisSer = Diagnosis.DiagnosisSer
LEFT JOIN Course ON Course.CourseSer = CourseDiagnosis.CourseSer
JOIN Patient ON Patient.PatientSer = Course.PatientSer
WHERE Diagnosis.ObjectStatus = 'Active' AND
Diagnosis.Description not like 'ERROR%' AND
Diagnosis.DiagnosisTableName not in ('RTDS Dates')),
View 10 Replies
View Related
Dec 7, 2006
Hello,
I'm reasonably new to SQL.
I have a table of information about meetings with various people. In it, I have the person's name and the date/time of the meeting (simplified example).
I need to write a query that SELECTs only the most recent meeting for each person.
Any ideas would be greatly appreciated.
View 2 Replies
View Related
Jul 7, 2007
Hi all,
i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.
sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.
i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.
Alon.
View 4 Replies
View Related
Mar 22, 2008
Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma
View 10 Replies
View Related
Jul 6, 2007
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 2 Replies
View Related
Aug 20, 2015
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
View 6 Replies
View Related
May 29, 2008
Hey i have a query i need help with.
I have a table where i have 4 columns in it which i need to group together and then sum up a cost column also. I want to sum up the columns where i have a parent and and child and then i want to sum up the other column where i have only a child.
Example of the data is below. I think i need to do this in a sub query
ID Ind Parent Child Cost
P110041012705921.8000
W11004101270595.4500
A110041012705921.8000
B110041012705916.3500
R110041012705916.3500
B0100420043.3000
P0100420043.3000
W0100420021.6500
View 2 Replies
View Related
Jul 20, 2005
I have a table that stores billing rates for our employees by client.Each employee can have a different billing rate for each client for aspecified period. Here are the columns in the table.eid - Employee ID#cid - Client ID#startdt - start date of billing rateenddt - end date of billing ratebrate - billing rateI need to create a script that will verify that for a given eid, and cidthat either the startdt or enddt for one billing rate, the periods donot overlap.For example, I need to be able to detect overlaps such as this:eid cid startdt enddt brate001 001 1/1/2003 12/31/2003 $50001 001 11/01/2003 04/01/2004 $75*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jul 20, 2005
suppose I have the following table:CREATE TABLE (int level, color varchar, length int, width int, heightint)It has the following rows1, "RED", 8, 10, 122, NULL, NULL, NULL, 203, NULL, 9, 82, 254, "BLUE", NULL, 67, NULL5, "GRAY", NULL NULL, NULLI want to write a query that will return me a view collapsed from"bottom-to-top" in order of level (level 1 is top, level 5 is bottom)So I want a query that will returnGRAY, 9, 67, 25The principle is that looking from the bottom level up in each columnwe first see GRAY for color, 9 for length, 67 for width, 25 forheight. In other words, any non-NULL row in a lower level overridesthe value set at a higher level.Is this possible in SQL without using stored procedures?Thanks!- Robert
View 22 Replies
View Related
Nov 13, 2006
Hi.
I am developing for a system that receives an input from an external modem.
The Transaction is split into 2 sections,
Section 1 = grants the transaction ID,
Section 2 = deliver the transaction Data.
I have 2 corresponding tables,
One called tblremoteunitrequestID (Where the transaction ID is granted)
The other called tblremoteunitrequests (Where the transaction is completed, about 1 second later)
I am writing a diagnostic report that determines if the first part of the transaction completes but the second part fails.
I am having difficulties designing the SQL for this.
Here is some sample data for tblremoteunitrequestID: (The first stage of the transaction)
RecordDate | Serial
13/11/2006 14:00:36 0000-0000-0000-0006
13/11/2006 14:00:30 0000-0000-0000-0004
13/11/2006 13:59:04 0000-0000-0000-0092 (This didtn transaction didnt complete)
13/11/2006 12:15:22 0000-0000-0000-0092 (nor did this one)
13/11/2006 10:31:54 0000-0000-0000-0092
13/11/2006 10:00:29 0000-0000-0000-0006
Here is some sample data for tblremoteunitrequests: (The second stage of transaction, 1st stage has to be completed beforehand)
DateReceived | Serial
13/11/2006 14:00:37 0000-0000-0000-0006
13/11/2006 14:00:31 0000-0000-0000-0004
13/11/2006 10:31:56 0000-0000-0000-0092
13/11/2006 10:00:31 0000-0000-0000-0006
13/11/2006 10:00:25 0000-0000-0000-0004
13/11/2006 07:19:13 0000-0000-0000-0020
From this data I can see that serial number 0000-0000-0000-0006 Successfully completed part 1 and part 2 of the transaction, as did serial number 0000-0000-0000-0004.
Serial number 0000-0000-0000-0092 had trouble, it connected at 13:59:04 (tblremoteunitrequestID) but part 2 didnt complete, so it wasent saved in tblremoteunitrequests. The same happened at 12:15:22 but at 10:31:54 it was successful so it was saved.
I Only want to display the transactions that didnt complete, sounds easy huh?
This is what I hope to get in my Results table:
DateReceived | Serial
13/11/2006 13:59:04 0000-0000-0000-0092
13/11/2006 12:15:22 0000-0000-0000-0092
I was experimenting with T-SQL today, this is what I have done so far:
SELECT DISTINCT
TBLRemoteFeildUnitRequestID.Serial, TBLRemoteFeildUnitRequestID.RecordDate,
CASE WHEN TBLRemoteUnitRequests.DateReceived BETWEEN DATEADD(SECOND,-1,TBLRemoteFeildUnitRequestID.RecordDate) AND DATEADD(SECOND,10,TBLRemoteFeildUnitRequestID.RecordDate)
THEN ' Ok'
ELSE ' Not ok'
END AS PROBLEM
FROM TBLRemoteFeildUnitRequestID LEFT OUTER JOIN
TBLRemoteUnitRequests ON TBLRemoteFeildUnitRequestID.Serial = TBLRemoteUnitRequests.Serial
WHERE TBLRemoteFeildUnitRequestID.RecordDate BETWEEN DATEADD(WEEK, - 2, GetDate()) AND GetDate()
ORDER BY RecordDate DESC
This kinda worked, but it caused records that satisfied the between condition to be displayed twice, once as "Ok" and once as "Not ok".
Heres a sample of the result I got:
Serial | RecordDate (1st part of transaction) | Status
0000-0000-0000-0006 2006-11-13 14:00:36.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 14:00:36.000 Not ok
0000-0000-0000-0004 2006-11-13 14:00:30.000 Not ok (Duplicated)
0000-0000-0000-0004 2006-11-13 14:00:30.000 Ok
0000-0000-0000-0092 2006-11-13 13:59:04.000 Not ok (Correct) (Not duplicated)
0000-0000-0000-0092 2006-11-13 12:15:22.000 Not ok (Correct) (Not Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Not ok (Duplicated)
0000-0000-0000-0092 2006-11-13 10:31:54.000 Ok
0000-0000-0000-0006 2006-11-13 10:00:29.000 Ok (Duplicated)
0000-0000-0000-0006 2006-11-13 10:00:29.000 Not ok
I have just about had enough, I have wasted an entire day on this
Someone please Help
Dan
View 4 Replies
View Related
Jul 6, 2005
hello,
I could need some help with a little query.
table "acme"
name1 varchar(128)
name2 varchar(128)
idate datetime
content
A,H,1/1/2005
A,H,2/1/2005
A,I,2/1/2005
A,J,3/1/2005
B,K,4/1/2005
B,L,5/1/2005
I want the following result (for 'A'):
1/1/2005,1
2/1/2005,3
3/1/2005,4
I want to filter for Column "Name1" and cumulative count the entries grouped by date.
what's the simplest solution?
best regards, thilo.
View 4 Replies
View Related
Sep 20, 2007
I'm writing a workflow management application for my work, and its somewhat complicated, here's a general idea of how it works:
- Anything that a company does is defined by a workflow.
- A workflow consists of tasks.
- Some tasks in a workflow can't be started until other tasks have been completed. If task A can't be started until tasks B and C are finished, then task A depends on B and C.
You might imagine that a bank has a workflow for handling a house loan. Before a bank could sign a contract with an applicant, they'd need proof of house ownership, but before they could get proof of house ownership they need an applicant's proof of identity like a driver's license or military ID.
Here's an oversimplified visual:
Each arrow points to its dependency. Each task can have multiple dependencies.
The setup above is represented in the database by a Tasks and a Dependencies table. Tasks has an ID field, and Dependencies has a TaskID and DependencyID field which are both foreign keys to Tasks.ID.
Code:
[Tasks]
ID Status Name
-- ------ ----
1 Done Start Processing Loan Application
2 Done Photocopy applicant's driver's license
3 NotDone Photocopy proof of house ownership
4 NotDone Get a copy of applicant's W-2 forms
5 NotDone Perform credit check on applicant
6 NotDone Sign loan contract
[Dependencies]
TaskID DependencyID
------ ------------
1 0
2 1
3 1
4 2
5 2
5 3
6 4
6 5
Tasks has a many-to-many relationship with itself.
Here's the hard part:
- A task can't be started until all of its dependencies have been completed.
- after a task is completed (meanings its status is marked "done"), I need to return a list of all the new tasks that are ready to be started.
When TaskID 2 is marked "Done", then TaskID 4 is ready to begin; however, TaskID 5 is not ready to begin since it depends on 2 and 3, and 3 hasn't been completed yet.
The requirements of the query are very simple, but the implementation is difficult.
I'll post a prelimenary solution in the next post:
View 1 Replies
View Related
Nov 23, 2005
Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.
View 5 Replies
View Related
Aug 18, 2007
GO
CREATE TABLE [dbo].[Product]
(
[ProductId] [smallint] IDENTITY(1,1) NOT NULL CONSTRAINT PkProduct_ProductId PRIMARY KEY,
[Name] [varchar](52) NOT NULL,
[Type] [smallint] NOT NULL,
)
For this table
I have to write the querywhich willget the TOP 1 Row of each Type.
I know the alternate way of doing this by union.
But this is not professional.
Can anyone resolve this issue?
View 2 Replies
View Related
Jan 7, 2008
If you know the answer please explain what you're doing if possible, that'll help me :)I have the following tables:CREATE TABLE [dbo].[tblUserData]( [UserCode] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY]CREATE TABLE [dbo].[tblFriends]( [UserCodeOwner] [int] NOT NULL, [UserCodeFriend] [int] NOT NULL, [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate] DEFAULT (getdate())) ON [PRIMARY]in tblFriends relations are stored twice, so for a relation between user 5 and 6, there will be 2 rows: 5-6 and 6-5Now, I want to get the columns (UsercodeOwner,UsercodeFriend,createdate,username,displayname) for relations that were created in tblFriends in the last 10 days for the FRIENDS of a person with usercode 5.Example:tblUserdata5 peter Petertje6 john Johnny11 simon SimonSays15 monique MontjetblFriends5 6 'createdate 30 days ago'5 11 'createdate 5 days ago'6 5 'createdate 30 days ago'6 11 'createdate 3 days ago'6 15 'createdate 7 days ago'11 5 'createdate 5 days ago'11 6 'createdate 3 days ago'15 6 'createdate 7 days ago'The resultset for a query on usercode 5 would now be (usercode1, username1, displayname1,usercode2, username2, displayname2,createdate):6 john Johnny 11 simon SimonSays 'createdate 3 days ago'6 john Johnny 15 monique Montje 'createdate 7 days ago'As you can see each relation is only returned twice even though there are always two entriesWhat would be the SQL statement, if possible without temp table..Thanks!
View 21 Replies
View Related
Jul 20, 2005
Hi All,I have what seems to me to be a difficult query request for a databaseI've inherited.I have a table that has a varchar(2000) column that is used to storesystem and user messages from an on-line ordering system.For some reason (I have no idea why), when the original database wasbeing designed no thought was given to putting these messages inanother table, one row per message, and I've now been asked to providesome stats on the contents of this field across the recordset.A pseudo example of the table would be:custrep, orderid, orderdate, comments1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer askedfor a brown model2, 10002, 2004-04-12, :Comment 3:Comment 4:1, 10003, 2004-04-12, :Comment 2:Comment 8:2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled orderSo, what I've been asked to provide is something like this:orderdate, custrep, syscomment, countofsyscomments2004-04-12, 1, Comment 1, 12004-04-12, 1, Comment 2, 22004-04-12, 1, Comment 3, 12004-04-12, 1, Comment 8, 12004-04-12, 2, Comment 1, 12004-04-12, 2, Comment 3, 12004-04-12, 2, Comment 4, 22004-04-12, 2, Comment 6, 22004-04-12, 2, Comment 7, 1I have a table in which each of the system comments are defined.Anything else appearing in the column is treated as a user comment.Does anyone have any thoughts on how this could be achieved? The endresult will end up in an SQL Server 2000 stored procedure which willbe called from an ASP page to provide order taking stats.Any help will be humbly and immensely appreciated!Much warmth,Murray
View 7 Replies
View Related
Mar 12, 2007
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
View 2 Replies
View Related
Oct 16, 2006
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 5 Replies
View Related
Apr 19, 2007
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 4 Replies
View Related
Apr 8, 2008
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 3 Replies
View Related
May 31, 2008
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 3 Replies
View Related
Jun 25, 2001
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 1 Replies
View Related
Feb 15, 2000
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 2 Replies
View Related
Aug 5, 2004
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 3 Replies
View Related
Jan 19, 2005
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 3 Replies
View Related
Oct 26, 2006
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 13 Replies
View Related
Mar 2, 2006
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 3 Replies
View Related
May 10, 2006
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 1 Replies
View Related
Oct 16, 2006
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 8 Replies
View Related