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
ADVERTISEMENT
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
View Related
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
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
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
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
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
View Related
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
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
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
Jun 23, 2015
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
View 4 Replies
View Related
Aug 20, 2015
The select command below will output one patient’s information in 1 row:
Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,
code]...
View 6 Replies
View Related
Jan 21, 2004
How do I get the row number in a SELECT
Select ???? as Row, Name, Stars From Member
Row Name Stars
---------------------------------------
1 Enigma ****
2 BreittKaiser *****
3 BlindMan *****
View 13 Replies
View Related
Oct 18, 2013
I have a problem where I essentially want to find all the B_ID numbers that do not have any ones or twos next to them in the whole table (see table below).
I come up with this query, however it is wrong, because certain B_ID do not have a 1 or 2 on some lines, but then they do on others :(.
SELECT B_ID
FROM NUMBER
WHERE NUM != 1
AND NUM != 2;
SELECT * FROM NUMBER;
+------+------+------+
| A_ID | B_ID | NUM |
+------+------+------+
| 101 | 101 | 1 |
| 101 | 102 | 2 |
| 101 | 201 | 3 |
| 101 | 301 | 4 |
| 102 | 201 | 2 |
| 103 | 102 | 3 |
| 201 | 101 | 1 |
| 301 | 301 | 2 |
| 401 | 102 | 7 |
| 501 | 102 | 1 |
| 501 | 301 | 3 |
+------+------+------+
11 rows in set (0.00 sec)
View 6 Replies
View Related
Jun 1, 2006
Hello all, I am using SQL 2000 SP4.
I have a table with a studentID column. Is there any way to select a single
row of data from that column by referencing the row number?
ex. select studentID from Table where row = 2
The above example obviously does not work, but is there a way to do
something similar? I want to go down the table sequentially and knowing any of the data in the other columns does not help me with that at all.
Any help is appreciated, thanks
View 1 Replies
View Related
Jan 2, 2004
Hi,
I have a fairly basic query I want to make but I'm struggling on figuring out how to do it. Let's say I have some fields (e.g. Value1, Value2, Value3, etc). I simply want to do a Select statement that returns the highest value among those fields for every row in my db.
At first I thought of the built in Max function until I realized that is for within a column only.
Any help would be appreciated, thank you.
View 4 Replies
View Related
Jun 23, 2004
I swear I have done this before and can't remember how.
I want to select columns in a SELECT statement by their number instead of name.
I have looked everywhere for the syntax.
Also, is there a function to 'count' the number of columns in a table?
Thanks.
View 2 Replies
View Related
Dec 7, 2004
I want to select with column order without field name .
Is it possible ?
SQL2000 db
...
I want to select a field by its order in table, but without naming it to have a constant name of it, like :
select (column01) as L1 from myTable
View 6 Replies
View Related
Feb 25, 2008
Hi all!I am looking for a way to select only numbers OR only the first word from a field.EG:1 cartoon => 12 cartoons => 2200 cartoons => 2007 Allo => 71 Poil 2 Pirates 3 Bizounes => 125Kilos2 => 25Where the values after the => is what I want as a result.Anybody knows how to do that ?
View 7 Replies
View Related
Dec 26, 2007
I have a price field that when I query it, it returns the price as"5.0000". What function can I use to return the price as "5.00"?THANKS
View 4 Replies
View Related
Jun 14, 2004
A table has a column of int type. I need to select a fixed number of rows for each value. For example, if data in that column (c) are 5, 6, 7, and the number I want to select is 2, then I need 2 rows from c=5, 2 from c=6, and 2 from c=7. How to write that query? Any idea?
Thanks.
View 6 Replies
View Related
Apr 6, 2005
Hi
I'm trying to convert and format integer values in a SQL Server select statement to a string representation of the number formated with ,'s (1000000 becomes 1,000,000 for example).
I've been looking at CAST and CONVERT and think the answers there somewhere. I just don'tseem to be able to work it out.
Anyone out there able to help me please?
Thanks,Keith.
View 4 Replies
View Related
Aug 25, 2005
I am working on a multi-page datagrid that pulls data from a database. The issue I am running into is the SQL select query. What I have is a table to 55 items. What I need to do is grab the first 35 of those items and bind the resulting DataReader to the grid, print the page, then grab the remaining 20 items, bind to the grid and print the page.
I can use "SELECT TOP 35 FROM Table" to get the first 35 items, but I don't know how to get the remaining 20 items. Is there a way to say something like "SELECT TOP 20 FROM Table" but specify only the rows that begin after row 35?
I tried doing this with an ArrayList but couldn't bind it to the DataGrid.
Any suggestions?
View 10 Replies
View Related
Apr 4, 2001
Hello, I have a field of type the Var, the stored data makes about 700 characters.
During a select through the query analyser, it tronque in about 200 or 300 characters.
How to explain it?
thank in advance
Pascal
View 2 Replies
View Related
Jul 30, 2002
Here is the example
state
-----
NY
NJ
CA
NY
NC
NJ
CA
IL
CA
Can we generate a result like this with select statement
state No
----- ---
CA 1
CA 2
CA 3
NJ 1
NJ 2
NY 1
NY 2
IL 1
NC 1
View 1 Replies
View Related
Aug 2, 2004
hey all,
I am writing a sproc:
select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
@dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
@dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
@strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.MaxNoStudents > sal.CurrNoStudents
I want to check if the select returned an empty set or not. I cannot use @@rowcount because i am assigning the values to the local vars. I tried
if @strCourseNameRegFor is null
begin
set @err = 'No courses';
end
but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?
View 3 Replies
View Related
Feb 21, 2014
I am assigning temporary Ids in my table, and right now I am assigning new ones via find the MAX(Temp_Id), stripping out the number and adding one.
Since these are temporary I would like to be able to reuse older ones when they are no longer in the table.
For example I have:
ID0001
ID0002
ID0003
ID0006
My code right now will add ID0007. I would like to re-use ID0004 and ID0005 since they're no longer in use. How do I go about do this?
View 3 Replies
View Related
Mar 8, 2006
I'm trying to select records that occur more than once. I'm trying to base this on the email column. So basically I want the query to look something like this:
select * from table where emailaddress count > 1
Can someone provide me with the correct syntax? :)
View 2 Replies
View Related
Mar 19, 2007
Dear Friends!
i have one table namely details
empid type qty
emp1 bucket 5
emp2 bucket 5
emp4 Book 5
emp5 Lux 5
.. .. ..
.. .. ..
nenp n n
Every end of the day i need select total no of type and qty.i have milions of records in the table
ex bucket 100
book 300
How it is possible
Regards
Umapathy
View 2 Replies
View Related
May 29, 2007
In other SQL programs I have seen the use of Limit to tell it how many rows to return.
Is there an equivalent in MS-SQL that will let me do a quick Select clause and tell it how many rows to return.
In other words if I just wanted to see the first 10 rows what would I add to Select * from tableA
Thanks!
View 2 Replies
View Related
Sep 19, 2006
I have a table with entries tied to a membership database. The problemis that I want to select a limit of sixteen entries per member, perday, where some members have 16+ entries per day.I have this so far ( which I've simplified for this post)SELECT dbo.members.firstname, dbo.members.lastname,dbo.entries.gameDayFROM dbo.members INNER JOINdbo.entries ON dbo.members.memberID =dbo.entries.memberIDIf it's day 5, each member should have 80 total.How can I change this to select only 16 entries for each member, makingsure it's 16 per day based on dbo.entries.gameDay?Thanks for your help.
View 2 Replies
View Related
Jul 20, 2005
Dear GroupI wonder whether you can give me a syntax example for a SQL Statement.Lets assume I've a table containing three columns ContactID (Primary Key),Firstname and Lastname.I would like to write a stored procedure which returns me the first tenrecords and increments an outside variable each time it runs.E.g If I run it the first time I pass the variable as 0 and it will returnme the first ten records and returns the variable value 1.When run a second time, I will pass the variable as 1 and it will return merecords 11-20 and sets the variable to 2 and so on...The difficult thing is how to tell to return me records 11-20. I can't usethe ContactID as someone might have deleted a row and e.g. ContactID 18 ismissing. In this case I only would get 9 rows returned. It always should beten.Thanks very much for your time and efforts!Kind Regards,Martin"There are 10 types of people in this world: Those that understand binaryarithmetic, and those that don't."
View 3 Replies
View Related
Sep 12, 2006
I have 1 table named BookMe,
with fields
$Date, $Div, $Name
I want to select the top N number of Div's (ie highest Div value) per distinct Date.
i know how to get the distinct date like this:
SELECT DISTINCT $Date FROM BookMe ORDER BY $Date
How do I combine a TOP keyword to get the TOP n rows per distinct date???
Thx!
View 5 Replies
View Related
