getting more than one field with max

getting more than one field with max

Post by Jamie Thingelsta » Sat, 26 Apr 1997 04:00:00



I have a time series of data, I would like to find the maximum
value in the time series along with another field that is in the same
row as that maximum.

if I have 2 columns,

     dte int,
     data numeric(9,5)

I can do a

      SELECT MAX(data) FROM foo

and get the maximum data, but in order to get the dte with it I end up
doing some complicated SQL sybase will do but is slow...

    SELECT data, dte FROM foo WHERE data =
      (SELECT MAX(data) FROM foo)

and this I have to do in a rowcount 1 incase there are multiples at the
max. Is there any better way to do this?

email replies appreciated.

 
 
 

getting more than one field with max

Post by luc van der veur » Sat, 26 Apr 1997 04:00:00


: I have a time series of data, I would like to find the maximum
: value in the time series along with another field that is in the same
: row as that maximum.

: if I have 2 columns,

:      dte int,
:      data numeric(9,5)

: I can do a

:       SELECT MAX(data) FROM foo

: and get the maximum data, but in order to get the dte with it I end up
: doing some complicated SQL sybase will do but is slow...

:     SELECT data, dte FROM foo WHERE data =
:       (SELECT MAX(data) FROM foo)

: and this I have to do in a rowcount 1 incase there are multiples at the
: max. Is there any better way to do this?

Since you already calculated the maximum, you can keep its value
in a variable and use that in the second query :



set rowcount 1

set rowcount 0

Luc.

______________________________________________________________________
Luc Van der Veurst                          ISUG Enhancements Co-Chair
Academic Hospital, VUB                             http://www.isug.com
Laarbeeklaan 101, 1090 Brussels, Belgium
32 - 2 477 69 80                                Next ISUG Conference :


 
 
 

getting more than one field with max

Post by Kevin Sherloc » Sat, 26 Apr 1997 04:00:00



:I have a time series of data, I would like to find the maximum
:value in the time series along with another field that is in the same
:row as that maximum.

:if I have 2 columns,

 :    dte int,
 :    data numeric(9,5)

:I can do a

 :     SELECT MAX(data) FROM foo

:and get the maximum data, but in order to get the dte with it I end up
:doing some complicated SQL sybase will do but is slow...

:   SELECT data, dte FROM foo WHERE data =
:      (SELECT MAX(data) FROM foo)

:and this I have to do in a rowcount 1 incase there are multiples at the
:max. Is there any better way to do this?

Why not use the TSQL extension:

SELECT data,dte
FROM foo
HAVING data = max(data)

--------------------------------
Kevin Sherlock
USWEST Dex
Omaha, NE

--------------------------------

 
 
 

getting more than one field with max

Post by Korrapolu Redd » Sat, 26 Apr 1997 04:00:00



> I have a time series of data, I would like to find the maximum
> value in the time series along with another field that is in the same
> row as that maximum.

> if I have 2 columns,

>      dte int,
>      data numeric(9,5)

> I can do a

>       SELECT MAX(data) FROM foo

> and get the maximum data, but in order to get the dte with it I end up
> doing some complicated SQL sybase will do but is slow...

>     SELECT data, dte FROM foo WHERE data =
>       (SELECT MAX(data) FROM foo)

> and this I have to do in a rowcount 1 incase there are multiples at the
> max. Is there any better way to do this?

> email replies appreciated.

You can do like this.

SET ROWCOUNT 1
SELECT data,dte FROM foo ORDER BY data ASC
Thanks,
Korr Reddy
Bridge Information Systems
St.Louis, MO

 
 
 

getting more than one field with max

Post by Michael C. Harri » Sat, 26 Apr 1997 04:00:00




> : I have a time series of data, I would like to find the maximum
> : value in the time series along with another field that is in the same
> : row as that maximum.

> : if I have 2 columns,

> :      dte int,
> :      data numeric(9,5)

> : I can do a

> :       SELECT MAX(data) FROM foo

> : and get the maximum data, but in order to get the dte with it I end up
> : doing some complicated SQL sybase will do but is slow...

> :     SELECT data, dte FROM foo WHERE data =
> :       (SELECT MAX(data) FROM foo)

> : and this I have to do in a rowcount 1 incase there are multiples at the
> : max. Is there any better way to do this?

> Since you already calculated the maximum, you can keep its value
> in a variable and use that in the second query :



> set rowcount 1

> set rowcount 0

> Luc.

> ______________________________________________________________________
> Luc Van der Veurst                          ISUG Enhancements Co-Chair
> Academic Hospital, VUB                             http://www.isug.com
> Laarbeeklaan 101, 1090 Brussels, Belgium
> 32 - 2 477 69 80                                Next ISUG Conference :


that only give the correct answer if there is a one-to-one
correspondence between the max value of data and dte!
 
 
 

1. Pls HELP - Trouble getting all data selected for max of one field

I have a table that has multiple records for each reference number.  I
am trying to select the max dateUpdated record for each reference
number.  The problem I am having is that my max dateUpdated is different
for each reference number and the other fields within the record may
have unique data so the select statement may pull 2 records for the same
reference number because the whole record is not distinct...
This statement works but only if the data is the same in all records
except referencenum and dateupdated.....
SELECT DISTINCTROW [ReferenceNum], [Description], [Type],
max([DateUpdated]) AS LastUpdated
FROM ProjectTestTable
GROUP BY [ReferenceNum], [Description], [Type]
HAVING (max([DateUpdated]))
WITH OWNERACCESS OPTION;
Someone please HELP.....   All I want to pull is the whole record that
has a the last updated date for each referencenum....  Thanks, Shawnash

--
Posted via http://dbforums.com

2. VB guy new to PICK and needs help...

3. Getting MAX integer value from varchar field

4. ADO record binding from SQL server to String type

5. Getting Max for time plus other fields

6. 17490-FL-MIAMI-ORACLE-EDI-UNIX-Programmer/Analyst

7. field MSG = varchar(8000) - but getting an error: max Len of 128

8. Tampa/St Petersburg, Florida Users Group - Informix and Redbrick

9. Getting the max of a char field

10. Getting around max Text Field Size

11. insert data from one field in one table to another field in another table

12. HowTo get max field value from Access table field

13. way to find max var char field lengths of fields in SQL Server table