Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

SAL Syntax

TABLE 1 example data

field1  field2_year
ABC      2012
DFG      2012
HIJ      2012
ABC      2013
DFG      2013
HIJ      2013



        SELECT
            field1
             ,SUM(HL) as HL
             ,SUM(UT) as UL
               ,field2_year
      FROM TABLE1
      GROUP BY field1,field2_year


I have example data in table 1 above.

i need to write a query that will return the following:
ABC      2012
DFG      2012
HIJ      2012
the trick is that i will not know what the value is in field2_year,
so a simple query like select field1,field2_year where field2_year = 2012 will not work.

i tried using the MAX function but it didnt work as i was hoping it would.

Any suggestions?
0
MoreThanDoubled
Asked:
MoreThanDoubled
3 Solutions
 
sdstuberCommented:
S ELECT   field1, SUM(hl) AS hl, SUM(ul) AS ul, field2_year
    FROM (SELECT field1,
                 hl,
                 ul,
                 field2_year,
                 RANK() OVER (PARTITION BY field1 ORDER BY field2_year) r
            FROM table1) x
   WHERE r = 1
GROUP BY field1, field2_year
0
 
Deepak ChauhanSQL Server DBACommented:
Hi use it hope so it will give u expected result

select field1 ,  field2_year
from (select row_number() over (partition by field1 order by field2_year) as row,
field1 ,  field2_year from table1)  a where row =1
0
 
Scott PletcherSenior DBACommented:
SELECT
            field1
             ,SUM(HL) as HL
             ,SUM(UT) as UL
               ,field2_year
      FROM TABLE1
      WHERE
            field2_year = (SELECT MAX(field2_year) FROM table1)  --<<-- limit rows to MAX(year) in table
      GROUP BY field1,field2_year
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now