.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Trying to build query using DISTINCT or GROUP BY...beginner here

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
Hi all, I have a table with the following format: instanceID    timeStamp  stepID 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:33.807 2 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:34.807 3 ... EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:26.773 2 so I need to build a query which will return 1 instanceID and all its stepIDs in one row. So the results would have to be something like this: instanceID    timeStamp  StepIDs 28B2D4FB-67F6-40CA-84A2-839BF3CC4B91 2010-09-07 20:36:32.807 1,2,3 EADD3AAA-5E93-4311-A844-9A7BE53A9606 2010-09-09 22:18:25.757 1,2 and if possible I would like to specify something like...bring me the data where 'timeStamp' > 2010-09-07 20:35 ps: I tried using DISCTINCT and GROUP BY but could not reach the desired results. Thank you!JCD

View Complete Post

More Related Resource Links

Need Help in LINQ query for group By with chunks of record


I am assigning and unique id [strShipperIdSequence] on my List on bases of some properties which are grouped together uniquely.
Now what i needed is that my group should be further break down to some maximum amount of chunks.[Let say 10]
that mean's even i am having Same value in 12 records i should get 2 groups[I of 10 items and other of 2 items]

var uniqueGroups = objMdbContentInfoList.GroupBy(p => new
}).Select(g => g.First()).ToList();
foreach (var objUnique in uniqueGroups)
string strShipperIdSequence = APIGlobalMethods.GetShipperRequestID();
foreach (MdbContentInfo obj in objMdbContentInfoList.FindAll(h => (h.CON_ENTRY_POINT == objUnique.CON_ENTRY_POINT &&
h.APPTType == objUnique.APPTType &&

Content Query with more thatn on group


I want to group the result of the content query web part in two levels but this web part only have one Grouping field. How can I do grouping with more than one field?

<site> and DocumentSubtype (custom property)


  • Site 1
  •   Document Sub type 1
  •        File 1
  •        File 2
  •   Document Sub type 2
  • Site 2
  • Document Sub type 1

GridView Paging + Unsupported overload used for query operator 'Distinct'


The issue is two fold in my ASP.NET Web Application. I need to use a custom Distinct() via LINQ to SQL, and then assign thevar as a DataSource to a GridView. If I use the custom Distinct that implements IEqualityComparer, I get "Unsupported overload used for query operator 'Distinct'" exception. but if i somehow get past this, i get "The data source does not support server-side data paging" exception on GridView.DataBind(). here is the code:

distinct_employees =
    (from emp in wsq.LIT_Active_Employees
    where (String.IsNullOrEmpty(ssFirstName.Text) ? (emp.FIRST_NAME == null || emp.FIRST_NAME.StartsWith(String.Empty)) : emp.FIRST_NAME.Contains(ssFirstName.Text)) &&
            (String.IsNullOrEmpty(ssLastName.Text) ? (emp.LAST_NAME == null || emp.LAST_NAME.StartsWith(String.Empty)) : emp.LAST_NAME.Contains(ssLastName.Text)) &&a

Group Query

Hi All. Greetings. I have 3 TablsTable =PersonID Person ---------- ----------- 1 ali2 abuTable = DetailID Person_ID Detail---------- ----------- --------------1 1 Good2 1 Excellent 3 2 Normal Table = ReferenceID Detail_ID description---------- ----------- ------------1 1 Has son2 2 Drop3 2 DiedI want result Like this.Ali----------GoodExcellentHas sonAbu----------NormalDropDiedAbove are 3 tables. I want to Show Person Name and then Its sub link or subcategory. If i use only 2 tables then i canshow required result but i have database where i have to fetch the record from 3rd and 4th table and bring under sub category.Thanks

T-SQL 2005 query for Group BY and not GROUP in same query for SQL reporting service use purpose?

Hi, I have SQL 2005 table like bellow @OrderTable I want to display all row data and GROUP BY data as well for SQL Reporting Service Matrix purpose...   declare @OrderTable TABLE (OrderID varchar(10),OrderType varchar(20),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06','O1',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06','O2',105.48,'2010-06-12') INSERT INTO @OrderTable VALUES('P07','O3',555.00,'2010-06-09') INSERT INTO @OrderTable VALUES('P08','O1',10.22,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O1',55.66,'2010-03-17') INSERT INTO @OrderTable VALUES('P06','O1',45.44,'2010-03-17') INSERT INTO @OrderTable VALUES('P07','O3',477.81,'2010-03-18') INSERT INTO @OrderTable VALUES('P07','O3',78.85,'2010-03-18') INSERT INTO @OrderTable VALUES('P06','O1',78.08,'2010-04-09') INSERT INTO @OrderTable VALUES('P07','O2',899.90,'2010-04-22') INSERT INTO @OrderTable VALUES('P08','O3',25.33,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',859.01,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O3',7433.89,'2010-01-24') INSERT INTO @OrderTable VALUES('P08','O1',1005.41,'2010-06-12') INSERT INTO @OrderTable VALUES('P06','O2',455.20,'2010-06-09') INSERT INTO @OrderTable VALUES('P07','O3',85.30,'2010-06-12') INSERT INTO @OrderTable VALUES

Get the row position of the group? merging query?

Hello! I am using SQLS2005. I have two tables: Unit: UnitId int PK Title varchar UnitOption: UnitOptionId int PK UnitId int FK Title varchar Quote: QuoteId int PK UnitOptionId int FK Title varchar I want to create a scalar UDF that takes a QuoteId param and returns a varchar that contains the following description (pseudu): Quote.Title + '-' + Unit.Title + '-' + Unit.UnitId + /* Here is where my question is: If there are more than 1 UnitOption under this Unit, then return '-' + the UnitOption number under this Unit (i.e.) if under this Unit, there are 3 UnitOption with IDs 13, 17, 55 under the unit, and the current Quote.UnitOptionId is the 17 one, it should return 2. Which means I want to retrieve an ID of this row in the group. Else return '' */

Please help me to find out distinct value from linq to sql query given.

profileuserDataContext db = new profileuserDataContext(); var Q = (from p in db.edufinals join RM in db.edufinals on p.UserName equals RM.UserName where p.name.StartsWith(srcval) select new { p.name, RM.ProfielID, RM.ProfielPic, RM.city }).OrderBy(x => x.name).Distinct().Take(10);//, p.Key.ProfielID, p.Key.ProfielPic, p.Key.city, p.Key.country foreach (var pg in Q.Distinct()) { Response.Write(pg.name); ..................................... .................................... } how to refine this query to get distinct "name" column? Please help me to find out exact value.

SSAS 2008 Measure group Distinct count

Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"  

Get Distinct value from a Group-Join syntax Linq

Hello,I have this group-join syntax but I couldn't get the distinct value from the relational tables below.Table_1key_1 t1_value1     Food2     Sports3     Leisure 4     Trip5     ZooTable_2key_2   key_1  t2_boolean15        1          True16        1          True17        1          True18        2          True19        2          True20        2          Falsedesire result:FoodSports            Dim c = (From t1 In dbContext_Table_1 _                                     Group Join GroupTable In _db.Table_2 On GroupTable.key_1 Equals t1.key_1 Into Group 

Parse XML To build Dynamic Query

Hi All, I am trying to figure out the best way to handle a scenario where I will be passed XML that will contain criteria for a search. If the user has selected specific filters then those will be sent in the XML and if there is a section that they left unfiltered then it will not be present in the XML (Which would mean everything for that filter should be returned). My question is around the best process to shred the XML and build a dynamic query out of what i am getting out of the XML object. Is there a better way to handle this scenario? Here is my current approach: Shred the XML and put the filtered data into Global Temp Tables so that I can use them to build my dynamic query. Use those temp tables to create "Where Exists" Criteria within the query to filter down the results based on what was passed to me in XML. If one of the search criteria sections wasn't filtered the temp table would have zero rows and I wouldn't add that to the where clause with an exists statement. I used FOR XML PATH('') in the queries to roll up the data into comma separated values. Build the test schema / objects:   -------------------------------------------------------- --Build Test Schema to demonstrate XML Parsing -------------------------------------------------------- SET NOCOUNT ON; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Products]

Create SharePoint Security Group populated by AD query

Is there any non-code way to create a SharePoint Security Group that is populated by an AD query? The "standard" way of getting the same "effect" is to create a group that contains an AD group but that does not allow members of a particular site to see who else is also a member of the site Any thoughts?

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

Group query

Hi All, Greetings.From Select statment Group query I am showing bellow recordAliHadi  | 2   | 3Jon    |  4 |  5HadiRobi | 2  | 3 Ali    |  4 |  5RobiHadi  | 2 | 3 Ali     |  4 |  5I want to show bellow result by Group query so what i wrote so it skip one category but show me in subcategory.Ali Hadi  | 2   | 3 Jon    |  4 |  5Robi Hadi  | 2 | 3 Ali     |  4 |  5Thanks

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

How to do a <> Select Query, and assign results to a Group 'Other'


How can I use this in a Select Query?
<> "*" & "Internet" & "*" Or <> "*" & "Old Customer" & "*" Or <> "*" & "Reference" & "*" Or <> "*" & "Saw Trucks" & "*" Or <> "*" & "Y/P" & "*" I want to group all the results (named count) and call the result 'Other'

Here’s my SQL now:

SELECT DATABASE.[LEAD FROM], Count(DATABASE.[LEAD FROM]) AS [Count of Leads], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-7,Date()) & "#") AS [Last 7-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-30,Date()) & "#") AS [Last 30-Days], DCount("*","[DATABASE]","[Lead From] = " & Chr$(34) & [Lead From] & Chr$(34) & " AND Database.[Appt Date] >= #" & DateAdd("d",-365,Date()) & "#") AS [Last 365-Days]



Best way to build a query /sproc to support filtering


Hi All,


I have a scenario where I will be getting search results from a front end UI.  If nothing is passed I should return all results, but if the users have selected one to many choices I will need to filter based on those choices.  My delima is how to handle this approach.  The data will be coming in the form of XML and I will need to shred it to get the criteria.

I was thinking I could use "WHERE EXISTS" Clauses and if there are any results passed to me I could fill a table variable with everything and join to it.....if they did make selections then I could fill the table with just those choices. 

I really just don't like the idea of having to fill the table with everythign when nothing is selected. 

Just wondering what others have done to handle this type of scenario?

Thanks !


How to build long query string



I know that it is basic questions, but I am stuck here.

I have TableOne have fields: USERNAME, PID, GID, TID, SID, NEWSID. I need to write query string to check if those value is already in the table. If not, insert those value to the table.

string AddUserName = UsernameToken(GetUsername);
string InsertString = "insert into TableOne(USERNAME, PID, GID, TID, SID, NEWSID) values (?, ?, ?, ?, ?, ? )" + "  WHERE NOT " + " ( "  [USERNAME] = '" + AddUserName + "' + " PID = '" + DropDownBoxPID.Text.Trim() +
 '" + " AND " + " GID = '" + DropDownBoxGID.Text.Trim()  +
 '"  + " AND " + "  TID = '" + DropDownBoxtid.Text.Trim()  +  
 '" + " AND " + " SID = '" + Textboxsid.Text.Trim() +  
 '" + " AND " + " NEWSID = '" + DropDownssid.Text.Trim() +
 '" + ")" ;

However, I got the many errors:

Error    1    Too many characters in character literal  

Error    3    Newline in constant   

Is any way to debug this? As always, your help is highly a

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend