Select one from each group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Select one from each group

Author

Topic

fralo
Posting Yak Master

161 Posts

Posted - 2014-07-01 : 15:18:54

Hi,

Let's suppose that I use the below query:

SELECT lname, fname,mname,title,dob,arresttype,warstatus,
ottic,arrestno,offenseno,arr_date
FROM arrest
WHERE arr_Date > '12/31/2012' and arr_date < '2/1/2013' AND arresttype <> 'D' AND arresttype <> 'C'

And that this returns rows in which some of them are duplicates with respect to certain fields. Like such:

JONES TOM CRAIG JR 3/12/1998 A A 0 8 0 ARR10 OFF10 4/12/2014
JONES TOM CRAIG JR 3/12/1998 A A 1 8 0 ARR10 OFF10 4/12/2014
SMITH JON BILL JR 2/12/1982 A A 0 7 1 ARR13 OFF19 2/11/2003

You will notice that, except for one value, the first two rows are duplicates.

I only want to select one of the rows into my output. It doesn't matter which one. My only concern is getting one row to show up for each person (lname,fname,mname) on the arr_date.

Thanks for any help you could provide.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-01 : 16:00:20

[code]
select *
from
(
SELECT
lname, fname,mname,title,dob,arresttype,warstatus, ottic, arrestno,offenseno,arr_date,
row_number() over(partition by lname, fname, mname, arr_date order by ottic) as Row
FROM arrest
WHERE arr_Date > '12/31/2012' and arr_date < '2/1/2013' AND arresttype <> 'D' AND arresttype <> 'C'
) t
where Row = 1
[/code]

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

fralo
Posting Yak Master

161 Posts

Posted - 2014-07-01 : 16:09:14

Thanks a lot Tara. My only question is will this work for any situation? I should have said that the ottic field was just a sample where the data may be different. It could be for other fields as well.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-01 : 16:16:17

Yes it will. If you care which row gets returned, then you would change the ORDER BY. You have to specify an ORDER BY when using PARTITION BY, so I had to select something.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

fralo
Posting Yak Master

161 Posts

Posted - 2014-07-01 : 16:26:30

It seems to work perfectly. Thanks so much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-01 : 16:46:18

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources