Disctinct Selections

Apr 11, 2006

Hi I am trying to figure out how to use the DISCTINCT function in s SELECT Query for one particular column, but output more that the disctinct column


for example:

table 1

Alan Andrews 1 main st 07465
John Andrews 1 main st 07465
Erick Andrews 1 main st 07465

I want to select by disctinct last name, but on my results I want to see all the other fields as well, and not just the last name. In this case the first name address and zip code.

So is there a way of doing this in SQL?

It does not have to be with the DISCTINCT function, but I need to net down to 1 per last name in a select query.

Thanks in advance!

Alan

View 14 Replies


ADVERTISEMENT

Disctinct Query Help

May 22, 2007

Can anyone tell me how to write this query correctly... Right now it is returning all of the results instead of just the results with unique emails...  SELECT     ID, firstname, address1, lastname, address2, city, province, postalCode, phoneNumber, email, referral, cpauseInfo, yukonInfo, dateAddedFROM         ResultsWHERE     email IN  (SELECT DISTINCT email  FROM  Results)  Please help. 

View 6 Replies View Related

Expression Problem Count Disctinct

Nov 8, 2007

Hello all,

I have a problem to do a count disctinct on two different fields within reporting services using an expression.

In my dataset i have a field called Description and a field called Type.
I would like to do a count distinct for every Description with Type=0 and a count distinct for every Description with Type=1.

Can this be done with an expression within RS (and not within my sql query from my dataset)?

Greetings
Vinnie

View 7 Replies View Related

Selections

Apr 26, 2006

here is my question

the way our companies are setup are as follows

company A is DatabaseA

Company B is DatabaseB

this was done to ensure company specific data.

now i need to create a proc that runs through DatabaseA gets all the tables, creates DatabaseC and insert all the Tables,Procedures, and data when the user creates a new company in our software interface.

if you could just help me cycle through the objects i can manage the rest.



thanks.

View 1 Replies View Related

Filtering Selections

Feb 23, 2005

Hi,

ok, i'm building a page to display a list of courses, a user rating and 'last visited' date.

I have 3 tables -
course (a list of all courses)
review (a list of all ratings)
visit (user visits to each course)

I've put together an SQL statment that returns everything i need, however its not quite right.
SELECT course.courseID, course.courseName, course.courseURL, avg(review.fldRating) AS fldAverage, visit.visitDate
FROM course


LEFT OUTER
JOIN review
ON course.courseId = review.fldcourseId

LEFT OUTER
JOIN visit
ON course.courseId = visit.courseId and visit.userId = 2

GROUP BY course.courseId, course.courseName, course.courseURL, visit.visitDate
ORDER BY course.courseId, visit.visitDate DESC

The problem lies with the fact that each time a user enters a course a new record is inserted into the visit table - so the visit table will show how many times a user has entered a course and on which dates.

this SQL statment returns something like this:

------------------------------------------------
course1 | 5 stars | 10/02/05
------------------------------------------------
course1 | 5 stars | 03/02/05
------------------------------------------------
course2 | 4 stars | 01/01/05

because the user has entered course 1 twice, the list is now showing 2 course1's - how can I change the statemtent to only select the most recent user visit, but still keep the complete list of courses?

I'm a bit of an SQL novice, so appologies if I've not explained this very well,
Thanks in advance,

-------------
injureFish

View 2 Replies View Related

Need A Way To Handle Multiple Selections

Oct 31, 2007

I have a webform that lists all items (codes) on the left and selected items (codes) on the right. A user selects an item on the left and clicks a button to move it to the right side. An update changes the bit from 0 to 1. This uses the bit column in the table to determine what is listed on the left (0) or right (1) sides.
Then I can filter in my stored procedure on the bit column WHERE (dbo.tblCodes.CodeSelect = 1)
My problem with this is that if two or more users are doing this process on different sessions, they can trip over each others selections.
I'm hoping someone has a suggestion on how I might avoid the users having this problem.

View 3 Replies View Related

Summing Different Combinations Of Selections

May 7, 2008

I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"

**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)

Thanks in advance
I.Shaame

View 9 Replies View Related

Unique Selections In Back End

Oct 14, 2005

I am using SQL 7 with an MS Access 2000 MDB front end, using bound formswith ODBC linked tables. In one form, the user needs to be able to check abox to select one or more records. This is accomplished with a local tablecontaining two fields: the primary key value of the SQL table and a booleanfield used for the check box.Since the local table used to contain the boolean field is local to the MDBfile, the result is a heterogeneous join in the underlying form query, whichdegrades performance. I would like to have the entire query be based on backend SQL data. However, each user needs to be able to make a unique set ofselections, without other users' selections affecting theirs.An idea I have is to port the selections table to the back end with anadditional field for machine name; create a view of the main table joined tothe selections table; link the view to the front end; and base the form onthe SQL: "Select * From MyView Where MachineName='MyMachine'".However, I wonder if there's a better approach. Any ideas would beappreciated.Thanks,Neil

