December 1, 2011 at 11:58 am
[font="Courier New"]
I need to filter an ordered result set based on the first occurrence of a unique value in one column. The query problem can be summarized with the following simple example data:
create table RelationshipTable (
Number int,
Type int,
Name varchar(20) )
insert into RelationshipTable
(Number, Type, Name)
values (34, 3, 'Fred'),
(32, 3, 'Dean'),
(18, 11, 'Bob'),
(29, 11, 'Al'),
(15, 7, 'Tony')
Number Type Name
----------- ----------- --------------------
34 3 Fred
32 3 Dean
18 11 Bob
29 11 Al
15 7 Tony
Desired query result: The first occurrence 'Number,Type,Name' for each unique Type
Number Type Name
----------- ----------- --------------------
34 3 Fred
18 11 Bob
15 7 Tony
I would greatly appreciate any suggestions you may have to help me with this query problem.
[/font]
December 1, 2011 at 12:11 pm
You can try this:
;WITH CTE AS
(
SELECT Number,Type,Name,ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Type) As 'Pos' FROM RelationshipTable
)
SELECT Number,Type,Name FROM CTE
WHERE
Pos = 1
Shatrughna
December 1, 2011 at 12:17 pm
If you care which one is "first" you will need to sort by more than just type. Ordering by type alone will not always get the same row for each type first. Order by type, name for example.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2011 at 12:19 pm
And aostanley, excellent job posting your question with ddl, inserts and desired output. Doing that makes it so much easier for people to help. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2011 at 1:56 pm
aostanley,
Here's another way to do it using APPLY. Just another way to look at it.
WITH Rel AS
( SELECT DISTINCT Type
FROM RelationshipTable
)
SELECT R.Type
, X.Number, X.Name
FROM Rel R
CROSS APPLY
(SELECT TOP 1 RT.Number, RT.Name
FROM RelationshipTable RT
WHERE R.Type = RT.Type
ORDER BY RT.Number -- SORT however you like
) AS X
Todd Fifield
December 1, 2011 at 2:47 pm
I would like to thank all of you who replied.
I found the common table expression solution submitted by shatrughna to be very useful.
I want to express my appreciation for your very quick reply.
December 1, 2011 at 2:54 pm
Just remember that without an extra sort column you can't be sure what order they will appear.
;WITH CTE AS
(
SELECT Number,Type,Name,ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Type, Name --now you KNOW which row will be the top 1
) As 'Pos' FROM RelationshipTable
)
SELECT Number,Type,Name FROM CTE
WHERE
Pos = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply