Click here to Skip to main content
15,513,571 members
Please Sign up or sign in to vote.
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' 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
Updated 26-Jul-21 1:51am
RedDk 27-May-21 11:47am    
The images are named according to their uniqueness. Or, better yet, images are named according to their uniqueness.

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:
SELECT a.* FROM MyTable a
      FROM MyTable
      GROUP BY Shopname) b
     ON a.ID = b.MinID
But until you decide the rules, there is no code you can write!
Share this answer
prasanna204 27-May-21 5:21am    
Hi originalGriff
Actually I gave Example.
In the table ABC and ABC @ 1F shops have same images.I need to display unique shop images instead of displaying same shop images multiple times.

ABc /images
Richard Deeming 28-May-21 4:52am    
How do you expect SQL to know that /images/1.jpg and /images/4.jpg are the same image? Even if it knew the root path for the relative image path in your table, are you expecting SQL to read and compare all of the bytes from all of the image files to see if they're identical?

And what if they bytes aren't identical? What looks like "the same image" to a human could be a completely different set of bytes.
---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, '%') 

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

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900