Search Results

Search found 19090 results on 764 pages for 'greatest n per group'.

Page 68/764 | < Previous Page | 64 65 66 67 68 69 70 71 72 73 74 75  | Next Page >

• ADO program to list members of a large group.

  - by AlexGomez

  Hi everyone, I'm attempting to list all the members in a Active Directory group using ADO. The problem I have is that many of these groups have over 1500 members and ADSI cannot handle more than 1500 items in a multi-valued attribute. Fortunately I came across Richard Muller's wonderful VBScript that handles more than 1500 members at http://www.rlmueller.net/DocumentLargeGroup.htm I modified his code as shown below so that I can list ALL the groups and its memberships in a certain OU. However, I'm keeping getting the exception shown below: "ADODB.Recordset: Item cannot be found in the collection corresponding to the requested name or ordinal." My program appears to get stuck at: strPath = adoRecordset.Fields("ADsPath").Value Set objGroup = GetObject(strPath) All I am doing above is issuing the query to get back a recordset consisting of the ADsPath for each group in the OU. It then walks through the recordset and grabs the ADsPath for the first group and store its in a variable named strPath; we then use the value of that variable to bind to the group account for that group. It really should work! Any idea why the code below doesn't work for me? Any pointers will be great appreciated. Thanks. Option Explicit Dim objRootDSE, strDNSDomain, adoCommand Dim adoConnection, strBase, strAttributes Dim strFilter, strQuery, adoRecordset Dim strDN, intCount, blnLast, intLowRange Dim intHighRange, intRangeStep, objField Dim objGroup, objMember, strName ' Determine DNS domain name. Set objRootDSE = GetObject("LDAP://RootDSE") 'strDNSDomain = objRootDSE.Get("DefaultNamingContext") strDNSDomain = "XXXXXXXX" ' Use ADO to search Active Directory. Set adoCommand = CreateObject("ADODB.Command") Set adoConnection = CreateObject("ADODB.Connection") adoConnection.Provider = "ADsDSOObject" adoConnection.Open = "Active Directory Provider" adoCommand.ActiveConnection = adoConnection adoCommand.Properties("Page Size") = 100 adoCommand.Properties("Timeout") = 30 adoCommand.Properties("Cache Results") = False ' Specify base of search. strBase = "<LDAP://" & strDNSDomain & ">" ' Specify the attribute values to retrieve. strAttributes = "member" ' Filter on objects of class "group" strFilter = "(&(objectClass=group)(samAccountName=*))" ' Enumerate direct group members. ' Use range limits to handle more than 1000/1500 members. ' Setup to retrieve 1000 members at a time. blnLast = False intRangeStep = 999 intLowRange = 0 IntHighRange = intLowRange + intRangeStep Do While True If (blnLast = True) Then ' If last query, retrieve remaining members. strQuery = strBase & ";" & strFilter & ";" _ & strAttributes & ";range=" & intLowRange _ & "-*;subtree" Else ' If not last query, retrieve 1000 members. strQuery = strBase & ";" & strFilter & ";" _ & strAttributes & ";range=" & intLowRange & "-" _ & intHighRange & ";subtree" End If adoCommand.CommandText = strQuery Set adoRecordset = adoCommand.Execute adoRecordset.MoveFirst intCount = 0 Do Until adoRecordset.EOF strPath = adoRecordset.Fields("ADsPath").Value Set objGroup = GetObject(strPath) For Each objField In adoRecordset.Fields If (VarType(objField) = (vbArray + vbVariant)) _ Then For Each strDN In objField.Value ' Escape any forward slash characters, "/", with the backslash ' escape character. All other characters that should be escaped are. strDN = Replace(strDN, "/", "\/") ' Check dictionary object for duplicates. 'If (objGroupList.Exists(strDN) = False) Then ' Add to dictionary object. 'objGroupList.Add strDN, True ' Bind to each group member, to find member's samAccountName Set objMember = GetObject("LDAP://" & strDN) ' Output group cn, group samaAccountName and group member's samAccountName. Wscript.Echo objMember.samAccountName intCount = intCount + 1 'End if Next End If Next adoRecordset.MoveNext Loop adoRecordset.Close ' If this is the last query, exit the Do While loop. If (blnLast = True) Then Exit Do End If ' If the previous query returned no members, then the previous ' query for the next 1000 members failed. Perform one more ' query to retrieve remaining members (less than 1000). If (intCount = 0) Then blnLast = True Else ' Setup to retrieve next 1000 members. intLowRange = intHighRange + 1 intHighRange = intLowRange + intRangeStep End If Loop

  Read the article

• How to get foreignSecurityPrincipal from group. using DirectorySearcher

  - by kain64b

  What I tested with 0 results: string queryForeignSecurityPrincipal = "(&(objectClass=foreignSecurityPrincipal)(memberof:1.2.840.113556.1.4.1941:={0})(uSNChanged>={1})(uSNChanged<={2}))"; sidsForeign = GetUsersSidsByQuery(groupName, string.Format(queryForeignSecurityPrincipal, groupPrincipal.DistinguishedName, 0, 0)); public IList<SecurityIdentifier> GetUsersSidsByQuery(string groupName, string query) { List<SecurityIdentifier> results = new List<SecurityIdentifier>(); try{ using (var context = new PrincipalContext(ContextType.Domain, DomainName, User, Password)) { using (var groupPrincipal = GroupPrincipal.FindByIdentity(context, IdentityType.SamAccountName, groupName)) { DirectoryEntry directoryEntry = (DirectoryEntry)groupPrincipal.GetUnderlyingObject(); do { directoryEntry = directoryEntry.Parent; } while (directoryEntry.SchemaClassName != "domainDNS"); DirectorySearcher searcher = new DirectorySearcher(directoryEntry){ SearchScope=System.DirectoryServices.SearchScope.Subtree, Filter=query, PageSize=10000, SizeLimit = 15000 }; searcher.PropertiesToLoad.Add("objectSid"); searcher.PropertiesToLoad.Add("distinguishedname"); using (SearchResultCollection result = searcher.FindAll()) { foreach (var obj in result) { if (obj != null) { var valueProp = ((SearchResult)obj).Properties["objectSid"]; foreach (var atributeValue in valueProp) { SecurityIdentifier value = (new SecurityIdentifier((byte[])atributeValue, 0)); results.Add(value); } } } } } } } catch (Exception e) { WriteSystemError(e); } return results; } I tested it on usual users with query: "(&(objectClass=user)(memberof:1.2.840.113556.1.4.1941:={0})(uSNChanged>={1})(uSNChanged<={2}))" and it is work, I test with objectClass=* ... nothing help... But If I call groupPrincipal.GetMembers,I get all foreing user account from group. BUT groupPrincipal.GetMembers HAS MEMORY LEAK. Any Idea how to fix my query????

  Read the article

