how to use partition in sql

Question

0.00/5 (No votes)

See more:

My Table:

SQL

id    img_name	   img	                          cat_id	sub_id
1	blue	 ../UploadedImage/download (1).jpg 1	       2
2	rose	 ../UploadedImage/images (3).jpg   1	       1
5	kola	 ../UploadedImage/6pl.jpg	   2	       3
6	rat	 ../UploadedImage/Hydrangeas.jpg   2	       4
7	animal	 ../UploadedImage/0z8.jpg	   1	       5
9	fdd	 ../UploadedImage/images (2).jpg   1	       1
10	guys	 ../UploadedImage/Desert.jpg	   2	       3
11	ddfdf	 ../UploadedImage/download.jpg	   1	       1
12	ddfdfdfd ../UploadedImage/images (1).jpg   1	       1

How do i get Result like below

SQL

  img_name                                         cat_id           sub_id
    blue    ../UploadedImage/download (1).jpg   1   2
rose    ../UploadedImage/images (3).jpg 1   1       1
ddfdf   ../UploadedImage/download.jpg   1   1       1
ddfdfdfd    ../UploadedImage/images (1).jpg 1   1

I wanna select distinct by sub_id,But Same sub_id have multilple Images
select Top 1 Records....

i am using subcategory table
any body help me?

Posted 10-May-15 21:42pm

MohamedEliyas

Updated 11-May-15 2:26am

King Fisher

v9

Add a Solution

Comments

Tomas Takac 11-May-15 3:58am

I don't see any two image with the same title. Your results are just filtered version of your inputs. Use where-clause and you are done.

MohamedEliyas 11-May-15 8:04am

am using subcategory table.i was tired. pls help me

MohamedEliyas 11-May-15 8:06am

SELECT image_id,image_path, image_name FROM
(SELECT *, ROW_NUMBER() OVER (PARTITION BY image_name ORDER By image_id ) AS rn FROM tbl_gallery) x
WHERE rn = 1

this query was perfectly working
but
how to add the sub_id in tbl_subimage i dont know sir

1 solution

Add a Solution

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

King Fisher · Answer 1 · 2015-05-11T02:30:00

Solution 1

Solution from your last thread:
how to use distinct in sql[^]

SQL

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

Posted 11-May-15 2:30am

King Fisher

how to use partition in sql

1 solution

Solution 1

Add your solution here

Preview 0

how to use partition in sql

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us