Sql query to find the results

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hi,

I have data in the below format:-

Col1 	Col2	Col3
Path1	A1	Value1
Path1	A1	Value2
Path1	A1	Value3
Path1	A1	Value4
Path1	A1	Value5
Path1	A2	Value6
Path1	A2	Value7
Path1	A2	Value8
Path1	A2	Value9

I want the output in the below format:-

Col1 	Col2	Col3
Path1	A1	Value1
Path1	A1	Value2
Path1	A2	Value6
Path1	A2	Value7

In short for every path and for every unique Col2 value (A1,A2) we need to pick any 2 values from col3.

What I have tried:

Have tried using row_number using partition by and ranking function but no luck

Posted 8-Dec-19 22:54pm

chints786

Updated 9-Dec-19 3:15am

Maciej Los

v2

Add a Solution

Comments

Herman<T>.Instance 9-Dec-19 5:17am

Show code to see what you did

2 solutions

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)

Richard MacCutchan · Answer 1 · 2019-12-08T23:09:00

Solution 1

See SQL SELECT TOP, LIMIT, ROWNUM[^].

Posted 8-Dec-19 23:09pm

Richard MacCutchan

Comments

chints786 9-Dec-19 5:11am

Not working

Herman<T>.Instance 9-Dec-19 5:17am

Why not?

phil.o 9-Dec-19 5:18am

Please show what you actually tried, instead of a vague description, and you may get some more detailed answers. Use the green Improve question widget which appears on hovering your question for that matter.

Richard MacCutchan 9-Dec-19 5:18am

Then your code must be wrong.

Maciej Los · Answer 2 · 2019-12-09T03:16:00

Try this:

SQL

DECLARE @tmp TABLE(Col1 NVARCHAR(100), Col2 NVARCHAR(100), Col3 NVARCHAR(100))

INSERT INTO @tmp(Col1, Col2, Col3)
VALUES('Path1', 'A1', 'Value1'),
('Path1', 'A1', 'Value2'),
('Path1', 'A1', 'Value3'),
('Path1', 'A1', 'Value4'),
('Path1', 'A1', 'Value5'),
('Path1', 'A2', 'Value6'),
('Path1', 'A2', 'Value7'),
('Path1', 'A2', 'Value8'),
('Path1', 'A2', 'Value9')


SELECT Col1, Col2, Col3
FROM (
	SELECT Col1, Col2, Col3, ROW_NUMBER() OVER(PARTITION BY COl2 ORDER BY Col1, Col2) AS RowNo  
	FROM @tmp) AS T
WHERE T.RowNo IN (1, 2)

Result:

Col1	Col2	Col3
Path1	A1	Value1
Path1	A1	Value2
Path1	A2	Value6
Path1	A2	Value7

Note: there's few other ways to resolve your issue.