I am trying to select distinct rows of data from a table (in general), but also within the same select statement, I would like to select distinct rows based on multiple criteria using the same table.
-- logic is:
-- select distinct rows from a table
-- but when there are rows that have the same on all these 7 fields:
-- batch_no, batch_date, accountno, locationid, amount, depid, glenkey
-- Then, select the row that has the later whencreated column data.
-- This is query for sample data
select *
into #temp
from
(values
(56555, '2022-04-01', 48570, 111, 445.00, 217, 1877885, '2022-03-01'),
(45698, '2022-03-01', 62550, 110, 344.59, 216, 1910945, '2022-02-01'),
(45698, '2022-03-01', 62550, 110, 344.59, 216, 1910945, '2022-01-01')
)
t1
(batch_no, batch_date, accountno, locationid, amount, deptid, glenkey, whencreated)
-- logic is:
-- select distinct rows
-- but when there are rows that have the same on all these 7 fields:
-- batch_no, batch_date, accountno, locationid, amount, depid, glenkey
-- then, select the row that has the later whencreated column data.
select distinct batch_no, batch_date, accountno, locationid, amount, deptid, glenkey,
whencreated from #temp
-- Expected outcome (manually created to be used for you):
select *
INTO #temp1
from
(values
(56555, '2022-04-01', 48570, 111, 445.00, 217, 1877885, '2022-03-01'),
(45698, '2022-03-01', 62550, 110, 344.59, 216, 1910945, '2022-02-01')
)
t1
(batch_no, batch_date, accountno, locationid, amount, deptid, glenkey, whencreated)
Expected output:
Do I need to use subquery? How do I write in T-SQL?