December 29, 2010 at 9:29 am
Hi,
I have this table:
COD; NAME; TYPE; STATUS
1 ; Pedro ; 1 ; A
2 ; Pedro ; 2 ; C
3 ; Pedro ; 2 ; A
4 ; Pedro ; 2 ; A
5 ; Marta ; 2 ; C
6 ; Marta ; 2 ; A
7 ; Marta ; 2 ; C
8 ; Paul ; 2 ; A
9 ; Ana ; 2; C
i wanto to make a query that retrive all the rows in this table wich have the same name and are from type 2 and have status equal to A.
The results, considering the data records above, would be:
3 ; Pedro ; 2 ; A
4 ; Pedro ; 2 ; A
Can someone help me with this query? how to do it? thank you
December 29, 2010 at 10:07 am
This sounds like a job for the row_number() function with the optional partition by clause. This article[/url] might help you out with understanding it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 29, 2010 at 10:12 am
This one is quick and dirty, but it works for the given requirements:
[font="Courier New"]CREATE TABLE MyTable (
[COD] INT,
[Name] VARCHAR(10),
[Type] INT,
[Status] CHAR
)
INSERT INTO MyTable
VALUES ( 1, 'Pedro', 1, 'A' ),
( 2, 'Pedro', 2, 'C' ),
( 3, 'Pedro', 2, 'A' ),
( 4, 'Pedro', 2, 'A' ),
( 5, 'Marta', 2, 'C' ),
( 6, 'Marta', 2, 'A' ),
( 7, 'Marta', 2, 'C' ),
( 8, 'Paul', 2, 'A' ),
( 9, 'Ana', 2, 'C' );
SELECT * FROM MyTable;
SELECT * FROM MyTable
WHERE [Name] IN
( SELECT t.[Name]
FROM ( SELECT [Name], [Type], [Status]
FROM MyTable
WHERE [Type] = 2 AND [Status] = 'A'
GROUP BY [Name], [Type], [Status]
HAVING COUNT(*) > 1
) t
)
AND [Type] = 2 AND [Status] = 'A'
DROP TABLE MyTable;[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply