Select Two Highest Value Per Foreign Key

Mar 25, 2008

Hi,

I need help on this one. Let's say I have a table like this:

Table_ID Value SomeOtherTable_ID
1 2 1
2 4 1
3 1 2
4 5 3
5 3 2
6 0 1

How can I get only two rows of each SomeOtherTable_ID? The result that I want is this:

SomeOtherTable_ID Value
1 4
1 2
2 3
2 1
3 5

Thanks in advance

View 2 Replies

Using TOP To Select The 2nd Highest Record

Jun 19, 2007

Hai frendz,

I am having a table named Employee(int EID, float Salary)...
Now I want to select the highest salary in the table and the query is-

"select top 1 EID, Salary from Employee ORDER BY Salary DESC"

Now I need to write a query which selects the second highest salary.
So how to achieve this?..

thanx

View 6 Replies View Related

Select Highest Version

Sep 21, 2004

Hello,

I have the following problem.

I have three Columns

name / version / package

I want to create a select query, that shows me all names and their actuall package. The actual Package is the record with the highest version number.

For Example my Database has the following entries :

name / version / package

name1 / 1 / package1
name2 / 1 / package1
name2 / 2 / package3
name3 / 1 / package2

The output should look like this :

name1 / 1 / package1
name2 / 2 / package3
name3 / 1 / package2

Has anyone a idea and can help me ?

Thanks in Advance

Mirco

View 4 Replies View Related

Select The Row With The Highest Number

Aug 26, 2004

Hi all,

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

Example:
Field1 Field2
blah 100
blah 400
blah1 12
blah1 9
blah1 2
blah 350

I need the return to be basically:

Field1 Field2
blah 400
blah1 12

I am sure that this has to be straight forward - however my brain just isn't connected the right way at the moment....

Thanks in advance for any assistance on this.

Troy

View 2 Replies View Related

SELECT Highest Value Of Duplicate Field

Mar 25, 2008

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

but the result is not correct

please help

In god we trust,everything else we test.

View 4 Replies View Related

Transact SQL :: UPDATE Then SELECT - Return Task With Highest Priority For A Given User

May 15, 2015

I have a stored proc which evaluates a table of 'tasks' to return the task with the highest priority for a given user - at the same time, once that task has been found - I want to allocate that user to the task.

I have created a stored proc that works...however I'm sure this requirement /pattern is common & I would like some best practice for this pattern of update and select within a transaction.

Here is some sample data:

