How to Get Group Value

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello Friends,

I have a two Column in tables (A, B Column).

A	        B
NOVA210601	AJI0001
NOVA210602	AJI0002
NOVA210591	AJI0003
NOVA210592	AJI0003
NOVA210593	AJI0003
NOVA210594	AJI0003
NOVA210595	AJI0004
NOVA210596	AJI0005
NOVA210597	AJI0006
NOVA210598	AJI0006
NOVA210599	AJI0006
NOVA210600	AJI0006
NOVA210570	AJI0006
NOVA210571	AJI0006
NOVA210572	AJI0006
NOVA210573	AJI0007
NOVA210574	AJI0008
NOVA210575	AJI0008
NOVA210576	AJI0008
NOVA210577	AJI0008
NOVA210578	AJI0008
NOVA210579	AJI0009
NOVA210580	AJI0009
NOVA210581	AJI0010
NOVA210582	AJI0011

Based on "B" Column grouping i want the result "A" column as follows

NOVA210601
NOVA210602
NOVA210591
NOVA210595
NOVA210596
NOVA210597
NOVA210573
NOVA210574
NOVA210579
NOVA210581
NOVA210582

How to write the query? pls Guide anyone

Posted 18-Dec-13 21:16pm

Pandiarajan A

Updated 18-Dec-13 21:40pm

Maciej Los

v2

Add a Solution

2 solutions

Solution 1

How about this...

SQL

SELECT A FROM
(SELECT A, B, RANK() over (Partition By B order by A ) as RANK FROM table)
RS Where Rank =1

Posted 18-Dec-13 21:29pm

Ashish_Agrawal

Comments

Maciej Los 19-Dec-13 3:41am

+5!

Pandiarajan A 19-Dec-13 3:53am

Hello Ashish, i got an error See "syntax error (missing operator) in query expression 'RANK() over (Partition By B order by A)"

Ashish_Agrawal 19-Dec-13 4:51am

My query is working fine with your given example. I do not get any syntax error. BTW, which SQL Server version you are using?

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)

creepz03 · Accepted Answer · 2013-12-18T21:43:00

Solution 2

Try something like this..

SQL

select min(ColumnA) from YourTable group by ColumnB

Posted 18-Dec-13 21:43pm

creepz03

Updated 18-Dec-13 21:46pm

v5

Comments

Pandiarajan A 19-Dec-13 4:01am

Super!!!! Thanks a lot

creepz03 19-Dec-13 18:51pm

Glad I could help.. ^_^