SQL Query - Execute A Sql Query That Will Display The Top 5 Based On Average?

May 17, 2012

I have a data table that looks like this:

USERNAME ICOUNT IAVERAGE DATE_LOGGED
LAGX01 1245 1245 05-07-2012
LAGX02 2211 1422 05-07-2012
LAGX03 1698 1112 05-07-2012
LAGX04 4598 1555 05-07-2012
LAGX05 2589 3245 05-07-2012
LAGX06 3321 1155 05-07-2012
LAGX07 3698 3458 05-07-2012
LAGX08 2589 4587 05-07-2012
LAGX09 1598 1142 05-07-2012
LAGX10 3156 1987 05-07-2012
LAGX11 5547 2011 05-07-2012
LAGX12 9456 3459 05-07-2012

Now, I want to execute a sql query that will display the top 5 based on average, so i did this:

SELECT DISTINCT USERNAME,IAVERAGE FROM myTable WHERE IAVERAGE > 0 AND DATE_LOGGED='05-07-2012' ORDER BY IAVERAGE LIMIT 0,5 and the result is:

USERNAME ICOUNT IAVERAGE DATE_LOGGED
LAGX03 1698 1112 05-07-2012
LAGX09 1598 1142 05-07-2012
LAGX06 3321 1155 05-07-2012
LAGX01 1245 1245 05-07-2012
LAGX02 2211 1422 05-07-2012

Now, I want to numberize my query result, so it will look like this:

RANK USERNAME ICOUNT IAVERAGE DATE_LOGGED

1 LAGX03 1698 1112 05-07-2012
2 LAGX09 1598 1142 05-07-2012
3 LAGX06 3321 1155 05-07-2012
4 LAGX01 1245 1245 05-07-2012
5 LAGX02 2211 1422 05-07-2012

View 4 Replies


ADVERTISEMENT

Parameterized Query : Works With Execute Query But Not In Code?

Oct 22, 2009

I have created a parameterized query in my tableadapter. Here is the SQL:

SELECT CalcinerID, Comments, CorrectiveActions, CrucibleOxideWt, CrucibleWeight, DCPressure, DCTemperature,
Date, DischargePressure, FeedPressure, FiredCrucibleOxideWt, GmSampleWt, LIMSNumber, LotNumber, MaterialType,
OperatorID, QualLabOperator, QualLabTimeStamp, RecordID, ToteBinNumber [code]....

When I use the Execute Query button in the Tableadapter Query Builder, the query works fine; I get all the rows I expect. However, when I run this in my code, the query behaves like an equality statement (i.e. Lotnumber = @Lotnumber) rather than a LIKE statement (Lotnumber LIKE '%' + @Lotnumber + '%').Here is the code that is not behaving correctly. Note that I have a textbox, LotSearchTextbox, where I enter the portion of the Lotnumber that I want to use as the filter. I also have a button, LotSearchButton:

