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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

selecting a value of one column based on the max value of another in a subquery

Posted By:      Posted Date: October 14, 2010    Points: 0   Category :Sql Server
 

I would like to select the value of column c  that is based on the max of column b

select q.a,q.c

(select a,max(b) as mx,c

from  a table group by a,c)q

when I try to select the row I get multiple rows

I need to return one row that corresponds to the max in col b




View Complete Post


More Related Resource Links

How to filter the search result based on a column value?

  
I do have search scope scoped at a list which contains attachment. I will use this scope to search (full text search) on this list then I need to filter the result based on a column which is in the same list. How do I do that? Thank you.

Filtering/selecting rows based on date

  

I have a gridview, based on thsi datasource:

 

<asp:SqlDataSource ID="SqlDataSourceAllLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>"               
        SelectCommand="SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?') AS Expr1 FROM tblDelivered FULL OUTER JOIN tblDeliveredInfo ON tblDelivered.delivered_LoadNo = tblDeliveredInfo.deliveryInfo_LoadNr ORDER BY delivered_Date DESC">
    </asp:SqlDataSource>


Using a dropdownlist, I can filter (by changing datasource) the results based on customer names in a dropdownlist. This datasource, working perfectly, looks like this:

 

<asp:SqlDataSource ID="sqldataSourceKundeLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>" 
        SelectCommand="SELECT  DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?&#

can alias in select be used for selecting other column in that table?

  
Hi All,I want to use an alias name in a select clause to select other column in that table? select   top 1 (   case when CreatedByName <> '' then 'yy'         else 'xx' end) as filName, (filName + 'xx')from Order       But it throws error like " Invalid column name 'fileName'."Could you please help me out?

changing font color on a data column field based on the contents

  
I am trying to change font color if the datafield has the string EMERGENCY. This is my original template field: <asp:TemplateField HeaderText="Type" SortExpression="DigType"> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("DigType") %>' ></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("DigType") %>' ></asp:Label> </ItemTemplate> </asp:TemplateField>   and this is what I thought would work: <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("DigType") %>' ForeColor='<%# IIf(Eval("DigType")="EMERGENCY","Black", "Red") %>'></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("DigType") %>' ForeColor='<%# IIf(Eval(&quo

how can we split a datatable into two ,based on a column at runtime

  
hi , ihave a problem that to assaign values to two listviews from single datatable based on a column which have values 0, 1. it is wpf app the listview has a grid . i need to specify the result data as datacontext

