I am having trouble with this statement
[Code]....
I only want the record with the highest value in the EarnedPoints Column, but I get the error
Column 'customer_locations.City' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I am trying to display the total points Earned, the Client Name, City and State.
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 | 1 | 1009 |
1004 | 1 | 1010 |
what I wanna achieve is to have a statement that selects distinct uniqueid's from the chapterID field and with every duplicate I would like to have the record with the highest version number
Below is the result I would like to achieve:
ChapterID | VersionNumber | VersionID |
1001 | 2 | 1005 |
1002 | 3 | 1008 |
1003 | 1 | 1009 |
1004 | 1 | 1010 |
I have tried this statement:
Select Distinct ChapterID, max(VersionNumber) from Versions groupby ChapterID
and it works fine, but when I add the VersionID column like so:
Select Distinct ChapterID, VersionID, max(VersionNumber) from Versions group by ChapterID
it returns an error.
query to find the 5th highest salary from an employee table?
View 2 RepliesI am trying to get the highest, lowest and average values from displayed gridview and then highlight the numbers. I had this code working, but now, I don't know what's going on. It does NOT give me the correct values!!! What's wrong? For simplicity I just give the code of getting the highest number. Here is my code:
[Code]....
This obviously isn't right but I think best describes what I'm trying to do:
SELECT ImagesLge.MainImages
FROM pieces
INNER JOIN ImagesLge
ON ImagesLge.ParentId = pieces.ID
"WHERE pieces.ID = MAX(pieces.ID)"
I want to write the query for finding second highest salary in my employee table how to find it??
View 3 Repliesi try to use select statement to select records within a week from orderdate. like,
"select * from order where orderdate <= DATEADD(day,7,orderdate)";
correct me if wrong.
I have a column with tinyint records with (1,0,1,1,0,1 ...). I want to select the negation of this column while inserting in to another table.I need the ~ operation like working with bool. (1->0, 0->1). How to achieve this functionality when the column is tinyint ?
View 2 RepliesI have data saved in a sql server database which i am using to draw a graph with in asp.net (c#) one field is an INT and the other is a datetime At the moment the graph is only drawing for all the data in the table i was wondering if there is any way to add into the sql string a statement that says for only today or this week or this month.
this is my sql string at the moment:
SelectCommand="SELECT [Time], [Temp] FROM [test2] ORDER BY [Time]"<br>
the format that the datetime is in is "2011-03-29 00:46:06.377"
I'll admit that I'm not the best at SQL, but what I have is a database that has different podcasts in it. We have 3 locations, so each podcast contains three columns, one for each location. In each column is the date it was shown at each location, or null if it wasn't shown there. Most of the time, I'm selecting podcasts for a specific location, which is easy enough:
[Code]....
But what if I want to select them all? I want "Date" to always be the Hudson location, but if Hudson is null, I want it to be the date of the location it was shown at: AuroraDate or StowDate.
I have to come up with a way to get data from a SQL server table given it's month
There's a smalldatetime type field called "date" in the "events" table and a field from it looks like this:
29/01/2003 17:00:00
It should be one among those that appear in the gridview when I select jan/03 in a given DropDownList control in ASP.NET.
What would be the best way to do this? Assuming I only want to go as long as 10 years to the past, wouldn't making a dictionary be a good way?
How do I change it so the top month is always the current, and each month another options appeaars in the dropdown ?
It should be a common issue, so that shall not be a trick, but I want a good and clean way of doing this, and (since I'm new to asp.net) I myself would come up with something messy after a long time trying.
So what I want is:
1) Select
2) Best way of populating DropDownList (with the current month always at the top)
3) Best way to bind the DropDownList user-friendly way of writing months to the select query-friendly way of writing datetimes
Info: ASP.NET, (C# or VB.NET), SQL Server 2005
I have a Table with three columns :
MessageID (int)
MessageText (ntext)
ChannelID (int)
Now i want to get the list of message IDs where the message Text Contains some Special characters as like the characters from the below image. how to query to get the list of message ids where the message text contains any one of this special characters.
I have a piece of sql code that selects the top 10% of a table. See below a sample of the database table
And this is my code
[Code]....
So what this code does is add up all the points in the points table, (also works out what percentage of the points each user has) and then displays the top 10%, which in this case will give me "John" and "Dave"
So what I need the sql to do is check if any other records in that table have the same number of points ast the last selected record, and to select it to.
I just need to know how to select in c# select from my sql server database for the purpose of seeing if data already exists.. do not need to display the data (i know how to select the data and display it in a data grid) but I am checking to see if dates are already in the database before performing an insert since there can not be duplicate dates... it is late but I only can figure out how to use the data as it applies to viewing or if bound to a control. Also I am not using the aspx... in tags... my c# is seperate... do I have to use a stored procedure just to see if the row exists???
View 5 RepliesSay I have a table called "Visits", and I want to create a query that counts the visits per day. The way I would accomplish this would be to create a query as such:
SELECT Count(VisitId) as VisitCount, Convert(varchar(32), VisitDateTime, 101) as VisitDate
FROM Visits
WHERE VisitDateTime BETWEEN '10/1/2010' AND '10/5/2010'
GROUP BY VisitDate
The problem I am trying to overcome is that if there were no visits in a given day, that day does not show in my list. For example, if there were 3 visits on the 1st, 2 on the 2nd, none on the 3rd, and 5 on the 4th, my result set would look like:
3, 10/1/2010
2, 10/2/2010
4, 10/4/2010
How can I create my query so that I get all days in the date range listed in the result set regardless of whether the VisitCount is zero?
I basically have a series of tables from one database that have an identical structure thus making retrieval of all records fairly easy (I just use a UNION ALL statement). However I need a list of values from another table within a different database that contains information regarding the group of the data.
I so far have the following:
SELECT Table1.* FROM Table1 UNION ALL SELECT Table2.* FROM Table2 UNION ALL SELECT Table3.* FROM Table3 UNION SELECT Database.dbo.SRFILE.SR_GROUP FROM Database.dbo.SRFILE INNER JOIN Database.dbo.SRFILE.SRONUMBER = Table1.Incidentx
I keep receiving a unable to parse message however all I need to retreive is the SR_GROUP value but just don't know the correct syntax. Is it actually possible to do this as the structure of SRFILE is not the same as Table1/2/3.
I have an employee salary table with fields empId, month, year, salary. I need query for the following - Total salary drown each employee last year - Id of the employee who got highest salary last year. i am getting wrong o/p for these queries.
View 3 RepliesThe end goal is to be able to reference our address book, and give the user the ability to select a Manager, which then in turn would return all the results for all the users under that manager. This information is stored in our Exchange servers address book. ie when you select on a person's name in Outlook, then Organization, you get their manager, so when you select the Manager on this site, I want it to return the results for all of his/her members.
I have a site that has a dropdown list of users who've entered data into a DB. This list is populated with a User's alias when they enter data.This DB of data (including Users' aliases) contains data from users in multiple groups, and I am trying to figure out how I can access our Outlook exchange.
Say If got these dates in a TextBox 2010/06/19,2010/06/20,2010/06/21,2010/06/22,2010/06/23, i want automicly my Start label to pickpup the lowest date and Label End to pickup the highest Date Start: [2010/06/19] End: [2010/06/23] between brackets is my label
View 16 RepliesI have three dropdowns that are used to choose a date (day, month and year). I want them to initially display today's date. They are being populated with code like this:
[Code]....
So the user changes the value to whatever they want and hits the Submit button and I try to collect the values they have chosen...
Code:
DateTime returnDate = new DateTime(Convert.ToInt32(myRYear.SelectedValue), Convert.ToInt32(myRMonth.SelectedValue), Convert.ToInt32(myRDate.SelectedValue));
But the #@#$!@# thing returns today's date!
This is not what I want obviously. Can anyone tell me where I'm going wrong? Why doesn't it return the date as chosen?
If I remove the myRDate.SelectedIndex = DateTime.Now.Day line from the initial population statement the 3 dropdowns are set to 1/1/2010 but the chosen values are passed to the database correctly.
Is there another way to set the initial selection that will not affect the dropdown for all time.
<td><%= Html.DropDownList("Weight", Model.Weightddl.Select(x => new SelectListItem { Text = x.Name, Value = x.Id.ToString() }))%></td>
Here is my line of code. I have the value Model.Weight which I want to have preselected on the load... How can I set it as the selected value?
am having a problem in selecting a value from dropdown list. Regardless of whatever I choose only the first value is selected always. Here is the code...
protected void Button4_Click(object sender, EventArgs e)
{
SqlConnection con;
[[code]...
i'm using a strongly typed view with a custom viewmodel. this viewmodel contains a collection of books.
public IList<Books> Books { get; private set; }
i'm also passing a select-list to the view. however the DropDownListFor helper class doesn't automatically preselect the default value of the book.
@Html.DropDownListFor(m => Model.Books[i].AuthorID, Model.Author as SelectList)
everything else works, e.g. it does pass the selected value back to the controller.
i figured out a workaround by creating the select list in the viewmodel:
@Html.DropDownListFor(m => Model.Books[i].AuthorID, new SelectList(Model.AuthorList, "Value", "Text", @Model.Books[i].AuthorID.ToString()))
however i don't want to implement this workaround if there is a simpler solution. is there a way that the view can handle values from a collection?
I am novice to aso.net programming using vb.net.I have three text boxes on a form.I have set their tab order also.text boxes already have some initial values in them when the form loads.what I want to do is when I press the tab button and it sets the focus on the next text box it should automatically select the whole value in thetext box so that when I start typing It overwrites the old value I dont have to delete the old value first and then write.P.S. I want to do the same thing in VB.net.
View 11 RepliesMy HTML looks something like this:
<table class="disabled">
<tr>
<td>
<input blah blah>
</td>
<td>
<img id="reallyLongASP.NetID" etcetc/>
</td>
</tr>
</table>
In all with a class indicated as "disabled", I want to set visibility: hidden on the <img>. I can disable using the crazy id ASP.Net gives, but would like to do a general statement that affects every control in the <table>