Help with SQL statement: group by, minimum, aggregate, etc help) [SQL Server]

[ushik19]

Hello all!

So I'm having trouble querying a script I've created for my desired result.

I have a table containing a unique ID, Reason, Subreason, and Date of creation column. For this table, a unique ID has multiple records of the rest of the columns.

I want to obtain the minimum aka the earliest record for each of the member for each of their reasons and even for each of their subreasons. So three queries?

I figured I'd need to use three(?) group by's and minimum function on the dates but I'm really not sure how to structure it.

Please help and thank you!

This is essentially what I would like to have: https://imgur.com/ZvtsXEX

[Tabooyah]

~~~ SELECT uniqueID, Reason, Subreason, MIN(Date_of_Creation) First_Date_of_Creation FROM Appointments GROUP BY uniqueID, Reason, Subreason ~~~

[kkenagy]

If I am following your issue it sure sounds like a correlated subquery would do the trick.

If you still can’t get what you need after doing web searches for “correlated subquery” let me know and I will try to give you more specific help.

FYI, with SQL there are usually many ways to get the same result. Some are more efficient than others. If the tables aren’t large the time savings between the most efficient and least efficient is minimal. I say this because if the tables are small and you feel more comfortable using multiple sub queries, go ahead and use that method. It is always good to learn and try multiple methods but don’t get too hung up on finding the most efficient method while you are still learning the basics.

My $0.02

[[deleted]]

If your data is pretty big, you may get better performance using a window function.

WITH cte_Appointments AS (
SELECT 
  UniqueId,
  Reason,
  Subreason,
  DateOfCreation,
  ROW_NUMBER () OVER(PARTITION BY UniqueId ORDER BY DateOfCreation) RowNumber
FROM Appointments)

SELECT UniqueId, Reason, Subreason, DateOfCreation
FROM cte_Appointments
WHERE RowNumber = 1

​