Get corresponding category for sql groupby min and max function SQL

user_13370214 0
Viewed 24

I have a table t1 with the columns : TRANSACTION_ID,USER_ID,MERCHANT_NAME,TRANSACTION_DATE,AMOUNT

a) Write a query to find the first merchant a user transacts on

b) Write a query to find the last merchant a user transacts on

I tried the following query which should work for a)

select USER_ID,MERCHANT_NAME,Min(Bill_Date) from t1
Group by customer_id
mysql-workbench group-by sql
1 Answers

You can use Limit with order by clause

For first query: order by Bill_Date will sort the rows in ascending order of Bill_Date then Limit 1 will return the first row.

select USER_ID,MERCHANT_NAME,Bill_Date from t1
Group by customer_id
order by Bill_Date 
Limit 1

For second query: order by Bill_Date desc will sort the rows in descending order of Bill_Date then Limit 1 will return the first row in the reverse order of first query.

select USER_ID,MERCHANT_NAME,Bill_Date from t1
Group by customer_id
order by Bill_Date desc
Limit 1
user_86516010
Related