DISTINCT Doesn't Seem To Be Working
Sep 28, 2006
Hi all,
I am using SQLEXPRESS 2005 and can't figure out why my SQL query is not working.Select DISTINCT ads.clientid, ads.ad, ads.adid, ads.cost, ads.description, ads.bold, ads.info,
ads.contact, ads.coupon, ads.logo, ads.map, ads.webpage, ads.weburl, tclients.clientname,
tclients.clientphone, tclients.addNumber, tclients.addStreet, tclients.addsuite, tclients.addcity,
tclients.addstate, tclients.addzip from tclients, ads where tclients.clientid=ads.clientid
AND AdActive=1 ORDER BY COST DESC
I expect only one row to be returned for each ads.clientid. However, every single record that matches the query is being returned, which results in multiple records with the same clientid.
This happens if it is ads.clientid or tclients.clientid.
Martin
View 5 Replies
ADVERTISEMENT
Feb 28, 2008
In my employee table has the following fields empid, empFname, empLname, email, city
Say it has data like follows:
1, Lucy, Sam, l@some.com, city1
2. Sam, Wite, l@some.com, city2
3. Laura, Mac, l@some.com, city2
4. Stacy, Soo, s@no.com , city1
So in my case I want to show all the column but I want to eliminate multiple email addresses. I tried Distinct but its not workin because here every column is not distinct. So what should I use?
In my case I only want to show empID 1, 3, 4. I want to show all the columns
View 5 Replies
View Related
May 19, 2008
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 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
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
May 8, 2006
Hello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )
This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table).
This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day.
So far so good, and everything works fine except one thing:
Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)
What I need to do is to get a result like (in a neat fashion :)
"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"
So, how to do it in a suitable way?
Thanks in advance.
View 4 Replies
View Related
Mar 3, 2006
hi
I had a view in which I did something like this
isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
Thanks in advance.
View 5 Replies
View Related
Mar 29, 2007
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
View 1 Replies
View Related
Jan 7, 2004
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)
Try
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId
cn.Open()
cmd.ExecuteNonQuery()
Label1.Text = "done"
cn.Close()
Catch ex As Exception
Label1.Text = ex.Message
End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Please I would appreciate any advice...
View 2 Replies
View Related
Jan 9, 2015
Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.
Here's an example of what I'm hoping to accomplish:
*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)
===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555
==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3
View 1 Replies
View Related
Jul 18, 2007
I setup a Sql Server Agent job to run daily every 15 minutes from 11:15PM to 10:45PM. I set this up about noon, and the job and schedule were both Enabled.
But the job would not run. I sat and watched it for some time but it just sat there. What do I have to do to get the job to run?
Sorry! I forgot that Recurring schedules do not work until the start-time is reached. If you change the start-time the schedule becomes inactive. So I created a second schedule to cover the hole!
View 1 Replies
View Related
Sep 27, 2007
If I know this SELECT will get me unique username to configname records:Select DISTINCT configname, username FROM EtechModelRequests
JOIN CC_host.dbo.usr_smc ON
username = user_id
JOIN Webservices.dbo.PartNumberPricingImport ON
PartNumber = configname
Where RequestDateTime > '9/26/2007' And country = 'US' And interfacename Like '%download%' And result = 0
How do I show the other fields I need? The field I need is List Price but I don't want to DISTINCT on it too.
View 2 Replies
View Related
Mar 31, 2008
Hi I have two tables linked by MemberID and I'll like to produce a list from the two tables but also want to specify one table to only use the Top 50 distinct records
Table 1 (top 50 Distinct)
Memberid (distinct)PictureDateTakenGalleryID
Order By DateTaken
Table 2
MemberidFirstNameLastNamePrivatelist
MemberidPictureFirstNameLastNameGalleryIDPrivate
View 6 Replies
View Related
Apr 2, 2004
I have a query and it is bringing up multiples of the same data
what i would like to know is if there is a way to use something like Distinct that i can use as a clause
such as
Select *
from Table
Where Distinct ColumnName
I know that distinct doesnt work in this situation
I would like to know if there is a command to do this or a way to fix it
if i use this
select distinct columnName,columnName2
from table
it returns the rows where columnName and ColumnName2 both are not the same
My purpose is that i need to select more than one column but i would like non duplicates based on ONE column name
thanks for any help
View 20 Replies
View Related
May 31, 2002
I have 6 fields in my table and I want to display only distinct values from one of those columns. However, I also need to use the fields from the remaining 5 columns in my asp page. For example,
Columns:
CompID
CompanyName
Ticker
Industry
CEOName
MarketCap
In one table on the page, I want to display only unique entries for 'CompanyName' which I've been doing with this statement:
SqlText = "SELECT DISTINCT CompanyName FROM [tablename] WHERE UserID=" & intUserID & ""
However, I also need to use the other values associated 'CompanyName' in my asp page after opening my recordset. I need to display <%=Rs("CompID"%>, <%=Rs("Ticker"%>, <%=Rs("Industry"%> etc. while maintaining the DISTINCT portion of my statement.
Thanks again.
View 1 Replies
View Related
Sep 12, 2006
I have 3 records in a db..
Table
id
file_name
category
Records
1
bob.jpg
male
2
rob.jpg
male
3
pam.jpg
female
I am trying to grab the distinct category so only 'male and female' is output, but i am also needing to grab the id, file_name to use later on the page as well.
When i try
select distinct category, id, file_name
from tbl_pictures
it outputs all the records since there is no exact match in all 3 fields,
but i am wanting this to happen
select distinct category
from tbl_pictures
but i am still needing to grab the other two fields because i need to use them in the next part of the page
how can i go about doing this
View 1 Replies
View Related
Apr 22, 2008
What is the difference between distinct and group by please?
View 4 Replies
View Related
Apr 24, 2008
Hi,
I have data in several tables and Im having trouble filtering the data.
This is statement that Im executing:
1. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
This is the statement that gives me the results I want:
2. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName
The problem with this is I need to have Persons.Skills in the statement and DISTINCT doesnt filter the data the way I need it to because the data in Persons.Skills is different which results in I get duplicated results.
Is it possible to do something like
SELECT DISTINCT column1,..,column5,(SELECT Persons.Skills)
by this I mean to apply distinct to some of the columns in the SELECT statement?
View 6 Replies
View Related
May 20, 2008
I require only the distinct objects that were depend on my stored procedures
i tried for sp_depends test_sp
for this
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_slno
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_type
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolevel1
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonolayout_operator
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonorlevel1desc
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenonorlevel2desc
dbo.iim_CPL_fsl_tree_tbl_tmpuser tablenoyesguid
dbo.abb_account_budget_dtluser tablenonocompany_code
dbo.abb_account_budget_dtluser tablenonofb_id
dbo.abb_account_budget_dtluser tablenonofin_year_code
dbo.abb_account_budget_dtluser tablenonofin_period_code
dbo.abb_account_budget_dtluser tablenonoaccount_code
being dbo.abb_account_budget_dtl were repeated more than once.i need only the distinct objects..any other system procedure is there??
View 1 Replies
View Related
Jun 3, 2008
I'm trying to find the Distinct for CODEID but it's still returning duplicate data. What is the best way to write this query so that the result I get are Distinct Codeid?
SELECT DISTINCT dbo.AgreementDurationCode.CodeID, dbo.Item.PartNumber, dbo.ItemShadow.AgreementDurationCode, dbo.AgreementDurationCode.CodeCategory,
dbo.AgreementDurationCode.CodeCategoryID, dbo.AgreementDurationCode.Code, dbo.AgreementDurationCode.CodeName,
dbo.AgreementDurationCode.CodeAbbreviation, dbo.AgreementDurationCode.CodeShortAbbreviation, dbo.Item.ItemName,
dbo.AgreementDurationCode.CodeMaxcimAbbreviation, dbo.ItemShadow.ItemStatusCode, dbo.ItemShadow.LicenseTypeCode
FROM dbo.Item INNER JOIN
dbo.ItemShadow ON dbo.Item.ItemID = dbo.ItemShadow.ItemID INNER JOIN
dbo.AgreementDurationCode ON dbo.Item.AgreementDurationCodeID = dbo.AgreementDurationCode.CodeID
WHERE (dbo.ItemShadow.LicenseTypeCode IN ('SEL', 'OLP', 'OLV')) AND (dbo.ItemShadow.ItemStatusCode = 'COM')
View 3 Replies
View Related
Apr 18, 2014
I have data like this
course_id Session
--------- --------
1234 8.00 - 10.00
4567 8.00 - 10.00
From the above table i want the result to be like this
course_id1 course_id2 session
---------- ---------- -------
1234 4567 8.00 - 10.00
Looking for query to get the above result format.
View 3 Replies
View Related
Mar 13, 2007
Hi All,
I am using SQL Server 2000. I have 3 fields in my table. I want to do distinct on one field but also want to show the remaining two fields in the result. How can I do that?
Thanks
-G
View 6 Replies
View Related
Apr 6, 2007
Is there some way to use the distinct keyword so that it applies ONLY to a subset of the items in the select list???
For example, suppose I want to
select col_1, col_2, col_3, col_4
but I do not want distinct applied against all 4 items...
Maybe I want all 4 items in the selection list,
but I want distinct to use only col_3 as its filtration criteria...
I know the syntax shown below is not valid, but I am showing it anyway because I am hoping it will illustrate what I am looking for...
Is there a VALID syntax that is something like this???
select col_1, col_2, (distinct col_3), col_4
or
select col_1, col_2, distinct (col_3), col_4
View 5 Replies
View Related
May 22, 2007
I am trying to pull information out of a table using distinct, but instead of just pulling a certain column I want to pull multiple columns in a row?
However when I use the command below I only get the "workitem_number" column available, where there are approx. 4 other columns that I need (workitem_title, workitem_comment, ETC) When I add the additional columns (after the distinct statement) it doesn't work due to the type of data.
"SELECT DISTINCT workitem_number from workitem_cost_view where assigned_to_worker_nt_id = '" & Request.QueryString("Name") & "' AND workitem_start_on = '" & Request.QueryString("schDate") & "' order by workitem_number"
Is there any way to pull multiple columns, based only on distinct for one column?
I hope I'm explaining this correctly.
Thanks.
Rwj6001
View 13 Replies
View Related
Sep 1, 2007
Hi
I have table 'TblProd' and columns ID, categories, make, description, model. I am using the below procedure
create procedure getsubcategories
@category nvarchar (32)
As
Select ID, make, model from TblProd where category = @category
GO
My question how do I get distinct 'make' and other respective columns.
advance thanks
View 11 Replies
View Related
Jan 28, 2008
Hi Guys,,
want I need your help
I have the ff records:
Customer
ID CustNo Name Type
10 1 Madonna R
10 2 Madonna R
I used the ff query
select distinct(ID), CustNo, name,type
from Customer
where Type = 'R'
order by Name
but the result is wrong.
I want this result:
10 2 Madonna R
View 2 Replies
View Related
Mar 4, 2008
Hello - I am having trouble with my sql statement:
SELECT * FROM (SELECT DISTINCT rfs_id FROM tbl_comment) DT INNER JOIN summary_rfs t1 ON DT.rfs_id = t1.rfs_id INNER JOIN tbl_callStatus as t2 on t1.callstatus_id=t2.callstatus_id INNER JOIN tbl_user as t3 on t1.requestor=t3.user_name INNER JOIN tbl_user as t4 on t1.assigned_to=t4.user_name WHERE date_opened BETWEEN '1/1/2008' AND '1/1/2009' AND ASSIGNED_TO='Elaine Tran' ORDER BY priority_id ASC
this runs fine - but I want to retrieve data from tbl_comment how can I do this to display info from tbl_comment but not see the duplicates - can I flatten or merge the data?
View 1 Replies
View Related
Nov 7, 2007
Hello,I have a tableItemID Version12 1.012 1.112 2.013 2.013 1.014 1.015 1.015 5.015 2.1How do I write a Select query to get me all distinct item IDs, whichmare of the latest version?Like this:ItemID Version12 2.013 2.014 1.015 2.1Any help would be appreciated.Thanks
View 2 Replies
View Related
Jul 9, 2006
Hi people,
I love reporting services 2005 BUT have struck a major limitation!
Basically I need a sum distinct function. I have various duplicate details lines and just need to sum the unique values. Anyway this is not possible and a number of people a stuck with this. Yes you can write another sql statement using DISTINCT but then how can you easily integrate that into a table with scope? you can't!
Anyway has anyone been able to achieve this nicely in reporting services? I was thinking of calling a distinct SQL statement from an Expression in a text box on a header field and passing another text box as a parameter to get around this limitation. Is this possible?
Here is a link with similiar problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=240606&SiteID=1
Cheers
Damien
View 17 Replies
View Related
Feb 6, 2008
Anybody see a reason why this list won't populate?
<asp:DropDownList ID="ddlState" DataSourceID="srcStates" DataTextField="StateName" DataValueField="StateName" runat="server"> <asp:ListItem>Select State</asp:ListItem></asp:DropDownList><asp:SqlDataSource ID="srcStates" runat="server" ConnectionString="<%$ ConnectionStrings:webConn %>" SelectCommand="sp_States" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="ddlState" DefaultValue="Select State" Name="State" PropertyName="SelectedValue" Type="String" /> </SelectParameters></asp:SqlDataSource>
View 6 Replies
View Related
May 11, 2005
I'm having a tough time understanding my current problem and why I'm having it.
I'm using an ODBC db connection to connect to my sql server and trying
to insert data into a table I've created, but I keep getting an error
saying that the table doesn't exist. This is the first time I've
ever used ODBC and DSN to connect to a db so I don't konw if there are
qerks associated with this, but I've tried everything I can think
of. I've changed the name of the table, placed it in brackets,
placed "dbo" infront of the name, and still I get the same error about
the table not existing. My "Intial Catalog" in my connection
string is the correct db, I just can't seem to get it going.
Any suggestions?
View 27 Replies
View Related
Aug 27, 2001
I'm pulling data from Oracle 8.05 using Oracle ODBC with DTS to SQL Server 7 sr2. If I choose my columns only, I have no problem. When I try to edit the SQL statement either in the Import Wizard or the DTS Designer, the package won't run. I am selecting on a date column. I corrected the TSQL using the "convert" statement. Does someone know what might be happening?
View 1 Replies
View Related
Sep 13, 1999
Hi there,
Since I have upgraded to SQL 7 I'm exeriencing a problem with SQL Server Agent. I have a job scheduled to run every hour between 8:30AM and 5:30 PM during weekdays. Every Monday for the last several weeks I check the job status and it says it is enabled, runnable, and scheduled. Last job was run at 5:30PM on Friday, next run time is Monday 8:30AM. The only thing is that it is after 9:00AM. What gives? Then I have to run the job manually for the rest of the day. It usually starts working the next day. Anybody has similar problem?
Regards,
P.S. The agent service is running.
View 1 Replies
View Related