• how to group data in a list

  - by prince23

  I need to group data of a list in c# ex: i have a data like this in a list c# i have a class called information.cs with these properties name,school, parent ex data name school parent kumar fes All manju fes kumar anu frank kumar anitha jss All rohit frank manju anill vijaya manju vani jss kumar soumya jss kumar madhu jss rohit shiva jss rohit vanitha jss anitha anu jss anitha now taking this as an input i wanted the output to be formated with a Hierarchical data when parent is all means it is the topmost level kumar fes All. what i need to do here is i need to create an object[0] and then check in list whether kumar exists as a parent in the list if it exista then add those items as under the object[0] as a parent i need to create one more oject under **manju fes kumar anu frank kumar** what i wanted do here is iterate through the list anD then check the parent level based on name school parent kumar fes All -->obj[0] manju fes kumar -->obj1[0] anu frank kumar -->obj1[1] for obj1-- obj[0] will be parent like this i need to genarte a list or observation class anitha jss All-->obj[1] vanitha jss anitha -->obj1[0] anu jss vanitha -->obj2[0] here obj2[0]--obj1[0]--obj[1] will be an parent like this i need to create a list or an observationclass hope my Question is clear what i am trying ask you people. i wanted to know how i can create an observationclass. any help would be really great. hope my question is clear

  Read the article

• how to group data in a list c#

  - by prince23

  hi, i need to group data of a list in c# ex: i have a data like this in a list c# i have a class called information.cs with these properties name,school, parent ex data name school parent kumar fes All manju fes kumar anu frank kumar anitha jss All rohit frank manju anill vijaya manju vani jss kumar soumya jss kumar madhu jss rohit shiva jss rohit vanitha jss anitha anu jss anitha now taking this as an input i wanted the output to be formated with a Hierarchical data when parent is all means it is the topmost level kumar fes All. what i need to do here is i need to create an object[0] and then check in list whether kumar exists as a parent in the list if it exista then add those items as under the object[0] as a parent i need to create one more oject under **manju fes kumar anu frank kumar** what i wanted do here is iterate through the list anD then check the parent level based on name school parent kumar fes All -->obj[0] manju fes kumar -->obj1[0] anu frank kumar -->obj1[1] for obj1-- obj[0] will be parent like this i need to genarte a list or observation class anitha jss All-->obj[1] vanitha jss anitha -->obj1[0] anu jss vanitha -->obj2[0] here obj2[0]--obj1[0]--obj[1] will be an parent like this i need to create a list or an observationclass hope my Question is clear what i am trying ask you people. i wanted to know how i can create an observationclass any help would be really great thanks prince hope my question is clear

  Read the article

• How do you capture a group with regex?

  - by Sylvain

  Hi, I'm trying to extract a string from another using regex. I'm using the POSIX regex functions (regcomp, regexec ...), and I fail at capturing a group ... For instance, let the pattern be something as simple as "MAIL FROM:<(.*)>" (with REG_EXTENDED cflags) I want to capture everything between '<' and '' My problem is that regmatch_t gives me the boundaries of the whole pattern (MAIL FROM:<...) instead of just what's between the parenthesis ... What am I missing ? Thanks in advance, edit: some code #define SENDER_REGEX "MAIL FROM:<(.*)>" int main(int ac, char **av) { regex_t regex; int status; regmatch_t pmatch[1]; if (regcomp(&regex, SENDER_REGEX, REG_ICASE|REG_EXTENDED) != 0) printf("regcomp error\n"); status = regexec(&regex, av[1], 1, pmatch, 0); regfree(&regex); if (!status) printf( "matched from %d (%c) to %d (%c)\n" , pmatch[0].rm_so , av[1][pmatch[0].rm_so] , pmatch[0].rm_eo , av[1][pmatch[0].rm_eo] ); return (0); } outputs: $./a.out "012345MAIL FROM:<abcd>$" matched from 6 (M) to 22 ($) solution: as RarrRarrRarr said, the indices are indeed in pmatch[1].rm_so and pmatch[1].rm_eo hence regmatch_t pmatch[1]; becomes regmatch_t pmatch[2]; and regexec(&regex, av[1], 1, pmatch, 0); becomes regexec(&regex, av[1], 2, pmatch, 0); Thanks :)

  Read the article

• jquery Tab group IDs

  - by mare

  I'm having an issue with jQuery UI Tabs script which does not pick up tabs that have a dot "." in their name (ID). For instance like this: <script type="text/javascript"> $(function () { $("#tabgroup\\.services").tabs(); }); </script> <div id="tabgroup.Services"> <ul> <li><a href="#tab.service1"> Service 1 title</a></li> <li><a href="#tab.service2"> Service 2 title</a></li> </ul> <div id="tab.service1"> <p>content</p> </div> <div id="tab.service2"> <p>content</p> </div> </div> The problem is because to select an element with a dot in its name, you need to use escapes (like when I initialize the tabs on my tabgroup). And apparently the Tabs JS implementation does not do that. Although I can do it at the tab group level, I cannot do it lower down because that's implemented in the Tabs JS file and I would not want to modify it (if possible).

  Read the article

• Getting percentage of "Count(*)" to the number of all items in "GROUP BY"

  - by celalo

  Let's say I need to have the ratio of "number of items available from certain category" to "the the number of all items". Please consider a MySQL table like this: /* mysql> select * from Item; +----+------------+----------+ | ID | Department | Category | +----+------------+----------+ | 1 | Popular | Rock | | 2 | Classical | Opera | | 3 | Popular | Jazz | | 4 | Classical | Dance | | 5 | Classical | General | | 6 | Classical | Vocal | | 7 | Popular | Blues | | 8 | Popular | Jazz | | 9 | Popular | Country | | 10 | Popular | New Age | | 11 | Popular | New Age | | 12 | Classical | General | | 13 | Classical | Dance | | 14 | Classical | Opera | | 15 | Popular | Blues | | 16 | Popular | Blues | +----+------------+----------+ 16 rows in set (0.03 sec) mysql> SELECT Category, COUNT(*) AS Total -> FROM Item -> WHERE Department='Popular' -> GROUP BY Category; +----------+-------+ | Category | Total | +----------+-------+ | Blues | 3 | | Country | 1 | | Jazz | 2 | | New Age | 2 | | Rock | 1 | +----------+-------+ 5 rows in set (0.02 sec) */ What I need is basically a result set resembles this one: /* +----------+-------+-----------------------------+ | Category | Total | percentage to the all items | (Note that number of all available items is "9") +----------+-------+-----------------------------+ | Blues | 3 | 33 | (3/9)*100 | Country | 1 | 11 | (1/9)*100 | Jazz | 2 | 22 | (2/9)*100 | New Age | 2 | 22 | (2/9)*100 | Rock | 1 | 11 | (1/9)*100 +----------+-------+-----------------------------+ 5 rows in set (0.02 sec) */ How can I achieve such a result set in a single query? Thanks in advance.

  Read the article

