How to get rows by repeated name in one table column SQL server

Question

1.00/5 (1 vote)

See more:

hi Friends,

I have the following table.

Id   ShopName  Image 
1    ABC        /images/1.jpg
2     E         /images/2.jpg
3     F         /images/3.jpg
4    ABC @ 1F   /images/4.jpg
5    G          /images/5.jpg
6    E @2F      /images/6.jpg

In this table ABC & ABC @ 1F and E & E@2F have same images.I need unique images but images are storing with different id's.how to get the unique images?
I need the following output

Id   ShopName  Image 
1    ABC        /images/1.jpg
3     F         /images/3.jpg
5    G          /images/5.jpg
6    E @2F      /images/6.jpg

What I have tried:

I tried with group by and distinct keyword.

By using substring to get the unique shopname

Posted 26-May-21 22:35pm

prasanna204

Updated 26-Jul-21 0:51am

OriginalGriff

v2

Add a Solution

Comments

RedDk 27-May-21 11:47am

The images are named according to their uniqueness. Or, better yet, images are named according to their uniqueness.

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-05-26T23:10:00

You do need the GROUP BY to do this, but you first need to decide what the rules are: you are using two incompatible rules to select your data.
Why do you select the first image for "ABC", and the last image for "E @2F"?
SQL has no idea which image you want, so before you even start coding a query, you need to decide what the rules are. Then you can apply them.

Suppose the rules were "select the lowest value ID for duplicates" - then GROUP BY and JOIN will give you results:

SQL

SELECT a.* FROM MyTable a
JOIN (SELECT MIN(Id) AS MinID
      FROM MyTable
      GROUP BY Shopname) b
     ON a.ID = b.MinID

But until you decide the rules, there is no code you can write!

Er Sagar Mahajan Pune · Answer 2 · 2021-07-26T00:51:00

SQL

---Hi, I have created one temp table named "#Shopes" and insert data as above, and the data look like this,

select * from #Shopes


id	ShopName	  Image
1	ABC     	/images/1.jpg
2	E	        /images/2.jpg
3	F	        /images/3.jpg
4	ABC @ 1F	/images/4.jpg
5	G	        /images/5.jpg
6	E @2F	    /images/6.jpg

--Then, I have used the following script, might be the following script will give you your final solution,

select * from #Shopes
where ShopName not in (
select t1.ShopName --t1.* 
from #Shopes t1
inner join #Shopes t2 on t1.ShopName LIKE CONCAT( t2.ShopName, '%') 
and t1.id<> t2.id 
)

--Please reply if it's works for you..
--Thanks ..!!

How to get rows by repeated name in one table column SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to get rows by repeated name in one table column SQL server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us