Find Highest Number

Sep 26, 2004

hello all,

i'm not new to SQL but i cant seem to get this right:
hope some one will:

how to find highest unique number of a certain column(val)for a specific name(name is in INPUT parameter)

i.e :

id | name | val
---------------
1 | name1 | 2.7
2 | name1 | 3.5
3 | name1 | 3.5
4 | name1 | 3.5
5 | name1 | 1.3
6 | name2 | 3.1
7 | name2 | 3.1
8 | name2 | 2.9

requested result:
if input param(name)=name1

result:
1 | name1 | 2.7

if input param(name)=name2

result:
8 | name2 | 2.9

hope some one can write the sql cmd for this
i'll be grateful !
thank you!

View 3 Replies


ADVERTISEMENT

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

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

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

SQL Server 2008 :: Using Cursors To Find First / Highest And Last Values

Sep 20, 2015

I have basic knowledge of T-SQL and I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.

@FirstName NVARCHAR,
@LastName NVARCHAR,
@FirstValue decimal,
@HighestValue decimal,
@LastValue decimal

-- First Cursor

DECLARE TESTCURSOR CURSOR
DYNAMIC

[Code] ....

The above code seems totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.

SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id

View 9 Replies View Related

Find Highest Marks In Student Table Query

Oct 9, 2007



Hi Everybody
I've one table named Student. Here is data







Name
Subject
Mraks





Prasad
English
80

Tushar
English
79

Sunil
English
78

Prasad
Geometry
80

Tushar
Geometry
81

Sunil
Geometry
79

Prasad
History
82

Tushar
History
81

Sunil
History
80


Now I want to write a query that displays student with subjects and marks who secured highest marks in each subject.
I want to output like this





Name
Subject
Mraks





Prasad
English
80

Tushar
Geometry
81

Prasad
History
82

So will anybody help me to write a sql query that acheive the same output

View 5 Replies View Related

Finding The Highest Or Largest Number In A Column Problem

Dec 19, 2006

Example data
CA1000
CA10001
CA10002
CA10003
CA11597
CA11603
CA1001
CA998
CA999
As you can see, CA11603 is the largest number in this list. 
 When I try the follow sql code,
SELECT
MAX([MyCode])
FROM
[MyTable]
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
MAX([MyCode])
FROM
[MyTable]
WHERE [MyCode] LIKE 'CA%'
 The largest number comes back as CA997
 
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
 The largest comes back as CA10001
When I try....
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
 The largest comes back as CA1278
What am I doing wrong?

View 3 Replies View Related

Finding Highest Current Number Only For Increasing Range

Jan 26, 2012

RecordNo Speed
-------- -----
1 0
2 0
3 0
4 0
5 23
6 66
7 48
8 0
9 31
10 0
11 34
12 23

The above data shows the speed of vehicle over a time period, given the above data I need to achieve the result below:

RecordNo Speed LastAcceleration
-------- ----- ----------------
1 0 0
2 0 0
3 0 0
4 0 0
5 23 23
6 66 66
7 48 66
8 0 66
9 31 31
10 0 31
11 34 34
12 23 34

The code below is almost there but falls over on Recordno 8:

select
curr.recordno,curr.speed
,CASE WHEN curr.speed >= ISNULL(prev.speed,0) THEN curr.speed
ELSE (
SELECT MAX(speed) FROM speedtest
WHERE recordno between (CASE WHEN curr.speed >= prev.speed then curr.recordindex else prev.recordno end ) and curr.recordno

[code]...

View 4 Replies View Related

Database Of Employees - Find Highest Pay Rate For One Single Person

Jul 10, 2014

I have a database of employees and pay rates.

One employee has two pay rates for two different jobs:

Job A: Rate $10.00
Job B: Rate $15.00

I will be updating their record so that they only have one job going forward, Job C. I need Job C to equal their HIGHER of the two existing jobs.

I have a select statement to find what the higher rate is. However, I am not sure how I can apply the rate to be the new job's rate. Here's what I used to find the highest rate for one single person:

SELECT max(rate), employeeID
FROM JobsTable
inner join IDTable
on JobsID2 = IDID2

WHERE JobCode in ('JOBA','JOBB')
and EmployeeID = '12345'
GROUP BY EmployeeID

(this returns the employee ID from one table, and the highest rate from Jobs A and B from another table)

I can get it to update to add JobC -- how can I get it to assign the result from the above query to be the rate used for Job C?

View 1 Replies View Related

Identity...I Need To Get The Last (or Highest Number In Identity Column)...

Sep 19, 2005

Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint

View 2 Replies View Related

Find Number Of Licenses

Jul 20, 2005

How do you find the number of licenses that are installed on a SQL Server2000?

View 2 Replies View Related

Find First Free Number

Sep 8, 2006

I have a table

Col1 Col2

1 1000

2 1001

4 1003

5 1004

7 1006

I want to find the first free number from first column.

Now It should return 3.

After inserting a row with col1 = 3 it should return 6 and after inserting a row with col1 = 6 it should return 8.

Is it posible ?

View 3 Replies View Related

SP To Find A Gap In A Number Field

Feb 13, 2008

I have a part number field which contains whole numbers. (1 - 5000+) however there are gaps in the numbers e.g. Part Number 3876 maybe missing. The table has new parts inserted from a VB Client. Im trying to write a stored procedure where when a user trys to run an update without entering a part number the first missing part number is found. If they do provide a part number then their number is committed as long as it is unique. I've tried using cursors but am not sure on the best approach. Any suggestions?

Thanks in advance

Alex

View 6 Replies View Related

Find Number Of Decimals

May 14, 2008

Can someone suggest a FAST way to select Currency values where the number has more than X decimal places? There are zillions of rows so looping in code is not the preferred solution.

(a bug forgot to round)

Thanks for your help

View 4 Replies View Related

How To Find The Row Number Transformation

Mar 31, 2006

I need help on how to find the Row Number Transformation,

I have followed this link http://www.sqlis.com/default.aspx?93

but I cannot find the Trash Destination. Could someone please tell me where I can download it?



Thanks

View 3 Replies View Related

Find Max Serial Number From 2 Tables

Feb 11, 2008

I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time  select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.

View 3 Replies View Related

How To Find The Number Of Rows In A Table

May 12, 2006

I try to find the number of rows in a table with this commands:
CountRec = New SqlParameterCountRec.ParameterName = "@countrec"CountRec.SqlDbType = SqlDbType.IntCountRec.Value = 0MyCommand = New Data.SqlClient.SqlCommand()MyCommand.CommandText = "select count(*) as @countrec from Customer;"MyCommand.CommandType = Data.CommandType.TextMyCommand.Connection = MyConnectionMyCommand.Parameters.Add(CountRec)MyCommand.Connection.Open()MyReader = MyCommand.ExecuteReaderiRecordCount = CountRec.Value
This is the result:
Incorrect syntax near '@countrec'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '@countrec'.Source Error:




Line 39:
Line 40: MyCommand.Connection.Open()
Line 41: MyReader = MyCommand.ExecuteReader
Line 42: iRecordCount = CountRec.Value
Line 43:
Source File: E:DevelopWebASPwebAccessTimberSalesUserEntry.aspx.vb    Line: 41
What to do? I need a complete example to see how it works.
Thanks...

View 3 Replies View Related

How To Find Out The Number Of User Connections

Aug 9, 2000

I am using SQL 6.5 and I would like to know (using Isql/w)
the number of users connected to my server at any given time.
Thanks for your help in advance.

View 3 Replies View Related

Find The Smallest Number In Two Columns Was: How Do I Do This ?

Jun 15, 2005

Hi,
I have a sql query which gives me the result set with lots of columns and rows.

a b c allocated unallocated
- ------ 75458702 0484095809
------- 534534 8743857
------- 953459034 90584395

i have to find of which is the smallest number in both allocated and unallocated columns -
here in this case
it would be 534534.
how do i do this ?

Thanks

View 1 Replies View Related

How To Find Current Row Number In MS SQL 2000

Jan 17, 2007

hi
How to find current row number in MS SQL 2000 server DB ?
like in oracle rowid is there to get unique no. of each row.
I want to track the last row in the resultset ..
is any fun for this ??

View 7 Replies View Related

How To Find Maximum Number From Two Tables

Feb 19, 2012

My db contains two table Employee_detail and Student_Detail.Employee_detail has Emp_id and Student_Detail has stud_id.Now my problem is that i want to get one maximum no from these two table.

View 11 Replies View Related

How To Find Age And Group By Student Number Like (IT%)

Aug 4, 2013

I have created a table:

CREATE TABLE [dbo].[Student](
[StudentNumber] [varchar](50) NOT NULL,
[Name] [char](50) NOT NULL,
[Contact] [int] NOT NULL,
[Address] [char](50) NOT NULL,
[DateOfBirth] [datetime] NOT NULL,
[YearEnrolled] [int] NOT NULL,
[Year] [int] NOT NULL;

And insert the following data:

INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1);
INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1);
INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1);
INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1);
INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)

