question

Gogolo avatar image
Gogolo asked

Simple select

Dear all, how to write a simple query "SELECT qField, sFiels FROM dbo.#tbl" where values on qField are same and values from sField are different.
sqlquery
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
can you provide some sample data that is in the #tbl table please? Its very difficult to imagine what you are trying to achieve.
2 Likes 2 ·
WilliamD avatar image
WilliamD answered
If I understand correctly, you are wanting to find cases where records in the table #tbl are the same, except for sFiels. The easiest way is a self join: DECLARE @TestTable AS TABLE (QField char(10), sFiels char(10) ) INSERT INTO @TestTable (QField, sFiels) SELECT 'a','a' -- This will match UNION ALL SELECT 'a','b' -- To this UNION ALL SELECT 'b','b' SELECT TT.QField, TT.sFiels, TT2.QField, TT2.sFiels FROM @TestTable TT INNER JOIN @TestTable TT2 ON TT.QField = TT2.QField AND TT.sFiels TT2.sFiels This will return all matching records per row - with my example, you get both results back showing the relationship in both directions. EDIT: **Beware that this can cause some serious performance problems if the table has a lot of entries!**
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
wow, the force is strong with you today. I figured sFields was a typo though... mebbe sField
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
Could well be a typo, just going with the info provided :)
0 Likes 0 ·
Gogolo avatar image Gogolo commented ·
Thanks a lot, Just done the same way and works perfect. Thanks a milion.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
You probably need to specify your requirements a little bit more, but I will try to guess what you want. DECLARE @tble TABLE ( qFields INT, sFiels INT ) INSERT INTO @tble ([qFields], [sFiels]) VALUES (0, 0),(0,1),(1,0),(1,0),(2,1),(2,2) ;WITH cte AS ( SELECT [qFields], [sFiels], count([@tble].[qFields]) OVER (PARTITION BY [@tble].[qFields]) AS qFieldCnt, count([@tble].[sFiels]) OVER (PARTITION BY [@tble].[qFields],[@tble].[sFiels]) AS sFieldCnt FROM @tble ) SELECT * FROM [cte] WHERE [cte].[qFieldCnt]>1 AND [cte].[sFieldCnt]=1 This will give you a list of all records where qFields are equal and the sFields are not.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
+1 for the speedier results. This is the winner if the OP only wants to know what has duplicates and not see the duplicates too.
0 Likes 0 ·
Leo avatar image
Leo answered
SELECT qField, sFiels FROM dbo.#tbl GROUP BY qField I hope it will give you (group with qField) with different sFiles Value.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
This will not work because sFiels is not part of the aggregation.
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.