Comparing two SELECT TOP 2 queries

Question

1.00/5 (1 vote)

See more:

I want to know the items where the first 2 "code" are identical with the first 2 "code" of other rows. (should be flexible, can also be TOP 5 OR TOP 25)

e.g. somthing like

SQL

SELECT itemNo FROM Table WHERE
(SELECT TOP 2 code FROM Table WHERE item=1 ORDER BY code)
=
(SELECT TOP 2 code FROM Table WHERE item<>1 ORDER BY code)

e.g.

using itemNo=1:
{aaa,bbb) = itemNo2 {aaa,bbb} = itemNo3 {bbb, xxx}

Table:

itemNo;code
     1;aaa
     1;bbb
     1;ccc
     1;ddd
     2;aaa
     2;bbb
     3;bbb
     3;xxx
     3;yyy

I hope someone will understand the problem... :-)

Andi

Posted 12-May-14 11:09am

Sni.DelWoods

Updated 12-May-14 12:19pm

Nelek

v4

Add a Solution

Comments

j snooze 12-May-14 17:41pm

Is this SQL server or access? If its SQL Server and you want the top X records you could try something like this.

Select tb1.Row,*
From (select ROW_NUMBER() over (Order by userid desc) as row,*
from Table) as item1
inner join (select ROW_NUMBER() over (Order by userid desc) as row,*
from Table) as otheritem
on item1.row = otheritem.row
Where item1.row < @TopX

Sni.DelWoods 12-May-14 17:43pm

thx. actually it's Access.

j snooze 12-May-14 17:53pm

I think the DCount() function might provide simiar functionality. Don't know how efficient it is though.

Maciej Los 12-May-14 18:01pm

Sorry, but not clear...

Sni.DelWoods 13-May-14 3:20am

Let me extend my problem:
I'm building a keyword index of serveral items (fields: itemNo, keyword, code). Code is a calculated (phonetical) field to mark same keywords (e.g. thomaz [268]= thomas [268]).

The number of keyword per item is different. To find similar items I want to compare the first 2 (or 5 or 10) keywords of the first item with the first 2 (or 5 or 10) items of the second item. The keywords are sorted.

So if item 1 has the TOP 2 keywords aaa,bbb all other items are simlilar if they also have the TOP 2 keywords aaa, bbb. Items which have the TOP 2 keywords bbb,ccc are not similar and should not be returned by the select query.

This is meant by
(SELECT TOP 2 code FROM Table WHERE item=1 ORDER BY code) = (SELECT TOP 2 code FROM Table WHERE item<>1 ORDER BY code)

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)

Maciej Los · Answer 1 · 2014-05-12T12:07:00

Please, read my comment to the question, because i'm not sure i understand you well ;(

[EDIT]

SQL

DECLARE @tmp TABLE (article VARCHAR(30), keywrd VARCHAR(30))

INSERT INTO @tmp (article, keywrd)
VALUES('artNo 1', 'keyword1'),
('artNo 1', 'keyword2'),
('artNo 1', 'keyword3'),
('artNo 1', 'keyword4'),
('artNo 1', 'keyword5'),
('artNo 2', 'keyword1'),
('artNo 2', 'keyword2'),
('artNo 2', 'keyword5'),
('artNo 2', 'keyword6'),
('artNo 3', 'keyword1'),
('artNo 3', 'keyword2'),
('artNo 3', 'keyword3'),
('artNo 4', 'keyword3')


SELECT DISTINCT article, STUFF((SELECT TOP(3) ','  + keywrd
                    FROM @tmp AS t2
                    WHERE t2.article = t1.article
                    FOR XML PATH('')),1,1,'') AS Keywrds
FROM @tmp AS t1

SELECT article, [1], [2], [3]
FROM (
    SELECT article, keywrd, ROW_NUMBER() OVER(PARTITION BY article ORDER BY keywrd) AS KeyId
    FROM @tmp
) AS DT
PIVOT(MAX(keywrd) FOR KeyId IN([1], [2], [3])) AS PT
ORDER BY [1], [2], [3]

1. query produces result:

artNo 1	keyword1,keyword2,keyword3
artNo 2	keyword1,keyword2,keyword5
artNo 3	keyword1,keyword2,keyword3
artNo 4	keyword3

2. query produces result:

artNo 1	keyword1	keyword2	keyword3
artNo 3	keyword1	keyword2	keyword3
artNo 2	keyword1	keyword2	keyword5
artNo 4	keyword3	NULL	NULL

Still i'm not sure it meets yous requirements. Please, let me know is that what you want to achieve.
[/EDIT]