• Regex to ensure group match doesn't end with a specific character

  - by AJ

  I'm having trouble coming up with a regular expression to match a particular case. I have a list of tv shows in about 4 formats: Name.Of.Show.S01E01 Name.Of.Show.0101 Name.Of.Show.01x01 Name.Of.Show.101 What I want to match is the show name. My main problem is that my regex matches the name of the show with a preceding '.'. My regex is the following: "^([0-9a-zA-Z\.]+)(S[0-9]{2}E[0-9]{2}|[0-9]{4}|[0-9]{2}x[0-9]{2}|[0-9]{3})" Some Examples: >>> import re >>> SHOW_INFO = re.compile("^([0-9a-zA-Z\.]+)(S[0-9]{2}E[0-9]{2}|[0-9]{4}|[0-9]{2}x[0-9]{2}|[0-9]{3})") >>> match = SHOW_INFO.match("Name.Of.Show.S01E01") >>> match.groups() ('Name.Of.Show.', 'S01E01') >>> match = SHOW_INFO.match("Name.Of.Show.0101") >>> match.groups() ('Name.Of.Show.0', '101') >>> match = SHOW_INFO.match("Name.Of.Show.01x01") >>> match.groups() ('Name.Of.Show.', '01x01') >>> match = SHOW_INFO.match("Name.Of.Show.101") >>> match.groups() ('Name.Of.Show.', '101') So the question is how do I avoid the first group ending with a period? I realize I could simply do: var.strip(".") However, that doesn't handle the case of "Name.Of.Show.0101". Is there a way I could improve the regex to handle that case better? Thanks in advance.

  Read the article

• Optimize GROUP BY&ORDER BY query

  - by Jan Hancic

  I have a web page where users upload&watch videos. Last week I asked what is the best way to track video views so that I could display the most viewed videos this week (videos from all dates). Now I need some help optimizing a query with which I get the videos from the database. The relevant tables are this: video (~239371 rows) VID(int), UID(int), title(varchar), status(enum), type(varchar), is_duplicate(enum), is_adult(enum), channel_id(tinyint) signup (~115440 rows) UID(int), username(varchar) videos_views (~359202 rows after 6 days of collecting data, so this table will grow rapidly) videos_id(int), views_date(date), num_of_views(int) The table video holds the videos, signup hodls users and videos_views holds data about video views (each video can have one row per day in that table). I have this query that does the trick, but takes ~10s to execute, and I imagine this will only get worse over time as the videos_views table grows in size. SELECT v.VID, v.title, v.vkey, v.duration, v.addtime, v.UID, v.viewnumber, v.com_num, v.rate, v.THB, s.username, SUM(vvt.num_of_views) AS tmp_num FROM video v LEFT JOIN videos_views vvt ON v.VID = vvt.videos_id LEFT JOIN signup s on v.UID = s.UID WHERE v.status = 'Converted' AND v.type = 'public' AND v.is_duplicate = '0' AND v.is_adult = '0' AND v.channel_id <> 10 AND vvt.views_date >= '2001-05-11' GROUP BY vvt.videos_id ORDER BY tmp_num DESC LIMIT 8 And here is a screenshot of the EXPLAIN result: So, how can I optimize this?

  Read the article

• Get percentiles of data-set with group by month

  - by Cylindric

  Hello, I have a SQL table with a whole load of records that look like this: | Date | Score | + -----------+-------+ | 01/01/2010 | 4 | | 02/01/2010 | 6 | | 03/01/2010 | 10 | ... | 16/03/2010 | 2 | I'm plotting this on a chart, so I get a nice line across the graph indicating score-over-time. Lovely. Now, what I need to do is include the average score on the chart, so we can see how that changes over time, so I can simply add this to the mix: SELECT YEAR(SCOREDATE) 'Year', MONTH(SCOREDATE) 'Month', MIN(SCORE) MinScore, AVG(SCORE) AverageScore, MAX(SCORE) MaxScore FROM SCORES GROUP BY YEAR(SCOREDATE), MONTH(SCOREDATE) ORDER BY YEAR(SCOREDATE), MONTH(SCOREDATE) That's no problem so far. The problem is, how can I easily calculate the percentiles at each time-period? I'm not sure that's the correct phrase. What I need in total is: A line on the chart for the score (easy) A line on the chart for the average (easy) A line on the chart showing the band that 95% of the scores occupy (stumped) It's the third one that I don't get. I need to calculate the 5% percentile figures, which I can do singly: SELECT MAX(SubQ.SCORE) FROM (SELECT TOP 45 PERCENT SCORE FROM SCORES WHERE YEAR(SCOREDATE) = 2010 AND MONTH(SCOREDATE) = 1 ORDER BY SCORE ASC) AS SubQ SELECT MIN(SubQ.SCORE) FROM (SELECT TOP 45 PERCENT SCORE FROM SCORES WHERE YEAR(SCOREDATE) = 2010 AND MONTH(SCOREDATE) = 1 ORDER BY SCORE DESC) AS SubQ But I can't work out how to get a table of all the months. | Date | Average | 45% | 55% | + -----------+---------+-----+-----+ | 01/01/2010 | 13 | 11 | 15 | | 02/01/2010 | 10 | 8 | 12 | | 03/01/2010 | 5 | 4 | 10 | ... | 16/03/2010 | 7 | 7 | 9 | At the moment I'm going to have to load this lot up into my app, and calculate the figures myself. Or run a larger number of individual queries and collate the results.

  Read the article

