simple but tricky query - 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

General SQL Server Forums

New to SQL Server Programming

simple but tricky query

Author

Topic

m121212
Starting Member

3 Posts

Posted - 2010-08-19 : 01:12:34

Hi,

I have a simple table with times that looks like this:

hour minute nearest_hour distance_to_nearest_hour
13 03 13 03
13 05 13 05
13 58 14 02
14 04 14 04
14 07 14 07
14 32 15 28
15 02 15 02

I want to do a SELECT to find the single entry for each hour that's closest to the 0 minute of that hour. For this table, the result would be

13 03 13 03
13 58 14 02
15 02 15 02

My intuition is to minimize over the distance field, but I'm not sure how to do that for every hour.

Thanks for any tips!
m121212

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-19 : 01:29:18

if you are using SQL 2005 / 2008, you can


select *
from
(
    select *, row_no = row_number() over (partition by nearest_hour order by distance_to_nearest_hour)
    from   simple_table
) d
where d.row_no = 1

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

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-19 : 02:20:30

Is there a chance that two entries for the same hour should be displayed? If i.e. you have 13:58 and 14:02, do you want to display them both? In that case you should replace row_number() by rank()

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

m121212
Starting Member

3 Posts

Posted - 2010-08-19 : 11:55:39

Sorry, I'm using MS Access 2007. row_number() doesn't seem to work for that.

Also, I only want one entry returned, even if two are the exact same distance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 13:52:35

then you should be posting this in Access forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m121212
Starting Member

3 Posts

Posted - 2010-08-19 : 14:04:41

oh, ok! thanks for your help.
m121212

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources