SELECT DISTINCT In DECLARE

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 !

---

Declare Inside Select Statement?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query.

So! Here's the statement that works:

======================
DECLARE @fixeddate datetime
DECLARE @CVG_ELECT char(1)
DECLARE @Effdt datetime
DECLARE EFFDTS CURSOR FOR
SELECT Z.EFFDT, COVERAGE_ELECT
FROM PS_LIFE_ADD_BEN Z
WHERE Z.EMPLID = '1000'
AND Z.EFFDT <=
GETDATE()
AND Z.PLAN_TYPE = '20'
ORDER BY Z.EFFDT DESC
OPEN EFFDTS
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
WHILE @@FETCH_STATUS = 0
BEGIN
if @CVG_ELECT <> 'E'
break
ELSE
SET @fixeddate = @Effdt
FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT
END

CLOSE EFFDTS
DEALLOCATE EFFDTS
PRINT @fixeddate

======================
If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea).

Is it possible to encapsulate this inside a select statement?

View Replies !

---

Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile...


Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.

However the select statement itself runs perfectly well as a query, no errors.

The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.

What gives with this?

Thanks in advance
R.

The code:




Code Snippet

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
    DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author:        -----
-- Create date:
-- Description:    inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
    -- Add the parameters for the stored procedure here
    @importedReportID int,
    @authCode varchar(12)
AS
BEGIN
    DECLARE @errmsg VARCHAR(80);

--    SET NOCOUNT ON added to prevent extra result sets from
--     interfering with SELECT statements.
    SET NOCOUNT ON;

    --IF (@authCode <> 'TX-TEC')
    --BEGIN
     --   SET @errmsg = 'Unsupported reporting format:' + @authCode
      --  RAISERROR(@errmsg, 11, 1);
    --END

    DECLARE srcRecsCursor CURSOR LOCAL
    FOR    (SELECT
           ImportedRecordID
          ,ImportedReportID
          ,AuthorityCode
          ,[ID]
          ,[Field1] AS RecordType
          ,[Field2] AS FormType
          ,[Field3] AS ItemID
          ,[Field4] AS EntityCode
          ,[Field5] AS LastName
          ,[Field6] AS FirstMiddleNames
          ,[Field7] AS Title
          ,[Field8] AS Suffix
          ,[Field9] AS AddressLine1
          ,[Field10] AS AddressLine2
          ,[Field11] AS City
          ,[Field12] AS [State]
          ,[Field13] AS ZipFull
          ,[Field14] AS OutOfStatePAC
          ,[Field15] AS FecID
          ,[Field16] AS Date
          ,[Field17] AS Amount
          ,[Field18] AS [Description]
          ,[Field19] AS Employer
          ,[Field20] AS Occupation
          ,[Field21] AS AttorneyJob
          ,[Field22] AS SpouseEmployer
          ,[Field23] As ChildParentEmployer1
          ,[Field24] AS ChildParentEmployer2
          ,[Field25] AS InKindTravel
          ,[Field26] AS TravellerLastName
          ,[Field27] AS TravellerFirstMiddleNames
          ,[Field28] AS TravellerTitle
          ,[Field29] AS TravellerSuffix
          ,[Field30] AS TravelMode
          ,[Field31] As DptCity
          ,[Field32] AS DptDate
          ,[Field33] AS ArvCity
          ,[Field34] AS ArvDate
          ,[Field35] AS TravelPurpose
          ,[Field36] AS TravelRecordBackReference
      FROM ImportedNativeRecords
      WHERE ImportedReportID IS NOT NULL
      AND ReportType IN ('RCPT','PLDG')
     ORDER BY ImportedRecordID  -- this should work but gives syntax error!
    );

END

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

---

After Union Select Distinct
That's about it (subject line), I have used UNION and now I want to select DISCTINCT from that resultset
Code:

SELECT c1 FROM t1
UNION
SELECT c2 FROM t2

That code gives me half of what I want.
I would like a list of the unique results.
I know I could use a TempTable and do the DISTINCT on that, but I'm hoping there is a more elegant way.


EDIT: The following code gives me the result I want:
Code:

CREATE TABLE #TempTable (
TempCol VARCHAR (20) collate database_default,
)

INSERT INTO #TempTable
SELECT c1 FROM t1
UNION
SELECT c2 FROM t2

SELECT * FROM #TempTable

DROP TABLE #TempTable

. . . but, can it be done without a TempTable??

View Replies !

---

SELECT DISTINCT Subquery
I the following table:

table1
member_name legacy_id team_name
-----------------------------------------
Bill 1234 nationals
Bill 1234 nationals
Tom 3456 nationals
Tom 3456 orioles

I wish I could restructure the data or normalize it but this is unfortunately what I have to deal with.

I need a query that returns the team name and the number of times it appears in the table excluding duplicates for each person. I have duplicates all over the place in this tables. Bill could have nationals listed a couple hundred times.

My query should return

team_name count
-----------------
nationals 2 - because it occurs for bill, and tom
orioles 1 - because it occurs for tom