use tempdb;
go
if OBJECT_ID('Tasks', 'U') is not null
drop table tasks;
go
create table tasks (
TaskId int identity primary key,

[Code] ....

And here's what my current stored proc looks like;

if OBJECT_ID('pGetNextTask', 'P') is not null
drop proc pGetNextTask;
go
create proc pGetNextTask (
@UserID char(10),
@TaskID int output
)

[Code] ....

View 3 Replies View Related

Get The Highest Value.

May 26, 2006

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!

View 3 Replies View Related

Get Highest Count

Jul 20, 2005

Hi all,Given:f1 f2-- --1 a2 b3 b4 b5 c6 c7 dHow to get the highest count for field f2, output as:f2 f2count-- -------b 3

View 1 Replies View Related

Highest Value Of Three Columns

Jan 23, 2008

I have four columns that include numbers like: A B C D

I want to update D with highest value of A, B or C.

For example A = 1, B = 2, C= 3 then D should be 3.

Can you help me?

View 1 Replies View Related

What Is The Highest Value Of An Int Data Type

Feb 27, 2007

Can someone tell me what the highest value (number) is for an int data type in sql server 2005?

View 2 Replies View Related

Find Highest Number

Sep 26, 2004

View 3 Replies View Related

Get Highest Count Of ItemNr

Dec 8, 2004

Got a table with salestatistics and I want to know what ItemNr sold most a certain day. Can anyone help me with the SQL?

View 1 Replies View Related

Retrieving Highest Paired Value

Jan 12, 2012

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.

View 3 Replies View Related

How To Obtain The Students'name With Highest GPA

Apr 12, 2006

I want to obtain the top 1 students' name in each grade,but this code
dosen't work:
select top 1 name
from student
group by grade
order by GPA desc

Can anyone tells me what to do?

View 6 Replies View Related

Selecting Top 5 Highest Rows

Sep 25, 2006

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

jideofor

View 2 Replies View Related

GROUP BY Highest Score Per User

Mar 4, 2005

Hi there!

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?

View 1 Replies View Related

Calculating Average For Each Student And Get The Highest

Feb 12, 2013

I want to calculate average of grades of each student and get the highest one with SQL command.

I have 2 tables:

Students:
*StudentId
*StudentName
___________
Grades:
*StudentId
*Grade
___________

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...

View 5 Replies View Related

Unique Result For Highest Count

Jul 9, 2015

I've got the following query

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.

View 1 Replies View Related

T-SQL (SS2K8) :: Get Top 10 Servers With Highest Average CPU?

Mar 9, 2015

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,

[code]....

View 2 Replies View Related

SQL Server 2008 :: Pull Highest Value For Each Row

Sep 10, 2015

I have a query that I need to be able to pull the highest value for each row. Here is the query:

SELECT
V.VEND_ID
, V.AP_CHK_VEND_ID
, V.VEND_NOTES
, V.VEND_NAME AS VEND_NAME
, V.VEND_LONG_NAME

[Code] ....

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.

View 2 Replies View Related

For Each Cust, Show Highest Sale

Apr 1, 2008

I haven't done any SQL in a while. Having trouble getting me head around this one.

I have a table Sales:
(Transaction_ID int, Customer_ID, Sales_DateTime Time, Sales_Amt Dollar)

I need a query that returns:
For each Customer_ID that has made a purchase
Show the highest dollar Sales_Amt they have ever made

In C++ I would probably do a recursive solution, is this doable? advisable? in T-SQL?

View 3 Replies View Related

Return Name Of Column Holding Highest Value

Apr 25, 2008

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

View 12 Replies View Related

Difference Between The Highest And Lowest Salaries

May 8, 2007

I need to display the difference between the highest and lowest salaries. Label the column DIFFERENCE. like below:

From what I came up with is should look something like so:

<b>SQL> select * from 'salary' order by salary desc limit
select * from 'salary' order by salary desc limit 1,1 </b>

DIFFERENCE
------------------
4200

View 8 Replies View Related

Highest Speed Data Access

Jan 21, 2008

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.

View 3 Replies View Related

How Do I Find The Highest Unique Identifier?

Sep 5, 2005

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?

View 5 Replies View Related

Function That Returns Highest Of Two Columns?

Jul 20, 2005

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???

View 2 Replies View Related

Get, Grouped, The Records With Highest Value In A Given Field

Apr 12, 2008

Hello all,

Here is an SQL Server 2005 table that lists player scores:

Code Snippet

+--------+--------+----------+
| NAME | POINTS | DATE |
+--------+--------+----------+
| Liz | 7 | 01/04/08 |
| Mark | 20 | 15/03/08 |
| John | 9 | 04/01/08 |
| Liz | 25 | 25/12/07 |
| Liz | 11 | 10/04/08 |
| Mark | 11 | 22/03/08 |
| Patty | 20 | 08/04/08 |
+--------+--------+----------+
I'd like to get, for each player, his/her best performance, including the date. Concretely, my SELECT query should return:

Code Snippet

+--------+--------+----------+
| NAME | POINTS | DATE |
+--------+--------+----------+
| Liz | 25 | 25/12/07 |
| Mark | 20 | 15/03/08 |
| John | 9 | 04/01/08 |
| Patty | 20 | 08/04/08 |
+--------+--------+----------+
does someone have any idea ?

Thx

View 3 Replies View Related

Selecting Highest ActionID For Each TradeID

Mar 26, 2008

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

but the result is not correct

please help

View 8 Replies View Related

What Is The Query To Find A 5th Highest Salary With Sqlserver.

Aug 9, 2005

What is the query to find a 5th highest salary.in emp table.i also use top1,top2,..but i don't get a result.what is new in sql server 2005.

View 11 Replies View Related

Query Highest Entries From Database - Pls Read More.....

Feb 6, 2006

Hi folks, sorry for the poor explanation.

Im using SQL 2000

I have a database that has a column named 'Initials' in a char in field

I want to be able to return in a query the highest entries if an indiviuals initials & count from the table, so it would display some like this

Initials Count
DRT 51
AMS 49
JJJ 21
PLI 10

Hope u can help, thanks in advance

View 2 Replies View Related

Keep Duplicate With Highest Score Fuzzy Grouping

Jan 22, 2012

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

[Code] ....

View 2 Replies View Related

Query To Return Highest TransNo Per CardNo?

Aug 14, 2013

structuring a query to give me the CurrBal per CardNo.

I have table that looks like this:

CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-04 11:50 AM 79 0 6
2 2013-08-05 15:34 PM 52 100 41
2 2013-08-05 14:11 PM -48 0 40
3 2013-07-09 13:52 PM 49 0 12
3 2013-07-22 13:51 PM 11 0 14
1 2013-06-12 10:46 AM 63 0 7
3 2013-07-15 14:04 PM 33 0 13
2 2013-08-06 15:05 PM 39 0 42
2 2013-08-07 13:38 PM 30 0 43

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:

CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-12 10:46 AM 63 0 7
2 2013-08-07 13:38 PM 30 0 43
3 2013-07-22 13:51 PM 11 0 14

I am using SQL 2012 Express but would also like this query to work in SQL 2005.

View 7 Replies View Related

Removing Duplicates Based On Highest Record ID

Dec 20, 2013

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

Sample of query results:

idlinked_to_worknumberspec_checklist_id
18323IP-DN-03377430
24229IP-DN-03377430
18294IP-DN-03380830
28027IP-DN-03380830
18295IP-DN-03381030
19924IP-DN-03381030

In the 3 cases, I would want to retain only records with ids 24229,28027 and 19924.

View 9 Replies View Related