Assigning a task based on 'person' column.

  
Hi All, I am creating an employee of the month type feature in Visual Studio. This involves a nominations list (where users can submit a nomination) and a custom task list (based on workflow tasks) In a nomination, a "Line Manager" is entered, and is set up as a Person Column. In code, I create a task, and I am trying to assign the task to the line manager. The code I have is: taskProperties.AssignedTo = workflowProperties.Item[ "Line Manager"].ToString(); This does return the name of the nominator (but with a prefix, usually #795; (or something like that)). How can I assign a task in code to a user based on the selection of a Person field (chosen using People Picker)?    Andrew Berry - Software analyst/Developer

Column Based Analytical Technology (CBAT) Alterion

  
Hi, Has anybody heard about Alterion ? I wanted to know if Data Mining in SSAS can replace the Alterion Analytical tool. After going through couple of articles I found that this technology is quite different than OLAP system. In OLAP we summarize the data and anlayse the performance of business in summary level. But Alterion is used for the analysis of detail level of data. I am totally new at Data Mining. Can Data Mining do the similar kind of Job of analysing detail level of data ? Most of the time, we have to perform analysis on Marketing data. For Example : There will be different sort of surveys done containing lot of questions. Now we want to analyse those type of data and not the summary level of data. Any suggestions can help us to replace Alterion to use SSAS tool. Thanks,

Infopath 2007 Repeating Table - Multiple Value Column Text - Hiding Rows based on Column text values

  
Infopath 2007 browser based form Full Trust Example: I have a repeating table (FruitChoice) that has multiple columns. Both drop down list point to sharepoint list data sources. Choose your tree ft. drop down list – 6Ft Choose your Department drop down list - 103 This repeating table is conditional on the drop down values. This works great. Trees     Fruit       Cost   Date Ordered    Date Delivery Department 6Ft        Peaches                                                        103 3Ft        Apples                                                          102 3Ft        Peaches         &

Infopath 2007 Repeating Table hide row based on Text values of column

  
Infopath 2007 browser based form Full Trust Example: I have a repeating table (FruitChoice) that has multiple columns. Both drop down list point to sharepoint list data sources. Choose your tree ft. drop down list – 6Ft Choose your Department drop down list - 103 This repeating table is conditional on the drop down values. This works great. Trees     Fruit       Cost   Date Ordered    Date Delivery Department 6Ft        Peaches                                                        103 3Ft        Apples                                                          102 3Ft        Peaches         &

Create Lookup Site Column based on External List (SharePoint 2010)

  

I've got an External List and I want to create a Site Column, which is Lookup to a column in my external list. If I tick any of the columns in 'Add a column to show each of these additional fields' and click OK i get the following error message:

[NullReferenceException: Object reference not set to an instance of an object.]
  Microsoft.SharePoint.SPBusinessDataField.IsInDefaultView() +58
  Microsoft.SharePoint.SPBusinessDataField.CreateSpFieldForBdcType(String bdcName, Boolean hidden, Boolean readOnly, Boolean idField, SPAddFieldOptions op) +809
  Microsoft.SharePoint.SPBusinessDataField.AddNeededSecondaryFields(String wssNames, String[] oldNames, String[] newNames, SPAddFieldOptions op) +315
  Microsoft.SharePoint.SPBusinessDataField.OnAdded(SPAddFieldOptions op) +240
  Microsoft.SharePoint.SPFieldCollection.AddFieldAsXmlInternal(String schemaXml, Boolean addToDefaultView, SPAddFieldOptions op, Boolean isMigration, Boolean fResetCTCol) +759
  Microsoft.SharePoint.SPFieldCollection.Add(SPField field) +61
  Microsoft.SharePoint.ApplicationPages.FieldNewPage.Save() +513
  Microsoft.SharePoint.ApplicationPages.FieldNewPage.BtnOk_Click(Object sender, EventArgs e) +46
  System.Web.UI.WebControls.Button.OnClick(EventArgs e) +114
  System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +139
  Syste

Selecting a field based on a variable

  

If I have a field name in @FieldName is there a way to do something like

SET @FieldName = 'MyField'

SELECT @FieldName FROM MyTable

where the above would yeild the same results as

SELECT MyField from MyTable

Thanks in advance.


Selecting destinct Keys with highest value in another column

  

Hey guys,

I have a table with 2 columns that lookes something like this:

_________________________________

ChapterID  | VersionNumber  | VersionID |

1001          | 1                        | 1004         |  

1001          | 2                        | 1005         |

1002          | 1                        | 1006         |

1002          | 2                        | 1007         |

1002          | 3                        | 1008         |

1003   &nbs

Combine multiple xml elements into one column field based on subheading

  

I need to pull down data from a third party vendor's SQL view, where one of the columns is xml. I've never worked with xml before; unfortunately our vendor was not willing to change the format to what they were previously providing.

XML Example:

<ResponseDTO xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <Response1>
  <Messages>
   <string>Message1:</string>
   

Computing a value in a table based on another column

  

Hi All,

I have Yearly sales column which has to be calculated based on the date and monthly sales columns. Every month I get a record in a table with the date populated in the Date column and sales for that month in the monthly sales column. Now my task is to calculate the yearly sales for the last 12 months including that month. I am unable to come up with a logic. if my sales for the last 12 months were flat $100, then the yearly sales for the current month should be populated as $1200. 

Hope I made my point clear.

Thanks
Vineesh


vineesh1701

Delete row from datatable based on column value

  

Hi.

I am populating a datatable with data from MSSQL and I have a need to then remove all rows in the datatable that have a certain value in a specific column.

Is there any way I can remove rows from a datatable by specifying the value in the column it should identify the rows by that should be removed? (eg like in MSSQL how you can say delete from tablename where field=value)?


Auto Populating SharePoint List Columns based on value in one column

  
Hi!
   I have a SharePoint List where I want to auto populate the value of some of the columns from another List based on a field "Title" which is primary & unique....
   I want to auto populate them when clicking "New Item"  and entering data... you put the "Title" value and it should match the Title value from another List and auto-populate some the columns...

   Please any idea would be highly appreciated....

Thanks in advance

Selecting record based on domain url and email comparison

  

I want to limit the selecting of data records to only the user who created the record, OR a users who's email address belongs to the domain of the record being edited.
So, if the producer has a value in the URL of "www.mydomain.com" and their email address in the Membership is "someone@mydomain.com" they will be able to see the record.


I have the following table:
Table -  Producer
Field: ProducerID
Field: ItemUserID
Field: URL

I have figured out how to select based on the ItemUserID (see code below), but I don't know how to compair the email address values of URL and the Membership.


protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
 {
      if (Page.User.Identity.IsAuthenticated)
    {
     //get id of user logged in
     e.Command.Parameters["@ItemUserID"].Value = Membership.GetUser().ProviderUserKey;
    }
        
 }

Can someone show me how to code my requirements?
Thanks.


Categories: 
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