select row besed on field. - 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 2008 Forums

Transact-SQL (2008)

select row besed on field.

Author

Topic

rezak
Starting Member

2 Posts

Posted - 2010-02-04 : 18:29:15

Hi

I'm sure exactly how to word this but i'm trying to figure out if it's possible to select a row depending on a value in a field. For example if you had the following table

date value
----------------
02/01/2010 p
02/02/2010 f
02/02/2010 p
02/03/2010 f
02/04/2010 f
02/04/2010 p
02/04/2010 p
02/05/2010 p
02/05/2010 p

the result i'm looking for would be

date value
----------------
02/01/2010 p
02/02/2010 f
02/03/2010 f
02/04/2010 f
02/05/2010 p

so basically in the data there can be multiples of one date but value can be only two entries 'p' or 'f'. however the result set needs to filter this down to only one day based on the value. if a day has multiple entries with some being 'p' others as 'f', the 'f' record would be only be showing, such as the case in 02/04/2010. if multiple 'p' for one day shows only one would be in the results (same as 'f'), such as case 02/05/2010

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-04 : 20:51:44

[code]
select *
from
(
select *, row_no = row_number() over(partition by date order by value)
from yourtable
) d
where d.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rezak
Starting Member

2 Posts

Posted - 2010-02-05 : 09:44:44

Thank you good sir! Been stuck with that for awhile.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources