Order and make unique columns

Question

I have 3 columns which are shop_id, shop_number and insert_date. I want to get the list according to descending insert_dates by making unique shop_id, shop_number.

shop_id    shop_number     INSERT_DATE
4              8          2021-06-29 08:37:03
4              2          2021-06-27 16:37:03
4              8          2021-06-28 16:37:03
6              1          2021-06-29 16:37:03
7              9          2021-06-30 09:37:03
6              1          2021-06-30 11:37:03
7              9          2021-06-27 16:37:03

What I expect

shop_id    shop_number     INSERT_DATE
6              1          2021-06-30 11:37:03
7              9          2021-06-30 09:37:03
4              8          2021-06-29 08:37:03
4              2          2021-06-27 16:37:03

I tried the following but it doesn't yield expected result.

select shop_id, shop_number, INSERT_DATE
from mytablename with(NOLOCK) 
where shop_number is not null
group by shop_id, shop_number
order by INSERT_DATE desc

Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere) — SMor, Jun 30 '21 at 12:26
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jun 30 '21 at 13:20

score 2 · Accepted Answer · answered Jun 30 '21 at 12:19

2

When you use GROUP BY, the query usually has aggregation functions. In your case, I think you want MAX():

select shop_id, shop_number, max(INSERT_DATE)
from mytablename 
where shop_number is not null
group by shop_id, shop_number
order by max(INSERT_DATE) desc;

Note: Do not use the WITH NOLOCK hint unless you understand what it really does. It means: "Yes, you can fetch data that is not 100% accurate" and that is usually not desirable.

answered Jun 30 '21 at 12:19

Gordon Linoff

1,242,037
58
646
786

I didn't want to accept without comprehending the snippet, meaning I wanted to gain an ability that integrates same logic to other situations. – Soner from The Ottoman Empire Jul 02 '21 at 14:59

score 1 · Answer 2 · answered Jun 30 '21 at 12:28

Row_Number could be used to get rid of those type of duplicates.

;WITH CTE
AS
(
   SELECT *, ROW_NUMBER()OVER(PARTITION BY shop_id, shop_number ORDER BY INSERT_DATE DESC) as RN
   FROM mytablename 
)
SELECT shop_id,shop_number,INSERT_DATE
FROM CTE
WHERE RN = 1

Order and make unique columns

2 Answers2