When Does Ssrs Use A DISTINCT Clause In It's Queries?
I am having a heck of a time figuring out what controls how/when the generated SQL for a report puts a DISTINCT clause in front of it.
For instance, not that this report makes any sense, but I have 58 rows in my fact table/entity €“ If I pull in a lookup field and execute, the distinct is put in the query and I basically get a list of the possible domain values. It runs the whole joined table query to get them, but it does list (in this case) just 4 records. Now I put in the primary ID of the fact entity and the distinct goes away and I get my 58 rows. If I put two lookup fields, the DISTINCT is back. If I pull in the description field (text string just a direct source field mapping not part of the identifying attributes), the distinct is there. If I pull in the Company Name field on a different entity (which is essentially the same as pulling in Description only it is part of the identifying attributes), there is no DISTINCT. I can pull in all my fields on this entity and none of them drive the distinct. And I swear (ok, I am probably lying but not on purpose) the field/attribute and roles properties are all the same on the attributes. But you get my general question/situation...
Any insight for me? Does it have to do with how I am building the report rather than the underlying model?
Thanks in advance,
View Complete Forum Thread with Replies
Related Forum Messages:
GROUP By Clause Or DISTINCT Clause
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
View Replies !
Many DISTINCT Queries
Hi,I have a table that contains log data, usually around a million records. Thetable has about 10 columns with various attributes of the logged data,nothing special. We're using SQL Server 2000.Some of the columns (for example "category") have duplicate valuesthroughout the records. We have a web page that queries the table to showall the unique columns, for example:select distinct CATEGORY from table TESTObviously the server has to scan all rows in order to get all unique columnswhich takes quite a while, especially since that web page contains severalof these types of queries. We also have a MAX(DATE) and MIN(DATE) query thatalso add to the load.I already created indexes on the CATEGORY (actually on all categories)column which might help a little but I'm pretty sure that there has got tobe a better way.I also create a view (select distinct CATEGORY from table TEST) and tried toindex it, but it won't let me index a query that contains a DISTINCTstatement.Isn't there a way to create an index that contains only the distinct values?Is there another way to speed this up?Thanks for any hints!
View Replies !
Not Part Of DISTINCT Clause But I Do Want It Selected
I get the rows(records) I want by removing the RequestDateTime column from the Select query SELECT distinct et.configname as 'Part Number', substring(email,(charindex('@',email)+1),len(email)) as 'email ending',u.company_name as 'Company',u.city as 'City',u.country as 'Country' --,RequestDateTime as 'Date Downloaded'from new_products.dbo.modelrequests etleft outer join cc_host.dbo.usr_list u on et.username = convert(varchar(50),u.[user_id])where interfacename like '%download%'and result=0and u.country = 'JP'and CONVERT(DATETIME, RequestDateTime, 102)>= convert(DATETIME,'12/5/2007', 102)GROUP BY email,et.configname,u.company_name,u.city,u.country,requestdatetime I want to show the RequestDateTime for the 1600 records that do get selected. How can I put RequestDateTime back in the resulting table output without it being part of the Distinct clause?
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 !
Using Multiple Distinct Queries Inside Single Stored Procedure
Hello, I was wondering if anyone can explain the positives and negatives of using a single stored procedure that contains one or more distinct queries. I know there are problems with dynamic SQL but I am not proficient enough to know whether this falls under that umbrella. For clarification, what I am referring to is this: In a single stored procedure, I have a parameter called Query_ID that is used to identify which query in the sproc that I want to execute. Then from my ASP page, I simply pass the appropriate value for Query_ID. So: IF @QUERY_ID = 1 BEGIN SELECT [whatever] FROM [tbl1] WHERE [conditions] GROUP BY [something] ORDER BY [somethingelse] END ELSE IF @QUERY_ID = 2 BEGIN SELECT [whatever] FROM [tbl2] WHERE [conditions] GROUP BY [something] ORDER BY [somethingelse] END END I hope that makes sense. Thanks in advance.
View Replies !
Sub Queries, Aggregate Functions && Group By Clause
Hi Guys, I am having trouble with a particular query that is beyond my scope of understanding. Basically I need to pull sales records based on the following criteria: I have CustomerID, InvoiceNumber, ContractEndDate, MobileNumber, etc.. Customers recontract their mobile phone plans through us, and we have a new sales record for each time they recontract. For example, CustomerNumber 123 has recontracted 3 times.. once on 2006-01-01, then on 2007-02-12, and finally on 2008-02-15.. So they have a 12 month contract each time.. then come in to recontract it. So.. a customer has a single Customer Detail record, but may have many sales records attached. And a customer may have several sales for the SAME mobile phone number. Currently to pull ALL sales records for all customers, my query is this: Code: SELECT xxx.CustomerID AS xxx_CustomerID, xxx.Invoice AS xxx_Invoice, yyy.PhoneType AS yyy_PhoneType, yyy.PlanType AS yyy_PlanType, yyy.ContractEnds AS yyy_ContractEnds, yyy.MOB AS yyy_MobileNumber FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice WHERE yyy.ContractEnds IS NOT NULL AND xxx.CustomerID IS NOT NULL We want to get a list of customers that we can call to recontract, based on the ContractEnd field. However, we want UNIQUE mobile phone numbers, with the LATEST ContrtactEnd date. So, Customer 123 has 6 sales, for 2 unique Mobile numbers, the sql may be like: Code: SELECT MAX(yyy.ContractEnds) AS LatestCED, yyy.MOB FROM dbo.SaleControl xxx INNER JOIN dbo.SaleDetails yyy ON xxx.Invoice = yyy.Invoice WHERE xxx.CustomerID='123' GROUP BY yyy.MOB Now, this works fine, and of course if i remove the WHERE clause, it collects all unique mobiles, with latest ContractEnd date for each, for all customers. (Customer 123 displays 2 mobile numbers, each with the LATEST ContractEnd date) BUT i need this information ALONG WITH the other fields (xxx.CustomerID, xxx.Invoice, yyy.PhoneType, yyy.PlanType) and i have tried a few ways of doing it, but can't get my head around it.. Keep getting errors about Aggregate functions and Group By clause, and i understand why i am getting them, just cant think of any alternative query. Can anyone please help me! Thanks guys, Mick
View Replies !
Report Model - Where Clause In Named Queries
Hi, Consider the following situtation. ReportModel is created using named queries. It has multiple entities. Report Model is deployed to the report server and datasource is set dynamically at runtime(programmatically). When user runs the report (which is created using report model above), I need to change the where clause of one of the entities in ReportModel. How can i acccomplish this programmatically?
View Replies !
Stop SSRS Rewriting SQL In Where Clause
Is there a way to stop SSRS from rewriting my SQL? I can write an SQL statement that will execute within seconds within SQL Server Management Studio. But when I put that SQL into a dataset in SSRS, SSRS rewrites the SQL and makes the WHERE clause much more complex. Now the SQL takes minutes to complete. Is there a way to stop this? Rob
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 !
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 To ShortDateString, Not DISTINCT To The DateTime; How?
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 Replies !
Filtering Results In The Where Clause Vs A Having Clause
I am working with a vendor on upgrading their application from SQL2K to SQL2K5 and am running into the following. When on SQL Server 2000 the following statement ran without issue: UPDATE dbo.Track_ID SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed WHERE Processed = 0 AND LegNum = 1 AND TrackID IN ( SELECT TrackID FROM dbo.Track_ID GROUP BY TrackID HAVING MAX(LegNum) = 1 AND TrackID + 'x1' IN ( SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID)) FROM dbo.Track_ID INNER JOIN dbo.transactions ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id GROUP BY dbo.Track_ID.TrackID ) ) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time. I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same? UPDATE dbo.Track_ID SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed WHERE Processed = 0 AND LegNum = 1 AND TrackID IN ( SELECT TrackID FROM dbo.Track_ID WHERE TrackID + 'x1' IN ( SELECT dbo.Track_ID.TrackID + 'x' + CONVERT(NVARCHAR(2), COUNT(dbo.Track_ID.TrackID)) FROM dbo.Track_ID INNER JOIN dbo.transactions ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id GROUP BY dbo.Track_ID.TrackID ) GROUP BY TrackID HAVING MAX(LegNum) = 1 )
View Replies !
Can I Write SSRS 2008 Reports To Run On SQL 2005 SSRS?
Is it possible to write SSRS 2008 reports to run on an existing SSRS 2005 server? If yes, what do I need to do to be able to write SSRS 2008 reports on my developer PC that will run on the SQL 2005 Server (which also has SSRS 2005 installed on it)? I installed Visual Studio 2008 beta on the development PC and it appears that it needs SSRS installed on it too. So I installed SQL 2008 SSRS (SQL Server Reporting Services) and SQL 2008 Workstation Components onto the developer PC. Next I run the SQL 2008 "Reporting Services Configuration" tool. When I click on the "Web Service URL" section it hangs indefinitely and I have to force it closed. The developer PC is Windows Vista Business. Any suggestions on making this work, or any other information that you can think of that would allow me to use Visual Studio 2008 and the SQL 2008 "Report Designer Preview" tool to develop SSRS reports that will run on the SQL 2005 SSRS server? Thanks! -ErikR
View Replies !
Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples: 1) Rows ordered using textual id rather than numeric id Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id Result set is ordered as: 1, 11, 2 I expect: 1,2,11 if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. 2) SQL server reject query below with next message Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique. Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. It reproducible on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) and Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) In both cases database collation is SQL_Latin1_General_CP1251_CS_AS If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again. Could someone clarify - is it bug or expected behaviour?
View Replies !
SSRS 64Bit And SSRS 32Bit
I did a quick Google for this but found nada... If I develop a report in SSRS 64 bit, can it be ported to a 32 bit installation? Any issues? I will do my own testing, but I was curious if anyone else had experience doing this.
View Replies !
Having Clause Without GROUP BY Clause?
Hi, What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ? eg : SELECT SUM(col1) from test HAVING col2 < 5 SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5 I want the equivalent query in MSSQLServer for the above Oracle query. Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?. Thanks, Gopi.
View Replies !
SQL Inner Join Clause And The Where Clause
Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ? Thanks
View Replies !
Diff In On Clause And Where Clause?????
hi.. i have basic question like what is differance between conditions put in ON clause and in WHERE clause in JOINS???? see conditions that shown in brown color select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) ) select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) ) another thing... if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove d1.ImplicitID >= d2.ImplicitID from WHERE clause????
View Replies !
Top Clause With GROUP BY Clause
How Can I use Top Clause with GROUP BY clause? Here is my simple problem. I have two tables Categories Products I want to know Top 5 Products in CategoryID 1,2,3,4,5 Resultset should contain 25 Rows ( 5 top products from each category ) I hope someone will help me soon. Its urngent thanks in advance regards Waqas
View Replies !
Parameterized Queries Running Slower Than Non-parameterized Queries
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View Replies !
Varrying Page Footer Size In Ssrs Or Dynamically Displaying Page Footer In Ssrs
In SSRS my problem is to "show the page footer only in the first page of the report" and not in the other pages. That is if i am having 10 pages, the page footer should come only in the first page of the SSRS report and it should not shown in the remaining 9 pages. I used the expression in the visibility property of the rectangle which is placed in the page footer. But i can able to hide only the rectangle which is in the page footer of the remaining 9 pages. But the space occupied by the page footer is not get reduced. So i need to show the page footer dynamically, without occupying the space even a little. Is there any "tricks or techniques"?. Please let me inform, if so. It's very urgent. Thanks a lot in advance. Regards, MTRCHN
View Replies !
Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View Replies !
Distinct And Top
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 Replies !
Not Too Many Distinct..
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 Replies !
Distinct Like
Hi,I have a simple message table and I would like to select messages of certain forums and topics with unique titles.select title, body, min(postdate) as pdate, from dbo.[Message]where forumid = 4and topicid = 14group by title, bodyorder by pdateThis is supposed to return the unique titles posted to forumid=4 and topicid=14 but it is not working...Any ideas?
View Replies !
Distinct
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 Replies !
Get Only Max Of Each Distinct?
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 Replies !
Using DISTINCT
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 Replies !
Distinct And Top
Hi. is it possible to use between distinct and top in the same time ? I get records from database by distinct, but all of them come to me I want only 10 of them to come.
View Replies !
Using Distinct
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 Replies !
SUm Distinct
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 Replies !
Distinct Value
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 Replies !
DISTINCT... Help :)
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 Replies !
SQL Distinct Help PLS
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 Replies !
Distinct
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 Replies !
How To Have Distinct Value?
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 Replies !
Using Distinct
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 Replies !
Using DISTINCT
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 Replies !
DISTINCT
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 Replies !
DISTINCT
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 Replies !
|
TRACKER