How do i calculate the age and how can i group by the StudentNumber like 'IT%'

View 3 Replies View Related

Query To Find A Missing Number

Jul 13, 2006

Hello,I need to write a query to find out a set of missing number in a givensequence.Eg : a Column in some table has the following dataCol11234568910Here I need to write a query to find out that number 7 is missing in thegiven sequence.One possible solution is by using any loop. But I am looking out if the samecan be achieved using any query.Thanks in advance.Regards,Mahesh

View 4 Replies View Related

How To Find Out Number Of SQL Server Seats Purchased?

Jul 2, 2007

 How to find out number of SQL Server seats purchased?

View 2 Replies View Related

Find Number Of SQL Servers Exists In The Network

Jun 10, 2003

is there any way to find the number of sqlservers exists (count and name of server) in a network using sql statement or stored procedure

View 1 Replies View Related

How To Find 'x' Number Of Days From 'yyyymmdd' Format?

Nov 15, 2004

I need to find out the count of number of records older than 100 days from a table having 'order_date' as yyyymmdd format eg. 20041115. Thanks in advance.

View 9 Replies View Related

To Find The Number Of Occurences Of A Particular Character In A String

Aug 8, 2001

Hi all,

@str varchar(500)

select @str = 'abcd,efgh,i,jklmn,opsqrtuv'

For the above string, which string function can be used to find the number of occurences of a particular character, for example, ','? For this example, the answer should be 4. Is there any built-in function in SQL to do this?

Thanks in advance,
-Praveena

View 1 Replies View Related

Transact SQL :: Find The Number Which Exists In Different Groups

May 25, 2015

I have a table which has 2 columns and the data is like below

API_Number        Group_Name

1234                     Group A
3241                     Group A
1234                     Group B
4567                     Group C
7896                     Group D
3241                     Group E

 I wanted to find the API numbers which are repeating in different groups. In the output I want

 API_Number           Group_Name

1234                       Group A,Group B
3241                       Group A,Group E

View 5 Replies View Related

How To Find The SQL Server License Number On Registry

Feb 19, 2008



I'm trying to find the SQL Server License Number used to install the software on the server. Does the installation program stores the information on the Registry or not? Looking through registry I found the ProductCode but I'm not sure if that is the valid License Number ... It does looks more like the unistall code ...

Thanks for your help

wamregua

View 1 Replies View Related

Find Logic Flaw, Order Number Generator

Mar 15, 2000

This procedure has been returning duplicate numbers. (Tested with scripts that called this proc and put value in a table.)

How can it return duplicates? Does the transaction Begin/Commit not guarantee transactional consistency?


CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS
DECLARE @NewOrderId int,
@nSQLError int,
@nRowCount int
BEGIN TRAN
UPDATE CUSTOMERS
SET ORDER_NUMBER=ORDER_NUMBER + 1
WHERE COMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -999
End
SELECTORDER_NUMBER
FROMCUSTOMERS
WHERECOMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -998
End
COMMIT TRAN

View 1 Replies View Related

SQL Server 2012 :: How To Find Continues Number Ranges

Dec 19, 2013

Consider the following data

create table #sampletbl
(
Category varchar(100),
Numbers varchar(100),
Status varchar(100)
)
insert into #sampletbl values ('A','29710000001','0')

[code]....

if status = 0 then unused status = 1 then used

i need to find 10 continues unused numbers

This is the sample data. In live I'm having plenty of data like that . So quite complex.

note : In real world I need to find the 1000 continues number ranges.

View 8 Replies View Related

Find Average Marks At Various Intervals Of Serial Number

Nov 24, 2013

I have this table of Marks as shown below. All I need is to find the average Marks at various intervals of S.no. That is I need averages at every 3rd S.No. as shown.

S.No. Marks
1 ------ 5
2 ------ 5
3 ------ 6 1st Average Value here (16/3)
4 ------ 5
5 ------ 6
6 ------ 7 2nd Average Value here (18/3)
7 ------ 7
8 ------ 7
9 ------ 8 3rd Average Value here (22/3)
10 ----- 8
11 ----- 9
12 ----- 8 4th Average Value here (26/3)

So basically I need a new table which will have 4 average values for the table above. Of-course the table can be much bigger and the average values can be at any nth value of S.No.

View 12 Replies View Related

Which Is The Most Efficient Query To Find Out The Total Number Of Rows In A Table?

Mar 30, 2006

which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query

View 10 Replies View Related







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