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

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

how to use distinct in sql

1 solution

Solution 1

Add your solution here

Preview 0

how to use distinct in sql

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us