使用sql查找按日期排序的第一个关键字，并输出其他字段 [英] Find the first key by date field using sql and output also have other fields

问题描述

我想根据最早的日期查询每个名字的首次出现.输出应具有完整的行.请帮我用sql编写查询.

I want to query the first occurrence of every name according to the earliest date. The output should have the complete row. Please help me to write the query in sql.

输入:

  Name |    ID |    payment_date |  Pack
 ------+-------+-----------------+-------
    A  |    11 |    31-Jan       |  P
    C  |    13 |    31-Jan       |  Q
    B  |    2  |    31-Jan       |  R
    C  |    3  |    28-Jan       |  P
    D  |    23 |    29-Jan       |  Q
    B  |    11 |    30-Jan       |  R
    A  |    17 |    25-Jan       |  P
    C  |    13 |    26-Jan       |  Q
    D  |    17 |    2-Feb        |  R
    B  |    23 |    3-Feb        |  P
    A  |    45 |    4-Feb        |  Q
    B  |    3  |    5-Feb        |  R

输出:

  Name |    ID | payment_date | Pack
  -----+-------+--------------+-----
    A  |    17 | 25-Jan       | P
    B  |    11 | 30-Jan       | R
    C  |    13 | 26-Jan       | Q
    D  |    23 | 29-Jan       | Q

推荐答案

您还可以使用Vertica的增强型LIMIT子句:

You can also use Vertica's enhanced LIMIT clause:

WITH
-- input, don't use in real query
input(Name,ID,payment_date,Pack) AS (
          SELECT 'A',11,DATE '31-Jan-2017','P'
UNION ALL SELECT 'C',13,DATE '31-Jan-2017','Q'
UNION ALL SELECT 'B',2, DATE '31-Jan-2017','R'
UNION ALL SELECT 'C',3, DATE '28-Jan-2017','P'
UNION ALL SELECT 'D',23,DATE '29-Jan-2017','Q'
UNION ALL SELECT 'B',11,DATE '30-Jan-2017','R'
UNION ALL SELECT 'A',17,DATE '25-Jan-2017','P'
UNION ALL SELECT 'C',13,DATE '26-Jan-2017','Q'
UNION ALL SELECT 'D',17,DATE  '2-Feb-2017','R'
UNION ALL SELECT 'B',23,DATE  '3-Feb-2017','P'
UNION ALL SELECT 'A',45,DATE  '4-Feb-2017','Q'
UNION ALL SELECT 'B',3, DATE  '5-Feb-2017','R'
)
-- end of input , start real query here:
SELECT * FROM input
LIMIT 1 OVER(PARTITION BY Name ORDER BY payment_date)
;

玩的开心... Marco the Sane

Happy playing ... Marco the Sane

这篇关于使用sql查找按日期排序的第一个关键字，并输出其他字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！