这个SQL怎么写？（应该不复杂）

Question

分组情况下求分组数据中某字段小于且最接近一个定值得查询
据个例子
由数据表数据如下：（共三个字段#z1 #z2 #z3）
#z1     #z2    #z3
a       b      2
a       b      1
a       b      7
c       d      3
c       d      5
f       g      4
f       g      2
f       w      1
f       q      4
要求按#z1 #z2 分组后组数据中#z3得值小于且最接近 6 的数据。要求结果如下：
#z1     #z2    #z3
a       b      2
c       d      5
f       g      4
f       w      1
f       q      4
小弟实在是写不出来，还望大哥们帮小弟一把！~

Answer 1

SELECT #Z1,#Z2,MAX(#Z3) AS #Z3
FROM TABLE_NAME
WHERE #Z3<6
GROUP BY #Z1,#Z2

Answer 2

zhang@zhang>select col1, col2, col3 from (
  2  select col1, col2, col3, col4, row_number() over(partition by col1, col2 order by col4) col5 fr
om (
  3  select col1, col2, col3, 6-col3 col4 from test2)
  4  where col4>0
  5  )
  6  where col5=1;COL1                 COL2                       COL3
-------------------- -------------------- ----------
a                    b                             2
c                    d                             5
f                    g                             4
f                    q                             4
f                    w                             1zhang@zhang>select * from test2;COL1                 COL2                       COL3
-------------------- -------------------- ----------
a                    b                             2
a                    b                             1
a                    b                             7
c                    d                             3
c                    d                             5
f                    g                             4
f                    g                             2
f                    w                             1
f                    q                             49 rows selected.zhang@zhang>select col1, col2, col3 from (
  2  select col1, col2, col3, col4, row_number() over(partition by col1, col2 order by col4) col5 fr
om (
  3  select col1, col2, col3, 6-col3 col4 from test2)
  4  where col4>0
  5  )
  6  where col5=1;COL1                 COL2                       COL3
-------------------- -------------------- ----------
a                    b                             2
c                    d                             5
f                    g                             4
f                    q                             4
f                    w                             1

Answer 3

select r. r1,r.r2,6-r.r3
from ( select z1 r1,z2 r2,min(6-z3 ) r3
          from zz
         where z3 <= 6
         group by z1,z2
        order by z1,z2) r;