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

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)

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