SELECT GROUP BY help - 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 GROUP BY help

Author

Topic

dalibor
Starting Member

21 Posts

Posted - 2009-09-24 : 10:25:44

Hi,

I have table azimuthal coordinates of Venus (azimuth and high above horizon):
SELECT
,[Year]
,[Month]
,[Day]
,[Hour]
,[Min]
,[A] -- azimuth, float
,[h] -- high above horizon, float
,[row_id] -- minute of the day, day has 1440 minutes. Range minutes of the Day 0 - 1440. Integer
FROM [Astro].[dbo].[Venus_horizontal_ext]

Table has coordinates each minute, each day whole year.
Task is - find moment of sunrise of the Venus, then h is near 0(high above horizon near 0). I using phrase min(abs(h)).

When using GROUP

SELECT 'Tv' as type -- Tv = sunrise Venus, because Hour < 10
,Month
,Day
,min(abs(h)) as h
FROM Venus_horizontal_ext
WHERE h BETWEEN -0.8 AND 0.8
and Hour < 10
GROUP BY Month,Day

obtain minimal h, but i dont have hour, minute of the moment.

I need add column, which is unique....
I don't know how write this select command.

Required result is 365 rows (days);
in each row only 4 values month, day, hour, minute of the moment sunrise, when h near 0. I don't need himself h, only time values.
----------------------------
| Month | Day | Hour | Min |
----------------------------

How solve this type of SELECT "sample" ?

Thanks in advance!

Vladimir

scuzymoto
Starting Member

17 Posts

Posted - 2009-09-24 : 19:11:17

What about something like this?

select [month], [day], hour, [min] from [Astro].[dbo].[Venus_horizontal_ext] AzimuthA
where row_id in
(select top 1 row_id from [Astro].[dbo].[Venus_horizontal_ext] AzimuthB
where AzimuthA.[month] = AzimuthB.[month] and AzimuthA.[day] = AzimuthB.[day]
order by abs(h) asc)

scuzymoto
Starting Member

17 Posts

Posted - 2009-09-24 : 19:14:08

FYI, what I gave you won't work if row_id isn't unique to each row. Based on the name I am assuming that it is unique but just be aware.

scuzymoto
Starting Member

17 Posts

Posted - 2009-09-24 : 19:20:52

whoops forgot the year.. this should work better..

select [month], [day], [hour], [min] from [Astro].[dbo].[Venus_horizontal_ext] AzimuthA
where row_id in
(select top 1 row_id from [Astro].[dbo].[Venus_horizontal_ext] AzimuthB
where
AzimuthA.[year] = AzimuthB.[year] and
AzimuthA.[month] = AzimuthB.[month] and
AzimuthA.[day] = AzimuthB.[day]
order by abs(h) asc)

dalibor
Starting Member

21 Posts

Posted - 2009-09-29 : 03:08:24

Thanks to scuzymoto, it really works; select command is time-consuming (5:17 min), but works. Now i know, how solve that type commands.
Thank you

Vladimir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources