1

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
Number1Rock
  • 53
  • 4

2 Answers2

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
SOS
  • 6,430
  • 2
  • 11
  • 29
1

@SOS's answer can be simplified in Teradata using QUALIFY to filter the results of a Windowed Aggregate:

SELECT *
FROM store_control
QUALIFY 
   ROW_NUMBER()
   OVER(PARTITION BY Location, Aisle 
        ORDER BY TStamp DESC, Time DESC) = 1
dnoeth
  • 59,503
  • 4
  • 39
  • 56