Link to home
Get AccessLog in
Avatar of asenna25
asenna25

asked on

How to Get the highest value of a recordset?

I have a table which looks like below:
id-----year-----month-----value
1-----2007--------9--------123
1-----2007--------8--------144
1-----2007--------7--------345
1-----2006-------12-------1448
3-----2007--------6--------123
3-----2007--------2--------144
3-----2006-------11--------345
3-----2006-------10-------1448

i need to get the highest year-month value of each id, e.g.
id-----year-----month-----value
1-----2007--------9--------123
3-----2007--------6--------123

how can i do it in sql?

thanks.
Avatar of Joe Wu
Joe Wu
Flag of Australia image

SELECT distinct id, max(year) FROM your_table_name
Avatar of asenna25
asenna25

ASKER

it doesn't work in this situation.  the year may not be the Max, e.g. for id 1, max is 2007, for id 3 max is 2007, but it could have an id 6 and its max is 2005.  This applied to the month column as well. and distinct will not return the single highest record since the value of each row may different.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Reason number 642 why dates should be stored in native data types.

O.K.  The trick here is to combine the fields, do the relational calculus, and then split them apart to get the columns back in the result.

Assuming the year and month are varchar, try something like....

SELECT ID,
       SUBSTRING(MAX(MYFIELD),1,4) AS YEAR,
       SUBSTRING(MAX(MYFIELD),5,2) AS MONTH,
       CONVERT(INT,RIGHT(MAX(MYFIELD),10)) AS VALUE
  FROM (SELECT ID, YEAR + RIGHT('0'+MONTH,2) +
               RIGHT(REPLICATE(' ',10)+CONVERT(VARCHAR,VALUE),10) AS MYFIELD
          FROM YourTable) TT
 GROUP BY ID

If the year and/or the month are integers, you have to stuff a convert on them in the derived table query.  You have the option of converting them back or not in the outer query.

Another approach is to create a view that has a real date field in it.

CREATE VIEW TT AS
   SELECT ID,
                CONVERT(DATE,YEAR+'/'+RIGHT('0'+MONTH,2)+'/01') AS DT_FLD,
                VALUE
       FROM YourTable

Then, you can do a little GROUP BY / HAVING trick...

SELECT ID, DT_FLD, VALUE
  FROM  (SELECT ID,
                           CONVERT(DATE,YEAR+'/'+RIGHT('0'+MONTH,2)+'/01') AS DT_FLD,
                            VALUE
                 FROM YourTable) TT
GROUP BY ID
HAVING DT_FLD = MAX(DT_FLD)

If you want to break the DT_FLD back out into Year and Month, you can do that with the DATEPART function.

Regards,
Bill
Sorry.  I got ahead of myself and submitted things before I got them cleaned up.

Forget the part about creating a view.  The second part of the second solution uses a derived table instead of a view.

Regards,
Bill
>Reason number 642 why dates should be stored in native data types.
I fully agree :)
Try this:

Select t1.ID, t1.year, t1.month, t1.value
from [insert table name here] t1
where t1.value = (select max(t2.value) from [insert table name here] t2 where t2.id = t1.id)


This ensures you have the max value for each ID regardless of the month/year (those values just get pulled in once you've found the max).

Hope that helps,

Joshua

FYI, while angelIII's solution will return the correct results, it will most likely be slower than the alternatives.  It has to perform two GROUP BY queries (implied in the SQL) then a pair of JOINs between the base table and the two work table results.

If you have a bunch of data, you should probably turn SHOWPLAN ON and try two or three versions of the query to find the one with the best query plan.

Regards,
Bill
assuming you have an (single) index with the columns ID, YEAR and MONTH (in that order) in it, my syntax should also be quite efficient.
the alternative to add up the year and month values will be unable to use an index, apart on the id column alone.
it will hence depend a lot on the number of rows you will have per id and in total to process.

index, with SHOWPLAN ON you can see a bit cleared.