If I do something like:

select
distinct(team_name),
count(team_name)
from table1
group by team_name

I get back:

team_name count
-------------------
nationals 3 - because it occurs for bill twice, and tom once
orioles 1 - because it occurs for tom once


I've tried something like:

select
team_name,
count(team_name)
from
table1
where legacy_id in (
select distinct legacy_id from table1
)

I get a syntax error. Regardless, I'm not sure this will give me what I need.

I've tried over a dozen variations of select distinct, joins, etc but with no luck.

Any of you sql gurus know how to solve this problem? I've been banging my head against it for a couple days and boy does my head hurt.

View Replies !

---

SELECT DISTINCT On One Column
I know this is a popular problem, but i haven't found an answer to my situation.
I have a table (myTable) with three Columns (Column1, Column2, Column3). Column1 entries are unique (with unique identifier), Column2 entries are not unique.
I want to do a SELECT DISTINCT on myTable so that Column2 is unique and i get all the other columns. I thought something like this would work

SELECT DISTINCT  Column2 as Col2, newid() as Col1, Column3 as Col4
-- I am getting uniqueidentifier for each row as well

This doesn't seem to work however. I still get all the rows because Col1 is unique.
since i am using aliases, the usual syntax doesn't work either.

any help would be greatly appreciated!

View Replies !

---

How To Select Distinct Row By Col1 ?
HI

Please help I didn't get right solution for this problem. So I am posting again with full details.
Scenario

col1---col2------col3----col4-----col5------col5
123-----AB--------WE-----Name------Add------Prod1
123-----AB--------DC-----Name------Add------Pro512
123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

How do I select Distinct row by col1 from the above scenario. Expected result will be

123-----AB--------WE-----Name------Add------Prod1
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

OR

123-----AB--------DC-----Name------Add------Pro512
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

OR

123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78


Please Help.

View Replies !

---

How To Select Distinct Row From A Table ?
Hi

I have a table with col1,col2,....upto col30

Col1 may have duplicate string like below

Col1-----------------Col2
202-345567-456677 Robert
202-345567-456677 Robert
020-456789-892543 Phil

How do I select DISTINCT ROW from the table?

SELECT DISTINCT COL1,COl2..... FROM tbl - Seems not working.

View Replies !

---

Problems With Select Distinct
I am having this problem i have a table when i try to select a distict value from model and inclued feilds link & Tariff i get a duplicats due to the fact that the model is available on diffrent tariffs.

i have a view which has all the models in it which was created using the select distinct and i get no dups only when i add link and tariff do i get the dups, their are other feilds that also create this prob

what i want is to select the first model keep that as distinct and add the other information without dups i have try most things including creating the view and the using dreamweaver to to filter the dups out but that had no luck

Table info highlighted fieds have information that cause the dups
dbo.###.ProductID, dbo.###.Category, dbo.###.Manufacturer, dbo.###.Colour,
dbo.###.Handset_Description, dbo.###.Tariff_Minutes, dbo.###.Tariff_Texts, dbo.###.Tariff_Cost, dbo.###.Network,
dbo.###.Phone_Price, dbo.###.Promotional_Header, dbo.###.Cashback_amount, dbo.###.Promotional_Text,
dbo.###.Image_URL, dbo.###.Dp_link_URL, dbo.###.Add_To_Basket_URL, dbo.###.Model,

any advice


Cyber Cauldron

View Replies !

---

Question Using SELECT DISTINCT?
How can I go about getting a result set that is distinct on only one field and still get the information from the other fields without looping:

If I have a table with
primary_key, first_name,last_name

1,joe,smith
2,bob,smith
3,fred,smith
4,joe,green
5,fred,green

I want to do something like

SELECT (DISTINCT last_name),first_name,primary_key FROM table

I want a way to get the DISTINCT to only look at the last_name field not all fields when filtering the results but still create a record set that contains the last_name, first_name and the primary key?

in the example above I want a results set like this

primary_key, first_name,last_name

1,joe,smith
4,joe,green

thanks.

Thankx

Kervin.
kervin_findlay@hotmail.com

View Replies !

---

Distinct Select Options
Hi, I am new to this forum.

I have a question regarding select queries.

I need to to a select on a distinct field but need to output more that that field.

so if I have

select name, company, address, location_number from table abc

the location_number is the only field that has to be one per on output.

table abc will have

Alan Andrews abc company 1 Main st 00001
John Andrews abc company 1 Main st 00001
bob Smith ZZX Inc 3 broadway 00001
eric Roberts SSS, LLC 123 Elm ST 00002
Rick Roberts SSS, LLC 123 Elm ST 00002
Jay Smith JJJ Inc 524 5th ave 00002

shold result to
Alan Andrews abc company 1 Main st 00001
eric Roberts SSS, LLC 123 Elm ST 00002

Is there a way to do this?

when I say:

select distinct name, company, address, location_number from table abc

I still get all the records back because its doing a distinct across all the fields I am selecting.

Thanks!

Alan

View Replies !

---