• Status: Solved
• Priority: Medium
• Security: Public
• Views: 255
• Last Modified:

# SQL on Pairs of Records in a Table

Hi!

I have a large table with the following structure and content - here is an example:

Order Number     Order Detail     Code
4711                       1                        100
4711                       1                        200
4712                        4                       100
4713                        2                       100
4713                        2                        200
4714                        6                        100
4714                         6                       200
4715                         3                       100

I have read that it is possible to perform one SQL involving two records but cannot figure out how to do.

I want to exclude the pairs of order number and detail having code 100 and 200 but do have shown all records that have only code 100 and not code 200: in above example 4712 and 4715. Is that possible?
0
Kim Neesgaard
Asked:
• 10
• 4
• 2
• +3
1 Solution

Commented:
you need a sub-query to select all of the records that contain thee 200 value, and then exclude those that fall into that category.

select * from your table where [order num] not in (select [order num] from yourtable where code = 200)
0

Commented:
Or you can use a row number to do it.

``````SELECT *
FROM
(SELECT [Order Number], [Order Detail], [Code],
ROW_NUMBER ( )  OVER ( [ PARTITION BY [Order Number] ORDER BY  [Order Detail], [Code]) As Row_num) SubQ
WHERE SubQ.Row_num = 1
``````
0

Author Commented:
Thank you - I will try your suggestions!
0

Commented:
Another way:

``````SELECT x.OrderNumber, x.OrderDetail, x.Code
FROM
(SELECT OrderNumber, OrderDetail, Code
FROM SomeTable
WHERE Code = 100) x LEFT JOIN
(SELECT OrderNumber, OrderDetail, Code
FROM SomeTable
WHERE Code = 200) y ON x.OrderNumber = y.OrderNumber
WHERE y.OrderNumber IS NULL
``````
0

Senior DBACommented:
If there's only one row per order number for the order numbers to be listed, you can use the query below, which only scans the table once vs. at least two times for other methods:

SELECT
[Order Number],
MAX([Order Detail]) AS [Order Detail]
--,MAX([other column]) AS [other column] --...
FROM dbo.tablename
GROUP BY
[Order Number]
HAVING
MAX(Code) = 100
0

Commented:
select [Order Number], [Order Detail] from table where code = 100
except
select [Order Number], [Order Detail] from table where code = 200
0

Author Commented:
Thank you for all suggestions - I have the following comments:

-> fyed
I have tried your suggestion but get to many records returned. Not only code 100 and 200 are in the column - also many other codes exist.

-> Jim P.
Many other codes than 100 and 200 are in the column so I think the specific codes need to be present in the query?

-> matthewspatrick
The returned number of records is pretty much as expected so this is a very relevant proposal to an SQL. However, I have a question: does your proposal take into account that 'pairs' of orders+order details must be searched for?

-> ScottPletcher
Please see under Jim P.

-> awking00
I get this error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'EXCEPT'.
0

Commented:
My method allows you to just get the top X rows regardless of the Codes on an order.

But if you want to include or exclude for specific codes that would be adding a where clause in the subq.
0

Author Commented:
-> Jim P.
Ok. I have investigated a bit more and the result seems to be completely correct!
0

Commented:
>>-> awking00
I get this error:<<
Don't know why. The syntax for using except can be found at this link -
http://technet.microsoft.com/en-us/library/ms188055.aspx
What was the exact query you submitted?
0

Author Commented:
-> awking00

select Order number, Order detail from table where code = '100'
EXCEPT
select Order number, Order from table where code = '200'
0

Commented:
You can't use a column name with spaces (e.g. order number) unless you enclose it in brackets and make sure the case sensitivity is correct. Also, what is the data type of code?
0

Author Commented:
No - and I did not. The actual SQL has no spaces - I have just converted the real and recognisable fields to reflect the example.
0

Senior DBACommented:
SELECT tn.*
FROM dbo.tablename tn
INNER JOIN (
SELECT
[Order Number]
FROM dbo.tablename
WHERE
CASE IN (100, 200)
GROUP BY
[Order Number]
HAVING
MAX(CASE WHEN Code = 100 THEN 1 ELSE 0 END) = 1 AND
MAX(CASE WHEN Code = 200 THEN 1 ELSE 0 END) = 0
) AS tn2 ON
tn2.[Order Number] = tn.[Order Number]
0

Commented:
Can you then post the actual query you submitted?
0

Author Commented:
-> awking00
Do you have an email I can forward it to?
0

Author Commented:
-> ScottPletcher
I get this error:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IN'.
0

Author Commented:
Thank you all for your contributions!! I have investigated the real data and it seems that I have found the solution in matthewspatrick answer.

But again: thank you very much to your all.
0

Commented:
neesgaard0,

Glad to have helped.  If your question is resolved, please remember to close it by accepting one or more comments as the answer.

Patrick
0

Author Commented:
I have just done so!

BR Kim
0

Author Commented:
-> Patrick Matthews

I 'dare' to put an additional question to you regarding the SQL: when data is extracted from a table, '20.000' is extracted as 20000.000 i.e. 1000 times too high. I have tried to use a ROUND in the SQL but I get an error. Could you suggest something that brings the SQL to extract the right figures?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 10
• 4
• 2
• +3
Tackle projects and never again get stuck behind a technical roadblock.