Distinct Values And Join Query
Feb 24, 2015
SELECT first.name,first.country, second.name, second.string
FROM
first LEFT OUTER JOIN second
ON
first.name = second.name
WHERE
first.date='2015/02/24'
This query means all record from second table and matching record from first table. Now my question is that on 24 Feb 2015 I have duplicate names in second table and I want distinct names from second table and then its matching values from first table. Now my query is showing all duplicate values from second table and its matching record from first table.
View 5 Replies
ADVERTISEMENT
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
Sep 26, 2006
Hello,
I have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.
I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.
TableC and TableD are guaranteed to have a relationship.
The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.
Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.
Thank you SO much for your help!
View 5 Replies
View Related
Nov 6, 2014
in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').
i tried in my join query
these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')
its taking so much of time how t o solve ?
View 1 Replies
View Related
Aug 18, 2015
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.
Please see my query below:-
SELECT Week_Day.Week_Day_name, Subject.Subject_Code, Daily_Timetable.Period_No
FROM Week_Day LEFT JOIN
Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6 LEFT JOIN
Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
View 4 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
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Dec 15, 2014
I have a view which joins 2 tables together in a left outer join.
Now, the left outer join table (lets call this SalesData) may or may not have records. As months and years go by, records will be inserted or updated.
I am trying to find a query which will do the following:
Bring back all records where the branch matches an input value and also:
- bring back NULL records for the YR and MO column
- bring back values which match the input params for YR and MO column.
this works as expected:
quote:
SELECT wmtecp.*
FROM v_stores wmtecp
RIGHT OUTER JOIN v_Stores roj ON wmtecp.ID = roj.ID
WHERE wmtecp.TM = @p1 AND 1=1 AND roj.YR IS NULL AND roj.MO IS NULL OR wmtecp.MO = 11 AND wmtecp.YR = 2014
This is right outer joining the view to itself and prefer it this way.
Here is the thing: I want to bring back only distinct records (distinct by store ID I guess).
We are bringing back result set which contains NULL and NON null values but for both result sets the same storeID is returned, I do not want this but to only bring back the same record once either WITH values or WITHOUT values.
View 1 Replies
View Related
Mar 5, 2007
Hello, first post since I can usually find answers by reading the forums. I've searched the internet up and down and for some reason I can't get this query to work properly.
I have two tables:
ticket
ticket_id
ticket_to
ticket_from
message
message_id
ticket_id
message
There can be several messages per ticket, connected by the ticket_id. I'd like to only pull only the most recent message, as well as the results from the ticket table. Currently I'm getting all messages per ticket.
Here's what I have:
SELECT distinct ticket.ticket_to, ticket.ticket_from, message.ticket_id, message.message
FROM tickets
INNER JOIN message ON tickets.ticket_id = message.ticket_id
GROUP BY message.ticket_id, message, ticket_to, ticket_from
Any help would be greatly appreciated! Thanks much.
View 3 Replies
View Related
Jul 20, 2005
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 14 Replies
View Related
Jun 4, 2007
Hello all,
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Any suggestions?
thanks for your time...
View 5 Replies
View Related
Feb 14, 2008
Hi again,
I have this SQL (part of a stored procedure) where I do LEFT JOIN. SELECT callingPartyNumber, AlertingName, originalCalledPartyNumber, finalCalledPartyNumber,
dateTimeConnect,
dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
clientMatterCode
FROM CDR1.dbo.CallDetailRecord t1
LEFT JOIN CDR2.dbo.NumPlan t2 ON t1.callingPartyNumber=t2.DNorPattern
WHERE
(t1.callingPartyNumber LIKE ISNULL(@callingPartyNumber, t1.callingPartyNumber) + '%') AND
(t1.originalCalledPartyNumber LIKE ISNULL(@originalCalledPartyNumber, t1.originalCalledPartyNumber) + '%') AND
(t1.finalCalledPartyNumber LIKE ISNULL(@finalCalledPartyNumber, t1.finalCalledPartyNumber) + '%') AND
(t1.clientMatterCode LIKE ISNULL(@clientMatterCode, t1.clientMatterCode) + '%') AND
(@callerName is NULL OR t2.AlertingName LIKE '%' + @callerName + '%') AND
(t1.duration >= @theDuration) AND
((t1.datetimeConnect) >= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), t1.datetimeConnect)) AND
((t1.dateTimeDisconnect) <= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), t1.dateTimeDisconnect))
The problem is that if the t2 has more than one entry for the same DNorPattern, it pulls the record more than once. So say t1 has a callingPartyNumber = 1000. t2 has two records for this number. It will pull it more than once. How do I get the Unique value.
What I am trying to get is the AlertingName (name of the caller) field value from t2 based on DNorPattern (which is the phone number).
If this is not clear, please let me know.
Thanks,
Bullpit
View 24 Replies
View Related
Apr 23, 2008
Hello all,
I am using INNER JOIN to connect 2 tables together but I wish it to return distinct columns instead of repeating itself !
eg.
Current output would be:
UserID Name UserID OrderID
1 John 1 5
2 Bob 2 6
I want it to be:
UserID Name OrderID
1 John 5
2 Bob 6
I need to use SELECT * as there are many many columns and wish to save time :)
Cheers,
James
View 3 Replies
View Related
Nov 16, 2006
I've been trying to get this to work right.
The db table has 3 fields: id, vwr, and reqType.
I need all DISTINCT vwr's from the table. (vwr's can repeat)
This gives me all rows, not distinct...
select distinct d.id, d.vwr, d.reqType from tblVWR AS dinner join tblVWR ton d.vwr = t.vwr
Any suggestions?
Thanks,
Zath
View 2 Replies
View Related
Jun 25, 2007
Hi guys,
Just trying to select a set of Articles from a SQL Server Database. The Articles all have a Category ID which is stored in another table (as an Article could be in more than one Category). I want to select the Top 3 Articles in a Category. At the moment I have as my SQL;
"SELECT TOP 3 f.ArticleID, f.Heading, f.Summary, f.WrittenDate, f.ArticleURL FROM feedTable f LEFT JOIN Categories c ON f.ArticleID = c.ArticleID WHERE c.CategoryID=" + CategoryID + " AND c.ArticleID<>" + id + " ORDER BY c.CategoryID"
Which seems to work to an extent in that I do get three articles in the same Category appearing. However, there are sometimes duplicates appearing, so I need to incorporate a DISTINCT clause to the above. I'm not sure where to put this in though. Any ideas?
Thanks.
View 6 Replies
View Related
Jul 28, 2007
I want to select all usernames from tlbUsers which practice a certain sport.I also have a table UsersAndSports:UserID intSportID intI therefore need an inner join statement:select username from(SELECT ROW_NUMBER() OVER (ORDER BY au.LastActivityDate DESC) as RowNum,ud.username from tblusers usinner join tblUsersAndSports uas on uas.usercode=us.usercode and (uas.sportID=3 or uas.sportID=4)WHERE us.UserName <>'' )as MemberInfo WHERE RowNum between (@startRowIndex+1) AND (@startRowIndex+@maximumRows)The problem lies in the usage of the ROW_NUMBER command. I need that in order to effectively page through my records, but it also makes thatI cant make a distinct selection as each RowNum is unique....:SIn case the user would practice 2 sports, the query would return 2 rows...if I place a distinct in front of the username as such:select distinct username fromThe query would return each user only once...BUTBUTBUT: my startrowindex and maximumrows apply to the results that can be found in the MemberInfo selection..so lets say my startrowindex would be 0 and maximumrow 5if my 1st query (without distinct) was to return:johnjohnjohnjohnmikemikerobmy 2nd query (with distinct) would return:johnmikewhereas I want it to return:johnmikerobWhat can I do?
View 2 Replies
View Related
Nov 17, 2006
Hi, i hope i'm asking this on the right forum.
My problem is:
I need to select only distinct columns from a table, for this i can use: "select distinct username from table_user" but this is not working each time .... someone told me that i can use "alias" ... what does this means and how can i use alias for geting only distinct usernames from my table???
hope that is here someone that can help me ... !
10x
David Palmer SQL Manager
View 4 Replies
View Related
Aug 3, 2006
I am writing a script to create a audit trigger on any table. I am getting duplicate rows inserted into my audit table, only for the primary key columns. Anybody see why?
Right now I am debugging an Insert, so I think you can ignore the "U" update part of the Where clause.
....starts with other code to determine columns and primary key fields for selected table....
--get number of columns
select
@rowId = min(RowId),
@MaxRowId = max(RowId)
from #tblFieldNames
-- Loop through fields and build Sql string
while @RowId <= @MaxRowId
BEGIN
SELECT @fieldname = colName FROM #tblFieldNames WHERE RowId = @RowId
SELECT @sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @sql = @sql + ' select distinct''' + @TableName + ''''
SELECT @sql = @sql + ',''' + @TriggerType + ''''
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ', 1'
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + @pkJoinClause
SELECT @sql = @sql + ' WHERE (''' + @TriggerType + ''' = ''I'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''D'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''U'' AND '
SELECT @sql = @sql + '((i.' + @fieldname + ' <> d.' + @fieldname + ')'
SELECT @sql = @sql + ' OR (''' + @fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' is NOT null)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' is null)))'
EXEC (@sql)
set @RowId = @RowId + 1
END
View 5 Replies
View Related
May 4, 2007
Hi,I am trying to output a list of data from a table, showing only one record of each TypeID.So, for instance, I have a simple SQL query that says:SELECT DISTINCT AlbumTypeID FROM Album
ORDER BY AlbumTypeID DESCThis works correctly, and gives a list of 1,2,3. But I need more information than that, I want the Description field output with the ID, but how can I do this without assigning that to be Distinc also?When I try: SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESCThe output is completely wrong.Many thanks
View 11 Replies
View Related
Jul 27, 2007
Hi ALL,
I need help in calculating sum of market value based on property_id. The sum should be calculated by finding the average market value for a given property and then sum the individual average of the property to get the Distinct sum.
I have a very little knowlegde in Cubes and analysis services. I need to perform this distinct sum by using calculated members using MDX on SQL server 2000.
Data example
P_Code Cus Prpty_IdMrkt Val
3000 1234 1111 $10,000 $10,000
3000 1234 2222 $20,000
3000 1234 3333 $30,000 $20,000
3000 5678 1111 $10,000
3000 5678 2222 $20,000 $30,000
3000 5678 3333 $30,000
3000 1020 1111 $10,000
3000 1020 3333 $30,000
Distinct Sum $60,000
Thanks in Advance
Brijesh
View 2 Replies
View Related
Aug 30, 2013
I want to get the summation of the amount column against all the distinct values of Description fields. May be the "Where" (applied on Tdate Column) Clause unable to contains all the Unique(Distinct) values from the single Column (Description).
Table Structure :
CREATE TABLE [dbo].[TransLine](
[TransID] [int] NULL,
[No] [int] NULL,
[Description] [varchar](50) NULL,
[Code] ....
Sample data : [URL] ....
View 5 Replies
View Related
Jun 25, 2014
I have a problem when selecting distinct values from a table. I want to select those distinct numbers with a code that I want. To make it more clear i`ll show it on an example.I have the following table:
tel_no | code | id_res
+-----------+--------------+-----------
0742062141 | a-not answer | 1
0742062141 | a-not answer | 2
0742062141 | c-answer | 3
0754231456 | a-not answer | 4
0754231456 | a-not answer | 5
When I use the following query it returns the distinct number but with the first code it finds:
select a.* from test_funnel as a
inner join
( select distinct tel_no,MIN(id_res) as id_res
from test_funnel
[code]....
View 3 Replies
View Related
Sep 21, 2007
Hi all,
I want to know how to coalesce distinct values as comma seperated into a variable which is used elsewhere. Here are my ddl and the query I tried.
My Expected result is
[Java],[MySQL],[.Net]
Code Snippet
CREATE TABLE #Tbl_Request
(
ID INT,
SkillCategoryID INT
)
GO
CREATE TABLE #Lkp_SkillCategory
(
ID INT,
Skill varchar(50)
)
GO
INSERT INTO #Tbl_Request VALUES(1,0)
INSERT INTO #Tbl_Request VALUES(2,1)
INSERT INTO #Tbl_Request VALUES(3,2)
INSERT INTO #Tbl_Request VALUES(4,0)
INSERT INTO #Tbl_Request VALUES(5,2)
INSERT INTO #Tbl_Request VALUES(6,2)
INSERT INTO #Tbl_Request VALUES(7,1)
GO
INSERT INTO #Lkp_SkillCategory VALUES(0,'Java')
INSERT INTO #Lkp_SkillCategory VALUES(1,'MySQL')
INSERT INTO #Lkp_SkillCategory VALUES(2,'.Net')
GO
DECLARE @listSkills nvarchar(max)
SELECT DISTINCT @listSkills= COALESCE(@listSkills+',','')+'['+ #Lkp_SkillCategory.Skill+']'
FROM #Tbl_Request INNER JOIN
#Lkp_SkillCategory ON #Tbl_Request.SkillCategoryID = #Lkp_SkillCategory.ID
SET @listSkills=(SELECT Skill = @listSkills)
SELECT @listSkills
DROP TABLE #Lkp_SkillCategory,#Tbl_Request
View 7 Replies
View Related
Sep 11, 2007
We have a query in which there are 20,000 rows and 90 distinct ID's
If we say
SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8
from #TEMPX
we get the 90 distinct values, but if we say
SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8, ts1avg, ts2avg, ts3avg, ts4avg, ts5avg, ts6avg, ts7avg, ts8avg from #TEMPX
TS1 contains 90 distinct values, whily ts1avg has 2,000 disitinct rows
Is there a way to get Distinct to work against only one column, i.e. SiteID ?
View 2 Replies
View Related
Aug 22, 2007
Hi,
How do I ensure that DISTINCT values of r.GPositionID are returned from the below??
Code Snippet
SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
FROM @GResults r
LEFT OUTER JOIN
ReconComments cm
ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;
Thanks.
View 11 Replies
View Related
Apr 11, 2008
Hello,
I have three columns in my database named FirstName, LastName, StudentID. i want to insert only the distinct values and avoid the values which are already in the database. can anybody please help me with the logic? is there any possibility of checking for the redudant values in the stored procedure?
below is my code:protected void Page_Load(object sender, EventArgs e)
{Label1.Visible = false;GridView1.Visible = false;
}protected void Button1_Click(object sender, EventArgs e)
{
loaddata();TextBox1.Visible = false;
TextBox2.Visible = false;TextBox3.Visible = false;
Button1.Visible = false;Label1.Visible = true;
Label2.Visible = false;Label3.Visible = false;Label4.Visible = false;
loadGrid();GridView1.Visible = true;
}private void loaddata()
{string str = ConfigurationManager.ConnectionStrings["adventure"].ConnectionString;
SqlConnection conn = new SqlConnection(str);SqlCommand cmd = new SqlCommand("Sp_Student", conn);
conn.Open();cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text;cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = TextBox2.Text;cmd.Parameters.Add("@StudentID", SqlDbType.Int).Value = TextBox3.Text;
cmd.ExecuteNonQuery();
conn.Close();
}
private void loadGrid()
{string strn = ConfigurationManager.ConnectionStrings["adventure"].ConnectionString;
SqlConnection connect = new SqlConnection(strn);string select = "select * from Student";
SqlCommand comm = new SqlCommand(select, connect);SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();da.Fill(ds, "Student");GridView1.DataSource = ds.Tables["Student"];
GridView1.DataBind();
}
Thanks
Sandeep
View 1 Replies
View Related
Aug 10, 1999
Hi,
Has anyone encountered this?
After applying SP 5a I found out that my SQL strings with sums and groups produced double rows. I found out that grouping one column (type CHAR(6)) produced two distinct rows of the equal values.
All the values in that column have 4 characters, but it seemed like some distinct values were grouped by the whole field length i.e 6 characters. I haven't seen earlier that those blanks after field value change the grouping results.
When I transferred the table and data to old SQL Server without any SPs, query showed distinct values correctly.
The sort order and other settings are the same. Can anyone tell where to look for help?
TIA, Al
View 1 Replies
View Related
Jul 6, 2006
Hello,
I have a table, which contains well over 30'000 rows. One of the Columns is for ransaction type, of which there are about 20-25 different types.
I am trying to pull a list of each type value, using a select distinct TSQL command...but thus far am unsuccessful.
Has anyone had a similar situation?? Am I wrong in using a Select Distinct?? Many thanks to all who reply!
View 3 Replies
View Related
Mar 17, 2008
Here is my dataset used by my report definition. The combo of barcode and order id is unique. The 'isDiscountedItem' field indicates if the customer used a coupon to purchased an item at a lower price.
departmentId classId barcode orderId isDiscountedItem
----------------------------------------------------------------------------------------------------------------------
1 1 123 1 True
2 7 456 1 False
1 1 123 2 False
1 1 123 3 True
1 1 789 3 True
2 7 456 3 False
... ... ... ... ...
I want to group my report by department id, class id and barcode. Then, I want to count all distinct order ids for which there was at leat one discounted item.
My report would produce the following output considering the above dataset:
Merchandise Number of customers who used a coupon
--------------------------------------------------------------------------------------------------------------
Department 1 2
Class 1 2
Barcode 123 2
Barcode 789 1
Department 2 0
Class 7 0
Barcode 456 0
I've been looking at a possible solution using hash tables defined in the report code but I would like to find a 'cleaner' solution. Any help would be appreciated.
View 3 Replies
View Related
Sep 21, 2015
I have the below query,
select
a.Assignment_UniqueID as DeploymentID,
a.AssignmentName as DeploymentName,
a.StartTime as Available,
a.EnforcementDeadline as Deadline,
sn.StateName as LastEnforcementState,-----Required Column
[Code] ....
o/p: is :
LastEnforcementState NumberOfComputers PComputers
Compliant 7056 91.54
Downloading update(s) 39 0.51
[code]....
I want to add these two rows information into single row,
Failed to Download Updates(s) 131(127+8) 1.66(1.65+0.1)
how to do this in my sql query?
View 12 Replies
View Related
Aug 17, 2006
I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!
View 3 Replies
View Related
Dec 6, 2013
Distinct name that match both subjects (math, science) from classname in level 2 only. Not sure where to even start. Example table below:
name subject level
bob math 2
hank math 1
joe science 2
bob science 2
joe math 2
ben science 2
carl science 1
View 2 Replies
View Related
Apr 5, 2006
I have a db that contains a column named DSCODES. Values in DSCODES are seperated by a comma.
If I run the following command.
select distinct(DSCODES) from DB
The following is returned.
S100,S102,S103
S100,S103,S105
What I would like returned is the following
S100
S102
S103
S105
Is this possible?
Thank you in advance
Graham
View 2 Replies
View Related