• SQL group results as a column array

  - by Radek

  Hi guys, this is an SQL question and don't know which type of JOIN, GROUP BY etc. to use, it is for a chat program where messages are related to rooms and each day in a room is linked to a transcript etc. Basically, when outputting my transcripts, I need to show which users have chatted on that transcript. At the moment I link them through the messages like so: SELECT rooms.id, rooms.name, niceDate, room_transcripts.date, long FROM room_transcripts JOIN rooms ON room_transcripts.room=rooms.id JOIN transcript_users ON transcript_users.room=rooms.id AND transcript_users.date=room_transcripts.date JOIN users ON transcript_users.user=users.id WHERE room_transcripts.deleted=0 AND rooms.id IN (1,2) ORDER BY room_transcripts.id DESC, long ASC The result set looks like this: Array ( [0] => Array ( [id] => 2 [name] => Room 2 [niceDate] => Wednesday, April 14 [date] => 2010-04-14 [long] => Jerry Seinfeld ) [1] => Array ( [id] => 1 [name] => Room 1 [niceDate] => Wednesday, April 14 [date] => 2010-04-14 [long] => Jerry Seinfeld ) [2] => Array ( [id] => 1 [name] => Room 1 [niceDate] => Wednesday, April 14 [date] => 2010-04-14 [long] => Test Users ) ) I would like though for each element in the array to represent one transcript entry and for the users to be grouped in an array as the entry's element. So 'long' will be an array listing all the names. Can this be done? At the moment I just append the names and when the transcript date and room changes I echo them retrospectively, but I will do the same for files and highlighted messages and it's messy. Thanks.

  Read the article