View 18 Replies View Related

Changing Table Selections

Mar 29, 2007

Hello,



I created a package using the import/export wizard in SSIS, that loads data from one database to the other. I am trying to find out how I can add and remove the tables that were originally selected when the package was created. I opened the package in BIDS, and I could not find that particular option. I know you can do this in 2000/DTS...



Any help would be appreciated...



Thank you,



David

View 2 Replies View Related

Saving Selections For Next Report Run?

Jun 28, 2006

Hi All,

Don't think this can be done, but a user request, so thought I should ask anyway...

I have report with 11 filters (yes, I know thats overkill, but we aren't using analysis services) and the user has asked, if he wants to run it with the same selection criteria every month, can he do that without having to re-select them all?

Using RS 2000

thanx,

99

View 4 Replies View Related

Feeding Selections Into A Stored Procedure

Aug 7, 2006

Hello All,I'm not really sure where to post this as I'm not quite sure how to approach the problem; either whether it's an SQL problem or shoudl be addressed on the form.Anyway, on an .aspx page (using VB.NET) I have a drop down box and two text boxes. The user first selects a centre from the drop down and then enters a start and end date in the two text boxes. The user then clicks on a button which lists the results of a SQL server stored procedure using the specified parameters in a datagrid. All this is fine and works.Users however have requested an 'All centers' option in the drop down, which if selected, essentially means that instead of listing the results for a single centre, all centres are listed for the start and end dates specified.This is where I'm having problems. How do I feed this into my stored procedure? Is it a change in the procedure or something I need to do on the form?Any help appreciated.MoP.S. stored procedure look like this:-------------------    @centreid int,    @startdate varchar(20),    @enddate varchar(20)    ASSELECT centreid, datecreated, centrenameFROM tblcentresWHERE (DataLength(@startdate)  = 0 OR CentreID = @CentreID)AND (DataLength(@startdate)  = 0 OR datecreated >= @startdate)AND (DataLength(@enddate)  = 0 OR datecreated <= @enddate)

View 4 Replies View Related

Query Building Based On User Selections

Sep 22, 2006

I currently have a form that has different options in a list box such as:Status Codes

View 3 Replies View Related

Multi Value Parameter - Limit Number Of Selections

Jun 12, 2007

Hello



Anyone know if it is possible to limit the number of selections in a multi value parameter? Eg: There are 50 values in the drop down combo, but I want the user to be able to select a maximum of 10?



Cheers

View 1 Replies View Related

Passing Multiple Selections To A Stored Proc Parameter

Jan 11, 2005

Hi,

I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter:

CREATE FUNCTION dbo.SplitOrderIDs
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)

END
END
RETURN
END
GO


I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this.

Any suggestions?

Thanks

My plan is to have the same ability as under the 'Optional' section of this page:

http://search1.workopolis.com/jobshome/db/work.search_cri

View 1 Replies View Related

DB Design :: Auto Selections While Coding Using Select Statement

Aug 25, 2015

enable or shortcut key for Auto Database or Table View while coding. When I wrote sample code like below

SELECT * FROM VENKAT.

When I type V then I need to know there is Venkat Database with pop Message below.

View 2 Replies View Related

Keep A Multi-valued Parameter Cleared From Selections Until The User Select His Choice

Apr 28, 2008

I have a report that includes two multi-valued parameters.
In the Default Values section, I choose 'from query' and select dataset and value field.
In the Available Values section, I choose 'from query' select the same dataset and value field, and in the label field I select the relevant label field.
When I run the report my multi-valued parameters look like I selected the option 'select all' (all options are selected).
How can I keep the multi-valued parameters cleared from selections until the user select his choice? Thanks in advance.

View 5 Replies View Related

Sub Report Parameter Selections Via Values From Master Report Data

Jan 30, 2007

I am designing a report which will be used under the subreport control of a master report, now i am using a stored procedure for the subreport which has two parameters.

In the subreports dataset parameters tab, how can i mention the master reports columns, since i do not see the master reports columns here. since it is a different report.

If i use a hardcoded select query then i can just say

select address, city, state, zipcode from customer where (customerid = @customerID)

but i am using a stored proc. how to define the parameters under datasets parameter tab.

Thank you very much for the information.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved