Query Help - Select Distinct Possibly?

DISTINCT SELECT Query With More Than One Field
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 Replies !

---

Difficult Select Distinct Query
Hi,I have a table as followingaa Text1 aa, Join Bytes!, 15267aa Text1 aa, Join Bytes!, 16598aa Text1 aa, Join Bytes!, 17568aa Text2 aa, Join Bytes!, 25698aa Text3 aa, Join Bytes!, 12258I have to write a query as follows ...SELECT DISTINCT TOP 500 fldText, fldContact, fldItemidFROM tableWHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')In the example you can see the table has rows in which text and contact ordouble but with different itemid's. Now my employer wants me to show only 1row 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 Iguess that will be fatal for the performance because then I have to loopthrough all selected rows, check each row with all other rows and store theprimary key in the temporary table if dedected it isn't double. AfterwardsI can execute ... SELECT ... FROM TABLE where primary key in (selecttemp_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 Replies !

---

SELECT Distinct Query Problem
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 Replies !

---

How To Use ORDER BY Clause In An SELECT DISTINCT Sql Query When AS SINGLECOLUMN Is Defined?
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 Replies !

---

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 !

---

Possibly Extremely Simple SQL Query
Right, I'm no SQL programmer. As I type this, I have roughly the third the hair I had at 5 o'clock last night. I even lost sleep over it.

I'm trying to return a list of records from a database holding organisation names. As I've built a table to hold record versions, the key fields (with sample data) from a View I created to display this is as follows:

record_id-----org_id-----live-----version
====== ===== === =====
1-------------1----------0----------1
2-------------2----------0----------1
3-------------1----------1----------2
4-------------2----------0----------2

as you can see the record id will always be unique. record 3 is a newer version of record 1, and 4 of 2. the issue is thus: i only want to return unique organisations. if a version of the organisation record is live on the system (in this case record id 3), i want to return the live version with its unique record id. i'm assuming for this i can perform a simple "SELECT WHERE live = 1" query.

however, some organisations will have no live versions (see org with id 2). i still wish to return a record for this organisation, but in this case the most recent version ie version 2 (and again - its unique record id)

in actual fact, it seems so much clearer when laid out like this. however, i feel it's not going to happen this end, and so any help would be greatly appreciated.

many thanks in advance,

phil

View Replies !

---

Possibly Incorrect Query Result
/* Test table */
create table test (c1 char(1), c2 varchar(1));
insert into test values ('','');

/* Query */
select
  c1,
  len(c1) len_c1,
  c2,
  len(c2) len_c2
from test


The result of the len(c1) expression is 0. I would expect the correct result to be 1, since "c1" is a fixed-length character string type and the values are right-padded with spaces to fit the defined length, in this case 1.

I'm using SQL Server 2005.

Regards,
Ole Willy Tuv

View Replies !

---

Possibly Simple Query But I'm Not Good Enough To Fathom It!
Hi AllI know that I should supply the DDL for the tables I'm going to talk about,but I'm not 100% on how to generate them just yet. Hopefully my question ismore a query methodology question than how the tables are constructed.My first attempt at the query is as follows:SELECT st.STOCKID, sd.FULLDESCRIPTION, sc.NAME, Sum(sq.QUANTITYINSTOCK) AS'Qty In Stock Total', Sum(st.QUANTITY) AS 'Qty Sold'FROM STOCK s, STOCKCATEGORIES sc, STOCKDESCRIPTIONS sd, STOCKQUANTITIES sq,STOCKTRANSACTIONS stWHERE sc.STOCKCATEGORYID = s.STOCKCATEGORYID AND st.STOCKID = s.STOCKID ANDsd.STOCKID = s.STOCKIDAND sq.STOCKID = s.STOCKID AND (sd.LANGUAGEID='UK') AND(st.TRANSACTIONTYPE=8) AND(sq.QUANTITYINSTOCK > 0)GROUP BY st.STOCKID, sd.FULLDESCRIPTION, sc.NAMEThis works in a fashion, but I need to sort of query the stocktransactionstable again to get the sum of the st.QUANTITY table for st.TRANSACTIONTYPE=1so that I can have an extra select field of Sum(st.QUANTITY) AS 'QtyOrdered', ie transactions with transactiontype of 1 are sales orders andtype 8 are invoices.I basically need to get a report result of:PRODUCT, DESCRIPTION, CATEGORY, CURRENT_STOCK_QTY, SALES_IN_PERIOD,ORDERS_IN_PERIODIs there any pointers whatsoever you can give me to try and get thisdouble-double query to work?Many thanks.Rgds Laphan

View Replies !

---

Trying To Add A NON-DISTINCT Field To A DISTINCT Record Set In A Query.
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 Replies !

---

&&"distinct&&" In Select Query
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 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 !

---

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 [name] From Merchantcategory
hi all,
i wanna ask a question regarding select distinct statement.....
this is my sql query : select distinct [name] from merchantcategory
how can i get the other attribute from the result of the query ???
is this possible ?
thanks

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 !

---

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

---

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 !

---

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 !

---

SELECT DISTINCT W/ORDER BY
I get the "ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

SELECT DISTINCT
pdm.Account,
pdm.Customer
FROM
dbo.Demands pdm LEFT OUTER JOIN
dbo.Tickets rct ON pdm.Account = rct.Account
WHERE
pdm.Code IN (66, 51)
ORDER BY
pdm.TransactionDate DESC

Is there any way to make the ORDER BY work in this case?

View Replies !

---

Performance Hit If I Use SELECT DISTINCT?
We use an ASP/MS SQL 2000 system to send out our mass e-mailing to about 3,500 subscribers (and the list is growing). There are some duplicate entries in the DB and I was thinking about using this code


SELECT DISTINCT email FROM Subscribers


to remove the duplicates (at least until we can get around to cleaning up the data and then putting up new subscriber form to prevent duplicate entries).

I was wondering, though... Will this have a significant impact on our performance? I mean, that's a lot of e-mail addresses to process and I don't want to bog our system down unnecessarily.

What do you performance gurus think?

View Replies !

---

SELECT COUNT Of DISTINCT
helloi want to do only one query for :SELECT DISTINCT Name FROM UsersSELECT COUNT(Name) AS Names FROM Users WHERE (Name LIKE 'xxx')something like :SELECT Name, COUNT(Name) AS Names FROM Users WHERE Name IN (SELECT DISTINCT Name FROM Users)i must get :Joe 23julie 17.....thank you

View Replies !

---

SELECT DISTINCT In DECLARE
Hi


I try to use this code in query analyzer

DECLARE @SendTo VarChar(4000)
SET @SendTo = ''
SELECT DISTINCT @SendTo = @SendTo + UserEmail + ';' FROM dbo.tbl_AccountInfo WHERE (UserEmail <> '')
PRINT @SendTo

The purpose of this code is to build up a ; seperated string of email adresses that I can use sending mail from SQL server.

It works but it only give me one record (should give me 130 records) , but if I remove the DISTINCT part it give me all records, duplicates too. Does anyone know why and how can I get this to work? Or maybe do it in another way?


Best regards

View Replies !

---

Select Distinct With Nvarchar
I have been trying to run a query using select distinct(column_name) on a column that is an nvarchar but the query still gives me all of the items including duplicates. I have removing any trailing spaces and still no good. Any ideas would be appreciated.

View Replies !

---