How to pull max timestamp for distinct sets and subsets

Question

EDIT: DBMS = Haddoop, Using Teradata SQL Asstistant

This is the original table. There are 20 location values (c1). Each Location has a set of aisles (c2). I want to get all the set of records from this table for Distinct locations and their set of distinct aisles based on max(tstamp).

Location	Aisle	Tstamp	qty	time
12	420	4/16/2021 12:22:01 PM	999	999
23	220	4/16/2021 11:22:01 PM	8888	222
31	310	4/16/2021 10:22:01 PM	666	333
12	420	4/16/2021 12:22:01 AM	666	444
31	120	4/16/2021 3:22:01 PM	666	555
22	210	4/16/2021 01:22:01 PM	666	666

I used this SELECT*FROM store_control WHERE store_control.tstamp IN (SELECT MAX(tstamp) FROM store_control AS sql2)

RESULT:

Location	Aisle	Tstamp	qty	time
23	220	4/16/2021 11:22:01 PM	8888	222

What I want is this:

Location	Aisle	Tstamp	qty	time
12	420	4/16/2021 12:22:01 PM	999	999
22	210	4/16/2021 01:22:01 PM	666	666
23	220	4/16/2021 11:22:01 PM	8888	222
31	310	4/16/2021 10:22:01 PM	666	333
31	120	4/16/2021 3:22:01 PM	666	555

Answer 1

You didn't mention which DBMS you're using, but most databases support Window Functions.

For example, with SQL Server you can assign a ROW_NUMBER() by Location and Aisle group, sorting by the latest TStamp and Time first. Then grab the record with row number = 1:

Note: Since both records for Location = 12 have the same TStamp, the query uses Time as a tie breaker

See also db<>fiddle

;WITH cte AS
(
   SELECT *
          , ROW_NUMBER() OVER(PARTITION BY Location, Aisle  ORDER BY Location, TStamp DESC, Time DESC) AS RowNum
   FROM   store_control
)
SELECT * 
FROM   cte
WHERE  RowNum = 1

Results:

Location	Aisle	TStamp	Qty	Time	RowNum
12	420	2021-04-16 12:22:01.000	999	999	1
22	210	2021-04-16 13:22:01.000	666	666	1
23	220	2021-04-16 23:22:01.000	8888	222	1
31	120	2021-04-16 15:22:01.000	666	555	1
31	310	2021-04-16 22:22:01.000	666	333	1