• Building Stored Procedure to group data into ranges with roughly equal results in each bucket

  - by Len

  I am trying to build one procedure to take a large amount of data and create 5 range buckets to display the data. the buckets ranges will have to be set according to the results. Here is my existing SP GO /****** Object: StoredProcedure [dbo].[sp_GetRangeCounts] Script Date: 03/28/2010 19:50:45 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_GetRangeCounts] @idMenu int AS declare @myMin decimal(19,2), @myMax decimal(19,2), @myDif decimal(19,2), @range1 decimal(19,2), @range2 decimal(19,2), @range3 decimal(19,2), @range4 decimal(19,2), @range5 decimal(19,2), @range6 decimal(19,2) SELECT @myMin=Min(modelpropvalue), @myMax=Max(modelpropvalue) FROM xmodelpropertyvalues where modelPropUnitDescriptionID=@idMenu set @myDif=(@myMax-@myMin)/5 set @range1=@myMin set @range2=@myMin+@myDif set @range3=@range2+@myDif set @range4=@range3+@myDif set @range5=@range4+@myDif set @range6=@range5+@myDif select @myMin,@myMax,@myDif,@range1,@range2,@range3,@range4,@range5,@range6 select t.range as myRange, count(*) as myCount from ( select case when modelpropvalue between @range1 and @range2 then 'range1' when modelpropvalue between @range2 and @range3 then 'range2' when modelpropvalue between @range3 and @range4 then 'range3' when modelpropvalue between @range4 and @range5 then 'range4' when modelpropvalue between @range5 and @range6 then 'range5' end as range from xmodelpropertyvalues where modelpropunitDescriptionID=@idmenu) t group by t.range order by t.range This calculates the min and max value from my table, works out the difference between the two and creates 5 buckets. The problem is that if there are a small amount of very high (or very low) values then the buckets will appear very distorted - as in these results... range1 2806 range2 296 range3 75 range5 1 Basically I want to rebuild the SP so it creates buckets with equal amounts of results in each. I have played around with some of the following approaches without quite nailing it... SELECT modelpropvalue, NTILE(5) OVER (ORDER BY modelpropvalue) FROM xmodelpropertyvalues - this creates a new column with either 1,2,3,4 or 5 in it ROW_NUMBER()OVER (ORDER BY modelpropvalue) between @range1 and @range2 ROW_NUMBER()OVER (ORDER BY modelpropvalue) between @range2 and @range3 or maybe i could allocate every record a row number then divide into ranges from this?

  Read the article

• Group panel of WPF ListBox

  - by rulestein

  I have a listbox that is grouping the items with a GroupStyle. I would like add a control at the bottom of the stackpanel that holds all of the groups. This additional control needs to be part of the scrolling content so that the user would scroll to the bottom of the list to see the control. If I were using a listbox without the groups, this task would be easy by modifying the ListBox template. However, with the items grouped, the ListBox template seems to only apply on a per group basis. I can modify the GroupStyle.Panel, but that doesn't allow me to add items to that panel. <ListBox> <ListBox.ItemsPanel> <ItemsPanelTemplate> <WrapPanel/> </ItemsPanelTemplate> </ListBox.ItemsPanel> <ListBox.GroupStyle> <GroupStyle> <GroupStyle.Panel> <ItemsPanelTemplate> <VirtualizingStackPanel/> **<----- I would like to add a control to this stackpanel** </ItemsPanelTemplate> </GroupStyle.Panel> <GroupStyle.ContainerStyle> <Style TargetType="{x:Type GroupItem}"> <Setter Property="Template"> <Setter.Value> <ControlTemplate TargetType="{x:Type GroupItem}"> <Grid> <ItemsPresenter /> </Grid> </ControlTemplate> </Setter.Value> </Setter> </Style> </GroupStyle.ContainerStyle> </GroupStyle> </ListBox.GroupStyle> This should give an idea of what I need to do:

  Read the article

• Html.RadioButton group defaulting to 0

  - by awrigley

  Hi A default value of 0 is creeping into a Surveys app I am developing, for no reason that I can see. The problem is as follows: I have a group of Html.RadioButtons that represent the possible values a user can choose to answer a survey question (1 == Not at all, 2 == A little, 3 == A lot). I have used a tinyint datatype, that does not allow null, to store the answer to each question. The view code looks like this: <ol class="SurveyQuestions"> <% foreach (SurveyQuestion question in Model.Questions) { string col = question.QuestionColumn; %> <li><%=question.QuestionText%> <ul style="float:right;" class="MultiChoice"> <li><%= Html.RadioButton(col, "1")%></li> <li><%= Html.RadioButton(col, "2")%></li> <li><%= Html.RadioButton(col, "3")%></li> </ul> <%= Html.ValidationMessage(col, "*") %> </li> <% } %> </ol> [Note on the above code:] Each survey has about 70 questions, so I have put the questions text in one table, and store the results in a different table. I have put the Questions into my form view model (hence Model.Questions); the questions table has a field called QuestionColumn that allows me to link up the answer table column to the question, as shown above (<%= Html.RadioButton(col, "1")%, etc) [/Note] However, when the user DOESN'T answer the question, the value 0 is getting inserted into the database column. As a result, I don't get what I expect, ie, a validation error. In no place have I stipulated a default value of 0 for the fields in the answers table. So what is happening? Any ideas?

  Read the article

• Eliminate full table scan due to BETWEEN (and GROUP BY)

  - by Dave Jarvis

  Description According to the explain command, there is a range that is causing a query to perform a full table scan (160k rows). How do I keep the range condition and reduce the scanning? I expect the culprit to be: Y.YEAR BETWEEN 1900 AND 2009 AND Code Here is the code that has the range condition (the STATION_DISTRICT is likely superfluous). SELECT COUNT(1) as MEASUREMENTS, AVG(D.AMOUNT) as AMOUNT, Y.YEAR as YEAR, MAKEDATE(Y.YEAR,1) as AMOUNT_DATE FROM CITY C, STATION S, STATION_DISTRICT SD, YEAR_REF Y FORCE INDEX(YEAR_IDX), MONTH_REF M, DAILY D WHERE -- For a specific city ... -- C.ID = 10663 AND -- Find all the stations within a specific unit radius ... -- 6371.009 * SQRT( POW(RADIANS(C.LATITUDE_DECIMAL - S.LATITUDE_DECIMAL), 2) + (COS(RADIANS(C.LATITUDE_DECIMAL + S.LATITUDE_DECIMAL) / 2) * POW(RADIANS(C.LONGITUDE_DECIMAL - S.LONGITUDE_DECIMAL), 2)) ) <= 50 AND -- Get the station district identification for the matching station. -- S.STATION_DISTRICT_ID = SD.ID AND -- Gather all known years for that station ... -- Y.STATION_DISTRICT_ID = SD.ID AND -- The data before 1900 is shaky; insufficient after 2009. -- Y.YEAR BETWEEN 1900 AND 2009 AND -- Filtered by all known months ... -- M.YEAR_REF_ID = Y.ID AND -- Whittled down by category ... -- M.CATEGORY_ID = '003' AND -- Into the valid daily climate data. -- M.ID = D.MONTH_REF_ID AND D.DAILY_FLAG_ID <> 'M' GROUP BY Y.YEAR Update The SQL is performing a full table scan, which results in MySQL performing a "copy to tmp table", as shown here: +----+-------------+-------+--------+-----------------------------------+--------------+---------+-------------------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------------------+--------------+---------+-------------------------------+--------+-------------+ | 1 | SIMPLE | C | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | Y | range | YEAR_IDX | YEAR_IDX | 4 | NULL | 160422 | Using where | | 1 | SIMPLE | SD | eq_ref | PRIMARY | PRIMARY | 4 | climate.Y.STATION_DISTRICT_ID | 1 | Using index | | 1 | SIMPLE | S | eq_ref | PRIMARY | PRIMARY | 4 | climate.SD.ID | 1 | Using where | | 1 | SIMPLE | M | ref | PRIMARY,YEAR_REF_IDX,CATEGORY_IDX | YEAR_REF_IDX | 8 | climate.Y.ID | 54 | Using where | | 1 | SIMPLE | D | ref | INDEX | INDEX | 8 | climate.M.ID | 11 | Using where | +----+-------------+-------+--------+-----------------------------------+--------------+---------+-------------------------------+--------+-------------+ Related http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html http://stackoverflow.com/questions/557425/optimize-sql-that-uses-between-clause Thank you!

  Read the article

• SQL to insert latest version of a group of items

  - by Garett

  I’m trying to determine a good way to handle the scenario below. I have the following two database tables, along with sample data. Table1 contains distributions that are grouped per project. A project can have one or more distributions. A distribution can have one of more accounts. An account has a percentage allocated to it. The distributions can be modified by adding or removing account, as well as changing percentages. Table2 tracks distributions, assigning a version number to each distribution. I need to be able to copy new distributions from Table1 to Table2, but only under two conditions: 1. the entire distribution does not already exist 2. the distribution has been modified (accounts added/removed or percentages changed). Note: When copying a distribution from Table1 to Table2 I need to compare all accounts and percentages within the distribution to determine if it already exists. When inserting the new distribution then I need to increment the VersionID (max(VersionID) + 1). So, in the example provided the distribution (12345, 1) has been modified, adding account number 7, as well as changing percentages allocated. The entire distribution should be copied to the second table, incrementing the VersionID to 3 in the process. The database in question is SQL Server 2005. Table1 ------ ProjectID AccountDistributionID AccountID Percent 12345 1 1 25.0 12345 1 2 25.0 12345 1 7 50.0 56789 2 3 25.0 56789 2 4 25.0 56789 2 5 25.0 56789 2 6 25.0 Table2 ------ ID VersionID Project ID AccountDistributionID AccountID Percent 1 1 12345 1 1 50.0 2 1 12345 1 2 50.0 3 2 56789 2 3 25.0 4 2 56789 2 4 25.0 5 2 56789 2 5 25.0 6 2 56789 2 6 25.0

  Read the article

• Can't store UTF-8 in RDS despite setting up new Parameter Group using Rails on Heroku

  - by Lail

  I'm setting up a new instance of a Rails(2.3.5) app on Heroku using Amazon RDS as the database. I'd like to use UTF-8 for everything. Since RDS isn't UTF-8 by default, I set up a new Parameter Group and switched the database to use that one, basically per this. Seems to have worked: SHOW VARIABLES LIKE '%character%'; character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 character_sets_dir /rdsdbbin/mysql-5.1.50.R3/share/mysql/charsets/ Furthermore, I've successfully setup Heroku to use the RDS database. After rake db:migrate, everything looks good: CREATE TABLE `comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `commentable_id` int(11) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, `content` text COLLATE utf8_unicode_ci, `child_count` int(11) DEFAULT '0', `created_at` datetime DEFAULT NULL, `updated_at` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `commentable_id` (`commentable_id`), KEY `index_comments_on_community_id` (`community_id`), KEY `parent_id` (`parent_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; In the markup, I've included: <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> Also, I've set: production: encoding: utf8 collation: utf8_general_ci ...in the database.yml, though I'm not very confident that anything is being done to honor any of those settings in this case, as Heroku seems to be doing its own config when connecting to RDS. Now, I enter a comment through the form in the app: "Úbe® ƒåiL", but in the database I've got "Úbe® Æ’Ã¥iL" It looks fine when Rails loads it back out of the database and it is rendered to the page, so whatever it is doing one way, it's undoing the other way. If I look at the RDS database in Sequel Pro, it looks fine if I set the encoding to "UTF-8 Unicode via Latin 1". So it seems Latin-1 is sneaking in there somewhere. Somebody must have done this before, right? What am I missing?

  Read the article

• Need a set based solution to group rows

  - by KM

  I need to group a set of rows based on the Category column, and also limit the combined rows based on the SUM(Number) column to be less than or equal to the @Limit value. For each distinct Category column I need to identify "buckets" that are <=@limit. If the SUM(Number) of all the rows for a Category column are <=@Limit then there will be only 1 bucket for that Category value (like 'CCCC' in the sample data). However if the SUM(Number)@limit, then there will be multiple bucket rows for that Category value (like 'AAAA' in the sample data), and each bucket must be <=@Limit. There can be as many buckets as necessary. Also, look at Category value 'DDDD', its one row is greater than @Limit all by itself, and gets split into two rows in the result set. Given this simplified data: DECLARE @Detail table (DetailID int primary key, Category char(4), Number int) SET NOCOUNT ON INSERT @Detail VALUES ( 1, 'AAAA',100) INSERT @Detail VALUES ( 2, 'AAAA', 50) INSERT @Detail VALUES ( 3, 'AAAA',300) INSERT @Detail VALUES ( 4, 'AAAA',200) INSERT @Detail VALUES ( 5, 'BBBB',500) INSERT @Detail VALUES ( 6, 'CCCC',200) INSERT @Detail VALUES ( 7, 'CCCC',100) INSERT @Detail VALUES ( 8, 'CCCC', 50) INSERT @Detail VALUES ( 9, 'DDDD',800) INSERT @Detail VALUES (10, 'EEEE',100) SET NOCOUNT OFF DECLARE @Limit int SET @Limit=500 I need one of these result set: DetailID Bucket | DetailID Category Bucket -------- ------ | -------- -------- ------ 1 1 | 1 'AAAA' 1 2 1 | 2 'AAAA' 1 3 1 | 3 'AAAA' 1 4 2 | 4 'AAAA' 2 5 3 OR 5 'BBBB' 1 6 4 | 6 'CCCC' 1 7 4 | 7 'CCCC' 1 8 4 | 8 'CCCC' 1 9 5 | 9 'DDDD' 1 9 6 | 9 'DDDD' 2 10 7 | 10 'EEEE' 1

  Read the article

• Speeding up a group by date query on a big table in postgres

  - by zaius

  I've got a table with around 20 million rows. For arguments sake, lets say there are two columns in the table - an id and a timestamp. I'm trying to get a count of the number of items per day. Here's what I have at the moment. SELECT DATE(timestamp) AS day, COUNT(*) FROM actions WHERE DATE(timestamp) >= '20100101' AND DATE(timestamp) < '20110101' GROUP BY day; Without any indices, this takes about a 30s to run on my machine. Here's the explain analyze output: GroupAggregate (cost=675462.78..676813.42 rows=46532 width=8) (actual time=24467.404..32417.643 rows=346 loops=1) -> Sort (cost=675462.78..675680.34 rows=87021 width=8) (actual time=24466.730..29071.438 rows=17321121 loops=1) Sort Key: (date("timestamp")) Sort Method: external merge Disk: 372496kB -> Seq Scan on actions (cost=0.00..667133.11 rows=87021 width=8) (actual time=1.981..12368.186 rows=17321121 loops=1) Filter: ((date("timestamp") >= '2010-01-01'::date) AND (date("timestamp") < '2011-01-01'::date)) Total runtime: 32447.762 ms Since I'm seeing a sequential scan, I tried to index on the date aggregate CREATE INDEX ON actions (DATE(timestamp)); Which cuts the speed by about 50%. HashAggregate (cost=796710.64..796716.19 rows=370 width=8) (actual time=17038.503..17038.590 rows=346 loops=1) -> Seq Scan on actions (cost=0.00..710202.27 rows=17301674 width=8) (actual time=1.745..12080.877 rows=17321121 loops=1) Filter: ((date("timestamp") >= '2010-01-01'::date) AND (date("timestamp") < '2011-01-01'::date)) Total runtime: 17038.663 ms I'm new to this whole query-optimization business, and I have no idea what to do next. Any clues how I could get this query running faster?

  Read the article

• SQL query mixing aggregated results and single values

  - by Paul Flowerdew

  I have a table with transactions. Each transaction has a transaction ID, and accounting period (AP), and a posting value (PV), as well as other fields. Some of the IDs are duplicated, usually because the transaction was done in error. To give an example, part of the table might look like: ID PV AP 123 100 2 123 -100 5 In this case the transaction was added in AP2 then removed in AP5. Another example would be: ID PV AP 456 100 2 456 -100 5 456 100 8 In the first example, the problem is that if I am analyzing what was spent in AP2, there is a transaction in there which actually shouldn't be taken into account because it was taken out again in AP5. In the second example, the second two transactions shouldn't be taken into account because they cancel each other out. I want to label as many transactions as possible which shouldn't be taken into account as erroneous. To identify these transactions, I want to find the ones with duplicate IDs whose PVs sum to zero (like ID 123 above) or transactions where the PV of the earliest one is equal to sum(PV), as in the second example. This second condition is what is causing me grief. So far I have SELECT * FROM table WHERE table.ID IN (SELECT table.ID FROM table GROUP BY table.ID HAVING COUNT(*) > 1 AND (SUM(table.PV) = 0 OR SUM(table.PV) = <PV of first transaction in each group>)) ORDER BY table.ID; The bit in chevrons is what I'm trying to do and I'm stuck. Can I do it like this or is there some other method I can use in SQL to do this? Edit 1: Btw I forgot to say that I'm using SQL Compact 3.5, in case it matters. Edit 2: I think the code snippet above is a bit misleading. I still want to mark out transactions with duplicate IDs where sum(PV) = 0, as in the first example. But where the PV of the earliest transaction = sum(PV), as in the second example, what I actually want is to keep the earliest transaction and mark out all the others with the same ID. Sorry if that caused confusion. Edit 3: I've been playing with Clodoaldo's solution and have made some progress, but still can't get quite what I want. I'm trying to get the transactions I know for certain to be erroneous. Suppose the following transactions are also in the table: ID PV AP 789 100 2 789 200 5 789 -100 8 In this example sum(PV) < 0 and the earliest PV < sum(PV) so I don't want to mark any of these out. If I modify Clodoaldo's query as follows: select t.* from t left join ( select id, min(ap) as ap, sum(pv) as sum_pv from t group by id having sum(pv) <> 0 ) s on t.id = s.id and t.ap = s.ap and t.pv = s.sum_pv where s.id is null This gives the result ID PV AP 123 100 2 123 -100 5 456 -100 5 456 100 8 789 100 3 789 200 5 789 -100 8 Whilst the first 4 transactions are ok (they would be marked out), the 789 transactions are also there, and I don't want them. But I can't figure out how to modify the query so that they're not included. Any ideas?

  Read the article

• LINQ to XML : A query body must end with a select clause or a group clause

  - by Josh

  Can someone guide me on to repairing the error on this query : var objApps = from item in xDoc.Descendants("VHost") where(from x in item.Descendants("Application")) select new clsApplication { ConnectionsTotal = item.Element("ConnectionsTotal").Value }; It displays a compiler error "A query body must end with a select clause or a group clause". Where am I going wrong? Would appreciate any help.. Thanks. Edit : Here is my XML(haven't closed the tags here)...I need the connectioncount values inside the Application.. - <Server> <ConnectionsCurrent>67</ConnectionsCurrent> <ConnectionsTotal>1424182</ConnectionsTotal> <ConnectionsTotalAccepted>1385091</ConnectionsTotalAccepted> <ConnectionsTotalRejected>39091</ConnectionsTotalRejected> <MessagesInBytesRate>410455.0</MessagesInBytesRate> <MessagesOutBytesRate>540146.0</MessagesOutBytesRate> - <VHost> <Name>_defaultVHost_</Name> <TimeRunning>5129615.178</TimeRunning> <ConnectionsLimit>0</ConnectionsLimit> <ConnectionsCurrent>67</ConnectionsCurrent> <ConnectionsTotal>1424182</ConnectionsTotal> <ConnectionsTotalAccepted>1385091</ConnectionsTotalAccepted> <ConnectionsTotalRejected>39091</ConnectionsTotalRejected> <MessagesInBytesRate>410455.0</MessagesInBytesRate> <MessagesOutBytesRate>540146.0</MessagesOutBytesRate> - <Application> <Name>TestApp</Name> <Status>loaded</Status> <TimeRunning>411642.953</TimeRunning> <ConnectionsCurrent>11</ConnectionsCurrent> <ConnectionsTotal>43777</ConnectionsTotal> <ConnectionsTotalAccepted>43135</ConnectionsTotalAccepted> <ConnectionsTotalRejected>642</ConnectionsTotalRejected> <MessagesInBytesRate>27876.0</MessagesInBytesRate> <MessagesOutBytesRate>175053.0</MessagesOutBytesRate>

  Read the article

• Complex SQL query with group by and two rows in one

  - by Ricket

  Okay, I need help. I'm usually pretty good at SQL queries but this one baffles me. By the way, this is not a homework assignment, it's a real situation in an Access database and I've written the requirements below myself. Here is my table layout. It's in Access 2007 if that matters; I'm writing the query using SQL. Id (primary key) PersonID (foreign key) EventDate NumberOfCredits SuperCredits (boolean) There are events that people go to. They can earn normal credits, or super credits, or both at one event. The SuperCredits column is true if the row represents a number of super credits earned at the event, or false if it represents normal credits. So for example, if there is an event which person 174 attends, and they earn 3 normal credits and 1 super credit at the event, the following two rows would be added to the table: ID PersonID EventDate NumberOfCredits SuperCredits 1 174 1/1/2010 3 false 2 174 1/1/2010 1 true It is also possible that the person could have done two separate things at the event, so there might be more than two columns for one event, and it might look like this: ID PersonID EventDate NumberOfCredits SuperCredits 1 174 1/1/2010 1 false 2 174 1/1/2010 2 false 3 174 1/1/2010 1 true Now we want to print out a report. Here will be the columns of the report: PersonID LastEventDate NumberOfNormalCredits NumberOfSuperCredits The report will have one row per person. The row will show the latest event that the person attended, and the normal and super credits that the person earned at that event. What I am asking of you is to write, or help me write, the SQL query to SELECT the data and GROUP BY and SUM() and whatnot. Or, let me know if this is for some reason not possible, and how to organize my data to make it possible. This is extremely confusing and I understand if you do not take the time to puzzle through it. I've tried to simplify it as much as possible, but definitely ask any questions if you give it a shot and need clarification. I'll be trying to figure it out but I'm having a real hard time with it, this is grouping beyond my experience...

  Read the article

• Optimize MySQL query (ngrams, COUNT(), GROUP BY, ORDER BY)

  - by Gerardo

  I have a database with thousands of companies and their locations. I have implemented n-grams to optimize search. I am making one query to retrieve all the companies that match with the search query and another one to get a list with their locations and the number of companies in each location. The query I am trying to optimize is the latter. Maybe the problem is this: Every company ('anunciante') has a field ('estado') to make logical deletes. So, if 'estado' equals 1, the company should be retrieved. When I run the EXPLAIN command, it shows that it goes through almost 40k rows, when the actual result (the reality matching companies) are 80. How can I optimize this? This is my query (XXX represent the n-grams for the search query): SELECT provincias.provincia AS provincia, provincias.id, COUNT(*) AS cantidad FROM anunciantes JOIN anunciante_invertido AS a_i0 ON anunciantes.id = a_i0.id_anunciante JOIN indice_invertido AS indice0 ON a_i0.id_invertido = indice0.id LEFT OUTER JOIN domicilios ON anunciantes.id = domicilios.id_anunciante LEFT OUTER JOIN localidades ON domicilios.id_localidad = localidades.id LEFT OUTER JOIN provincias ON provincias.id = localidades.id_provincia WHERE anunciantes.estado = 1 AND indice0.id IN (SELECT invertido_ngrama.id_palabra FROM invertido_ngrama JOIN ngrama ON ngrama.id = invertido_ngrama.id_ngrama WHERE ngrama.ngrama = 'XXX') AND indice0.id IN (SELECT invertido_ngrama.id_palabra FROM invertido_ngrama JOIN ngrama ON ngrama.id = invertido_ngrama.id_ngrama WHERE ngrama.ngrama = 'XXX') AND indice0.id IN (SELECT invertido_ngrama.id_palabra FROM invertido_ngrama JOIN ngrama ON ngrama.id = invertido_ngrama.id_ngrama WHERE ngrama.ngrama = 'XXX') AND indice0.id IN (SELECT invertido_ngrama.id_palabra FROM invertido_ngrama JOIN ngrama ON ngrama.id = invertido_ngrama.id_ngrama WHERE ngrama.ngrama = 'XXX') AND indice0.id IN (SELECT invertido_ngrama.id_palabra FROM invertido_ngrama JOIN ngrama ON ngrama.id = invertido_ngrama.id_ngrama WHERE ngrama.ngrama = 'XXX') GROUP BY provincias.id ORDER BY cantidad DESC And this is the query explained (hope it can be read in this format): id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY anunciantes ref PRIMARY,estado estado 1 const 36669 Using index; Using temporary; Using filesort 1 PRIMARY domicilios ref id_anunciante id_anunciante 4 db84771_viaempresas.anunciantes.id 1 1 PRIMARY localidades eq_ref PRIMARY PRIMARY 4 db84771_viaempresas.domicilios.id_localidad 1 1 PRIMARY provincias eq_ref PRIMARY PRIMARY 4 db84771_viaempresas.localidades.id_provincia 1 1 PRIMARY a_i0 ref PRIMARY,id_anunciante,id_invertido PRIMARY 4 db84771_viaempresas.anunciantes.id 1 Using where; Using index 1 PRIMARY indice0 eq_ref PRIMARY PRIMARY 4 db84771_viaempresas.a_i0.id_invertido 1 Using index 6 DEPENDENT SUBQUERY ngrama const PRIMARY,ngrama ngrama 5 const 1 Using index 6 DEPENDENT SUBQUERY invertido_ngrama eq_ref PRIMARY,id_palabra,id_ngrama PRIMARY 8 func,const 1 Using index 5 DEPENDENT SUBQUERY ngrama const PRIMARY,ngrama ngrama 5 const 1 Using index 5 DEPENDENT SUBQUERY invertido_ngrama eq_ref PRIMARY,id_palabra,id_ngrama PRIMARY 8 func,const 1 Using index 4 DEPENDENT SUBQUERY ngrama const PRIMARY,ngrama ngrama 5 const 1 Using index 4 DEPENDENT SUBQUERY invertido_ngrama eq_ref PRIMARY,id_palabra,id_ngrama PRIMARY 8 func,const 1 Using index 3 DEPENDENT SUBQUERY ngrama const PRIMARY,ngrama ngrama 5 const 1 Using index 3 DEPENDENT SUBQUERY invertido_ngrama eq_ref PRIMARY,id_palabra,id_ngrama PRIMARY 8 func,const 1 Using index 2 DEPENDENT SUBQUERY ngrama const PRIMARY,ngrama ngrama 5 const 1 Using index 2 DEPENDENT SUBQUERY invertido_ngrama eq_ref PRIMARY,id_palabra,id_ngrama PRIMARY 8 func,const 1 Using index

  Read the article

• Select latest group by in nhibernate

  - by Kendrick

  I have Canine and CanineHandler objects in my application. The CanineHandler object has a PersonID (which references a completely different database), an EffectiveDate (which specifies when a handler started with the canine), and a FK reference to the Canine (CanineID). Given a specific PersonID, I want to find all canines they're currently responsible for. The (simplified) query I'd use in SQL would be: Select Canine.* from Canine inner join CanineHandler on(CanineHandler.CanineID=Canine.CanineID) inner join (select CanineID,Max(EffectiveDate) MaxEffectiveDate from caninehandler group by CanineID) as CurrentHandler on(CurrentHandler.CanineID=CanineHandler.CanineID and CurrentHandler.MaxEffectiveDate=CanineHandler.EffectiveDate) where CanineHandler.HandlerPersonID=@PersonID Edit: Added mapping files below: <class name="CanineHandler" table="CanineHandler" schema="dbo"> <id name="CanineHandlerID" type="Int32"> <generator class="identity" /> </id> <property name="EffectiveDate" type="DateTime" precision="16" not-null="true" /> <property name="HandlerPersonID" type="Int64" precision="19" not-null="true" /> <many-to-one name="Canine" class="Canine" column="CanineID" not-null="true" access="field.camelcase-underscore" /> </class> <class name="Canine" table="Canine"> <id name="CanineID" type="Int32"> <generator class="identity" /> </id> <property name="Name" type="String" length="64" not-null="true" /> ... <set name="CanineHandlers" table="CanineHandler" inverse="true" order-by="EffectiveDate desc" cascade="save-update" access="field.camelcase-underscore"> <key column="CanineID" /> <one-to-many class="CanineHandler" /> </set> <property name="IsDeleted" type="Boolean" not-null="true" /> </class> I haven't tried yet, but I'm guessing I could do this in HQL. I haven't had to write anything in HQL yet, so I'll have to tackle that eventually anyway, but my question is whether/how I can do this sub-query with the criterion/subqueries objects. I got as far as creating the following detached criteria: DetachedCriteria effectiveHandlers = DetachedCriteria.For<Canine>() .SetProjection(Projections.ProjectionList() .Add(Projections.Max("EffectiveDate"),"MaxEffectiveDate") .Add(Projections.GroupProperty("CanineID"),"handledCanineID") ); but I can't figure out how to do the inner join. If I do this: Session.CreateCriteria<Canine>() .CreateCriteria("CanineHandler", "handler", NHibernate.SqlCommand.JoinType.InnerJoin) .List<Canine>(); I get an error "could not resolve property: CanineHandler of: OPS.CanineApp.Model.Canine". Obviously I'm missing something(s) but from the documentation I got the impression that should return a list of Canines that have handlers (possibly with duplicates). Until I can make this work, adding the subquery isn't going to work... I've found similar questions, such as http://stackoverflow.com/questions/747382/only-get-latest-results-using-nhibernate but none of the answers really seem to apply with the kind of direct result I'm looking for. Any help or suggestion is greatly appreciated.

  Read the article

• Help Converting T-SQL Query to LINQ Query

  - by campbelt

  I am new to LINQ, and so am struggle over some queries that I'm sure are pretty simple. In any case, I have been hiting my head against this for a while, but I'm stumped. Can anyone here help me convert this T-SQL query into a LINQ query? Once I see how it is done, I'm sure I'll have some question about the syntax: SELECT BlogTitle FROM Blogs b JOIN BlogComments bc ON b.BlogID = bc.BlogID WHERE b.Deleted = 0 AND b.Draft = 0 AND b.[Default] = 0 AND bc.Deleted = 0 GROUP BY BlogTitle ORDER BY MAX([bc].[Timestamp]) DESC Just to show that I have tried to solve this on my own, here is what I've come up with so far, though it doesn't compile, let alone work ... var iqueryable = from blog in db.Blogs join blogComment in db.BlogComments on blog.BlogID equals blogComment.BlogID where blog.Deleted == false && blog.Draft == false && blog.Default == false && blogComment.Deleted == false group blogComment by blog.BlogID into blogGroup orderby blogGroup.Max(blogComment => blogComment.Timestamp) select blogGroup;

  Read the article

< Previous Page | 64 65 66 67 68 69 70 71 72 73 74 75  | Next Page >