I am attempting to create a report that will display all the yearly, and monthly gas production for a company but the issue I am encountering is finding amended records for a lease. Below is what the query produces:
Company Number Year Month Gas Production Lease Number
3125 20091 296 9105701
3125 20091 345 165207
3125 20091 565 2250593 <--
3125 2009 1 1161 2250593
I would like to omit the lower production gas production, and keep the higher production from the same lease.
f_namef_countryf_ID ABCUS123 DEFGB123 ABCUS456 GHIGB789 etc.
I need to run a query to discover all instances where a f_name and f_country pair exists for more than one f_ID. ABC/US is one such example; IDs 123 and 456 have this pair.
Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!
Hopefully you can assist this pleb (me) with some (hopefully) basic scripting.
I have a table which has bucket loads of rows in it (funnily enough) Field1 & Field2 Field1 is repeated numerous times (but also has differing values) through the table Field2 is a numeric value which is assigned to the Field1
What I need to do is pull back all the unique Field1's but only those with the highest value in Field2
I have a table stock, on the table I have company_name, stock_value, sector. Now I want to get top five rows from this table depending on the five highest value of stock_value and for each sector. The query has to run on both oracle DB and Microsoft SQL Server with top priority MSSQL. I will appreciate if you help me on this
I've got a SPROC that generates a recordset of user vote tallies (they're calculated in a separated SPROC). The user submissions are grouped by a GUID value so as to remain unique for a user's submission (each user can have multiple submissions.
The problem is that the recordset returned displays ALL the users, and I'd like to only select the highest score for each user. So, if I have 500 submissions from 3 users (User1 and User2 submit once each and User3 submits 497 times), the total recordset will have 3 rows - being the highest score per user, discounting the others.
Here's my base query:
SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints] FROM Users a INNER JOIN Ballots b ON a.UserID = b.UserID GROUP BY a.UserID, a.Name,b.SubmissionGUID ORDER BY [TotalPoints] DESC,[Name] ASC
...and I've been able to get the highest vote per user, discounting duplicate entries, by using this:
SELECT a.UserID,MAX(b.TotalTally) AS [TotalPoints] FROM Users a INNER JOIN Ballots b ON a.UserID = b.UserID GROUP BY a.UserID
How can I write combine the two in a nested subquery to display only the top score per user?
I need to calculate average of each student and then get the highest.
My try:
Code: SELECT Students.StudentId,Students.StudentName,AVG(Grades.Grade) AS avg_grade FROM Students s JOIN Grades g ON Grades.StudentId =Students.StudentId GROUP BY Students.StudentId, Students.StudentName
ORDER BY avg_grade LIMIT 1 FROM Students;
I encounter problem with this code, maybe it's Completely wrong...
SELECT MAX(CountCategories.countOfCategory) AS maxCategory, CountCategories.phoneNumber, CountCategories.category FROM (SELECT Categories.phoneNumber, Categories.category, Count(Categories.category) AS CountOfcategory FROM Categories GROUP BY Categories.phoneNumber, Categories.category) AS CountCategories GROUP BY CountCategories.phoneNumber, CountCategories.category;
As you can see I have a sub query that is counting categories for the same phoneNumber and category. I then create a Select query about this as I wanted to only return the highest result. However the above query is returning the same as the subquery.It isn't showing me the unique result for the highest count.
I have a need to create a line graph report in SSRS 2008. The report should show the top 10 servers from a group of servers with the highest CPU utilization for the last day. The report is for Microsoft System Center Operations Manager 2012. I have a SQL query that will return the average CPU for all of the servers in the group, with the average for each hour (24 records per server).
How can I get the top 10 servers with the highest average CPU? I think I need to create an average of the averages, then select the top 10. Here is the SQL query I have so far:
Use OperationsManagerDW GO SELECT vPerf.DateTime, vPerf.SampleCount, cast(vPerf.AverageValue as numeric(10,2)) as AverageCPU, vPerformanceRuleInstance.InstanceName,
So if someone makes a change to a vendor address, it creates a new row with the new address and a new "RowVersion" number is created. Right now this query will pull both addresses. I need it to pull the row with the highest rowversion number. The datatype for the RowVersion column is "int", and VEND_ID I believe is the unique identifier for the rowversion.
I have this query that returns the largest value in a row, but i need to know the column name that this value is in as well. any help in advance is appreciated
select clientID, (select max(incomeValue) from (select earnings as incomeValue union all select unemployment union all select pensionRetirement union all select alimony union all select childSupport union all select dividendInterest union all select SS union all select SSI union all select SSDI union all select veteranBenefits union all select FIP union all select workStudy union all select other union all select otherHHWS) as income) as MaxIncomeValue from tbl_income
I have inherited a half-finished sql-server based project from a recently departed coworker. The critial point of this project is one app thread that reads barcodes, queries a single table in the database for the one record with that code as its primary key, and makes desisions based on that record. The faster that I can make that go, the better the process will run, up to a max rate as high as 20 queries per second if that were possible. I have a limited general knowledge of sql, but very little of sql-server express.
My question is what is the best way with sql-server to maximize my single-table request rate?? On some other databases I could create an in-memory temp copy of the table with trigger events on the main table to keep the copy in sync, or I could do an initial select on the entire table to hopefully get the table into cache memory, or I could use some kind of ado-like table on the app side (but do I really gain much of anything doing this??)
With SQL server, what is my best approach to maximize my throughput under these conditions??
FYI..The c++ app uses direct odbc calls to a localhost database. Table theoretically could have 75000 ever-changing records in it. There are 5 or 6 other processes also hitting on this table, but at a far more lakadaisical (say once every 10 seconds level) rate.
I have a table like TradeID ActionID 58096 3663 58096 3664 78901 2235 78901 2236
I want to select the only the TradeID with the highest ActionID
I tried using select distinct tradeid,actionid From cct Where ActionID = (SELECT MAX(ActionID) FROM cct1 WHERE cct1.TradeID = cct.TradeID) group by tradeid,actionid
I'd like to know the current value of my uniqueID column before Icreate a new record.Is there a way to find out this value?It is numeric in my case, but I can't just look for the MAX value,since some records may have been deleted, and the value for theuniqueID still stays at the higher value.Is there a way to read this internally kept value?
Is there a function that compares two columns in a row and will returnthe highest of the two values? Something like:Acct Total_Dollars Collected Total_Dollars_Due11233 900.00 1000.00Declare @Value as moneyset @Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)Print @ValueThis function will return 1000.00 or the Total_dollars_Due??Is there such a creature???
I have a table like Tradeid ActionID 3664 58096 3665 58096 3666 58097 3667 58097 3668 58098 3669 58098 I want to select the only the rows representing TradeID with the highest ActionID like 3665 58096 3667 58097 3668 58098
I tried using select distinct tradeid,actionid From cct Where ActionID = (SELECT MAX(ActionID) FROM cct1 WHERE cct1.TradeID = cct.TradeID) group by tradeid,actionid
I have recently decided to dedupe my data but i am having a problem after running fuzzy grouping with the query on updating which duplicate to keep
_key_in is unique, _key_out is the duplicates so for example:
_key_in , _key_out , name , score , dedupe 1 , 1 , ron , 10 , purge 2 , 1 , ronn , 15 , keep 3 , 3 , john , 5 , keep 4 , 4 , matt , 15 , keep 5 , 4 , mat , 10 , purge 6 , 4 , matt , 15 , purge
I want to keep the _key_out with the higher score by setting the field de_dupe to 'keep' and the remainder to 'purge'. The score can also be the same within a duplicate so in the case it is the same i just need to keep one it doesnt matter which one. The query i have below nearly works but it marks duplicates with the same score as keep.
Code: UPDATE b SET b.dedupe_result = 'keep' FROM [BusinessListings].[dbo].[MongoOrganisationACTM1Destination] b INNER JOIN
I am looking to order this table by CardNo and then TransNo but i only want the query to display the record with the highest TransNo for each CardNo. In other words discard the records with the lower TransNo for each CardNo.
My desired result should hopefully look something like this:
I have a query which finds duplicate spec_items linked to a work order. What I want to do it remove the duplicates (and in some cases there will be more than one) leaving only the record with the highest [sr.id]
select sr.id, sr.linked_to_worknumber, sr.spec_checklist_id from spec_checklist_remind sr inner join spec_checklist_remind sc on sc.linked_to_worknumber = sr.linked_to_worknumber group by sr.id,sr.linked_to_worknumber, sr.spec_checklist_id Having sr.spec_checklist_id = 30 and count(*)>1 order by sr.linked_to_worknumber
