how to use distinct in sql

Question

0.00/5 (No votes)

See more:

Table:

SQL

image_id	Title	image_path	
1	Test	../UploadedImage/images (4).jpg
2	rose	../UploadedImage/Hydrangeas.jpg	
3	Test	../UploadedImage/Desert.jpg	
4	rose	../UploadedImage/download (1).jpg

How do i get Result like below

SQL

 Title      image_path
Test ../UploadedImage/images (4).jpg
 rose    ../UploadedImage/Hydrangeas.jpg

I wanna select distinct by Title ,But Same title have multilple Images
select Top 1 Records
any body help me?

Posted 7-May-15 3:13am

MohamedEliyas

Updated 7-May-15 3:32am

v2

Add a Solution

Comments

ZurdoDev 7-May-15 9:20am

No one can help you with that little bit of information. What do you need?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2015-05-07T03:45:00

Solution 1

You don't want DISTINCT - you want to use a subquery:

SQL

SELECT Image_id, Title, Image_path FROM
       (SELECT  *, ROW_NUMBER() OVER (PARTITION BY Title ORDER By Image_Id ) AS rn FROM MyTable) x
WHERE rn = 1

Posted 7-May-15 3:45am

OriginalGriff

Comments

MohamedEliyas 11-May-15 8:11am

thank u sir.. this query was very useful sir....
but now i was using subcategory how to add the sub_id in above table sir...

its using a joins query sir.i will tried to the write query.

pls help me sir..........

OriginalGriff 11-May-15 8:17am

You will have to explain better than that! :laugh:
Try showing exactly the input table data, and what output you expect.

MohamedEliyas 11-May-15 8:18am

its mey be using joins query sir.. i dont know

MohamedEliyas 11-May-15 8:27am

table name:tbl_cat
cat_id cat_name cat_img
1 Plants green
2 Plants blue
3 Gallery orange
4 Gallery yellow
5 Plants white
6 Gallery black

another table: tbl_sub
id img_name img cat_id sub_id
1 G1 ../UploadedImage/bike.jpg 1 1
2 G2 ../UploadedImage/Chrysanthemum.jpg 1 1
3 B1 ../UploadedImage/9mz.jpg 1 2
4 B2 ../UploadedImage/p71.jpg 1 2
5 O1 ../UploadedImage/8l9.jpg 2 3
6 O2 ../UploadedImage/2f2.jpg 2 3
7 Y1 ../UploadedImage/ygq.jpg 2 4
8 Y2 ../UploadedImage/2vi.jpg 2 4

my result:

cat_img img_name img

green ../UploadedImage/bike.jpg
../UploadedImage/Chrysanthemum.jpg

blue B1 ../UploadedImage/9mz.jpg
B2 ../UploadedImage/p71.jpg

I wanna select distinct by Title ,But Same title have multilple Images
select Top 1 Records
any body help me?

OriginalGriff 11-May-15 10:24am

What Title? You don't have a Title column to be distinct on!

MohamedEliyas 13-May-15 1:17am

title name cat_img