Find The First Key By Date Field Using Sql And Output Also Have Other Fields

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. Input: Name

Solution 1:

You can use the min function, also assuming payment_date is a date type:

select Name, ID, min(payment_date), Pack from mytable
groupby payment_date,Name, ID, Pack
orderby Name

The downfall about this method is putting all of the fields in the group by.

Solution 2:

If your payment_date is a date data type, you can use not exists() like so:

select*from t
wherenotexists (
  select1from t i
  where i.Name = t.Name
    and i.payment_date < t.payment_date
    )

rextester demo (sql server): http://rextester.com/OKB46268

returns

+------+----+-------------+------+
| Name | Id | PaymentDate | Pack |
+------+----+-------------+------+
| A    | 17 | 2017-01-25  | P    |
| B    | 11 | 2017-01-30  | R    |
| C    | 13 | 2017-01-26  | Q    |
| D    | 23 | 2017-01-29  | Q    |
+------+----+-------------+------+

Solution 3:

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'UNIONALLSELECT'C',13,DATE'31-Jan-2017','Q'UNIONALLSELECT'B',2, DATE'31-Jan-2017','R'UNIONALLSELECT'C',3, DATE'28-Jan-2017','P'UNIONALLSELECT'D',23,DATE'29-Jan-2017','Q'UNIONALLSELECT'B',11,DATE'30-Jan-2017','R'UNIONALLSELECT'A',17,DATE'25-Jan-2017','P'UNIONALLSELECT'C',13,DATE'26-Jan-2017','Q'UNIONALLSELECT'D',17,DATE'2-Feb-2017','R'UNIONALLSELECT'B',23,DATE'3-Feb-2017','P'UNIONALLSELECT'A',45,DATE'4-Feb-2017','Q'UNIONALLSELECT'B',3, DATE'5-Feb-2017','R'
)
-- end of input , start real query here:SELECT*FROM input
LIMIT 1OVER(PARTITIONBY Name ORDERBY payment_date)
;

Happy playing ... Marco the Sane