Private Sub LotSearchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LotSearchButton.Click
Try
Dim SomeNumber As Integer = Me.LotCalcinerO2TableAdapter.FillByLotNumber(Me.CalcinerDBDataSet.LotCalcinerO2,[code].....

why the query works in the Query Builder but not in the code?

View 4 Replies

How To Find Average In LINQ Query

May 3, 2010

I tried the following code:
Dim T5NHCEDCavg As Double
qry = From employee In empList
Where employee.HCEDC = False And employee.X5 = False _
Select employee.DCFSA
T5NHCEDCavg = qry.Average

But I'm getting a design-time error that says "Overload resolution failed because no accessible 'Average' accepts this number of arguments." I realize that I'm missing an overload here, but I'm not sure what to use or how to do this. I'm pretty new to LINQ. I've got a dataset in memory that I'm querying, and there is a double-precision property DCFSA, of which I'm trying to get the average based on my query above.

View 4 Replies

Sql - When Run The Query In Query Analyzer, It Returns One Row But When Use The Same Query, No Rows Are Returned?

Aug 19, 2010

Here is the code:

Function getData(ByVal id As String)
Dim reader As SqlClient.SqlDataReader
Dim statement As String

[code].....

View 1 Replies

Execute Sql Query On .net?

Jan 30, 2012

ALTER DATABASE test SET ENABLE_BROKER this is the sql query i wish to execute while program is executing.because i do not want to everytime i change computer also need open sql management tool to execute this query rath

View 3 Replies

Execute A Query In Program?

Oct 19, 2009

There is a "submit" button and a textbox, when the button is pressed, i want to connect to access database and search for the entered text in the textbox . if true i want to show "okay" a string .

View 4 Replies

Execute A Query In VS 2008

Aug 22, 2010

How to execute a query in visual studio 2008. What is the smooth process?

View 2 Replies

Execute Non Query Error?

Feb 18, 2010

i got an execute non query error... heres the

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim da As New OleDb.OleDbDataAdapter
Dim ds As New DataSet
Dim obj As New OleDb.OleDbCommand

[code]....

View 4 Replies

.net - Execute A LINQ-Query Immediately?

Jan 25, 2012

I don't like the verbose way of adding DataRows one by one via Tbl.Rows.Add, so i've created this pseudo LINQ query that does nothing but adding rows implicitely:

Private Function GetData() As DataTable
Dim years = {"2010/2009", "2009/2008", "2008/2007", "2007/2006", "2006/2005", "2005/2004", "2004/2003"}
Dim tbl = New DataTable
tbl.Columns.Add(New DataColumn("Year"))

[code]....

As you can see i don't need the result of the query at all, it's only purpose is to iterate and call DataTable.Rows.Add. So the result is already available in the DataTable itself and not needed from the query.Admittedly this question is somewhat hyphotetical because there would not be a big difference in using f.e. ToList and normally a query's purpose is to return something.But anyway, what's the cheapest way(in terms of memory consumption, execution time) to execute a LINQ query when only the execution and not the result matters?Edit: Ok, thisquestion was a quick-shot and a chicken-and-egg problem. I wanted to reduce 20 lines of code to a single line but noticed that i need some kind of DataSource for the LINQ-Query. Therefor i've created the Array. But at this point i could have simply used a for-each loop as well to add the DataRows.

Conclusion: Use a LINQ-Query for what it's for: a query. Then this question is pointless since there is no cheapest way but only that which returns the correct result.

View 2 Replies

Have A LINQ Query Execute In A BackgroundWorker?

Jan 22, 2010

I would like to run a query in background (using a BackgroundWorker) in this way[code]...

...but, because of deferred execution, the actual getting of the data does not happen in the "BackgroundWorker" thread, but in the "RunWorkerCompleted" Sub, making the BackgroundWorker itself useless.Is there a right way to do this without looping through the "MyQuery" to get the results and copying them to another object to pass it to the BindingSource?

View 2 Replies

LINQ Compiled Query Won't Execute

Feb 7, 2011

I'm testing the Compiled Queries for the first time.[code]...

View 3 Replies

Query If It Is More Than 1 Row - Execute Start.show()

Apr 29, 2009

I'm trying this for long time and I still can not figure it, I'm trying to query a database "password" with the field to check if the login is there "login" and if it find the login in the table execute start.show() but I still geting errors.

code.

Imports System.Data.SqlClient
Imports System.Data
Public Class login

[CODE]...

View 4 Replies

When Creating A Query Error "The Schema Returned By The New Query Differs From The Base Query"

Feb 29, 2012

When Creating a query using the sear Criteria Builder . I keep getting the error : "The schema returned by the new query differs from the base query" what does that mean and how do i avoid this problem in future

View 2 Replies

Execute My Query To Insert Records In Tables?

Aug 17, 2009

How can i execute my query to insert records in tables?[code]...

View 7 Replies

Make Property Or Directly Execute Query?

Jul 7, 2010

Public Class Customer
Private m_CID As Integer
Private m_FirstName As String

[Code].....

My question is that do I need to add an OID in customer class or I can directly send a query to customer table when ever an order is made.

View 1 Replies

Trigger Database - Execute Automatically - Query In Code

May 17, 2012

Whenever I run the form and complete the procedure then I need to go back to sql database and click on execute. I want to avoid everytime going back and executing each entry because if I don't execute everytime then the further other [b]form data [/b]will not be[b] retrieved[/b] properly as the [b]previous form[/b] will [b]not be updated[/b]. I hope ur understanding ...

[Code]...

View 1 Replies

Use OleDbDataReader To Execute Union Query In MS Access Database

May 6, 2010

I'm new to VB.NET. I'm trying very hard to not go screaming back to VB6...... I have a Query stored in an Access2000 database called 'build_VNMON'. (Does that make it a Stored Procedure ?) It uses multiple tables and multiple Union Statements to arrive at the desired results. It may not need to be as convoluted as it is, but I inherited it, and it works. What I really need is to output one column of the build_VNMON result to a text file, so I'm trying to use an OleDbDataReader.

[Code]...

View 2 Replies

Create A Module That Will Contain All General SQL Functionality Like Connect - Disconnect And Execute A Sql Query

Sep 8, 2010

I'm fairly new to VB.NET and I'm looking to create a module that will contain all general SQL functionality like connect, disconnect and execute a sql query etc. I think I'm almost there but the code keeps bombing in one place. Can anyone see what is wrong with the following code?

[Code]...

View 1 Replies

Create A LINQ Query Based On SQL?

Sep 25, 2010

i try to convert this kind of sql query into linq in visual basic, but i get stuck on how to make a percent.. I also don't know how to use linqpad for creating this linq

SELECT CASE RIGHT(PICName, 3)
WHEN '(P)' THEN 'Problem'
WHEN '(R)' THEN 'Request'

[Code].....

View 3 Replies

Query Database Based On A Value In A Label

Mar 11, 2010

i have a problem trying to query my database based on a value in a label. Here is the code for the query,

[Code]...

View 2 Replies

Sql - Get A Single Query To Update Based On The Id?

Dec 5, 2010

i need to update the table based on the two user.

for eg:

update table1 set rating=@rate where User1=@userid / user2=@userid

so i need to write a single query which satisfy the condition for both the users user1 and user2.if i pass the @userid ,if it matches the user's id it should update user1 record. if the id whcih i pass is user2's id then it should update the user2 rec.How to check for this condition in a single query...

View 3 Replies

TableAdapter Query Based On A Textbox Value?

Apr 24, 2010

I have a tableadapter where the select query is as follows:

I want to select only those rows where the value of orderno is in the TextBox1.text. How can i do this?

SELECT CustomerID, Orderno, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax
FROM Customers

View 5 Replies

Convert This String-based Sql Query To Use Linq?

Feb 3, 2011

I have the following routine (that works) but which is messy to update owing to the hand-typed strings it uses:

Private Sub ListDefaults()
Dim conn As New SqlConnection( _
"server=bas047AUTODESKVAULT;Database=DWGDetails;Integrated Security=SSPI")
'Dim conn As New SqlConnection( _

[code]....

View 1 Replies

Copy Files Based On Query Results?

Mar 19, 2012

I have created a database that allows me to sort through the hundreds of pictures I have based on certain criteria. The file path for each picture is stored in a text box in the database (file path). I am currently using this code to copy the files to a new folder:

My.Computer.FileSystem.CopyFile(file_pathtextbox.text, �E:photo_gallary�)

However, this only copies the first file in my query not all the others. I am at a loss as how to loop through them all so they all end up in their new destination.

View 8 Replies

Textbox Value To Increment Based On Mysql Query?

Sep 27, 2011

i am trying to insert/save the listview item/s on the database.what i am trying to do now is if the product is being entered/saved for the first time.the fcode should be 1, and increments after that.i have a combo box and a text box.the combo box items are from the database [ product table ]

let say user choose product 1

and the list view has 2 items [ a, b ]

and then save

the table would look like

fcode -- prod_id -- prod_name -- item
1 1 product 1 a

1 1 product 1 b[code]......

View 8 Replies

VS 2008 : Build Query Based On Two Variables?

Dec 29, 2010

In my program I have a combo box that the user selects a date from and a list view that the user selects an employee ID from. How do I properly design my database query string to use both the date selected and the employee ID selected? The date is stored in a variable called YearMonth and the employee ID is stored in a variable called EmpID.

Here is what I am trying to do with my query:

Dim ExactQuery As String = _
"SELECT * " & _
"FROM[JobTypeExact_Query] WHERE (EMPID = ?) AND WHERE (YEARMONTH = YM)" 'Define Exact job type query

[code]....

View 6 Replies

VS 2008 Inserting Records Based Upon A Query In .net?

Oct 19, 2011

I'm getting an error on my index simply setting a string field prior to using it and entering the loop. When I set this field within the loop I get a error still. how to go about Inserting records based upon a query in vb .net? The following is the code that I have:

Public Sub Insert_RunsizeUPCs()
Dim Command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim dsin As New DataSet

[code]....

View 2 Replies

VS 2010 Table Adapters Based On Query?

Mar 17, 2011

I would like to know if it is possible for a Table Adapter to reference a query as opposed to a table. Currently my Table Adapter references a table in my SQL database, but I wanted to know if it was possible to have it reference a query, which selects a table from another database?

View 5 Replies

Asp.net - Select Query In LINQ Based On Foreign Table

Mar 2, 2011

I have 2 Tables , OrderDetails and Requests In my LINQ to SQL dbml file. OrderDetailsID is a foreign key in Requests Table.

I want to write the query to get the sum of UnitCost from OrderDetails based on OrderId. And If there is a row in Requests Table for each OrderDetailsID, and the Requests.Last.RequestType="Refund" I want to reduce the total refund amount from the main sum otherwise If there is no row based on OrderDetailsID, add to sum.

Here is the way I implement that. I am looking to prevent using "For each".

iRefund = (From od1 In dc.OrderDetails _
Where od1.OrderID =1 _
Select od1.UnitCost).Sum

[Code]....

View 1 Replies

Cannot Filter A Binding Source Based On A Linq Query

Feb 3, 2010

I've got a datacontext and created a binding source using LINQ to sql but cannot filter said bindingsource. The supportsfilter of the bindingsource is set to false ? why ? I can't find any info to say that I cannot filter a binding source based on a linq query.[code]

View 3 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved