473,372 Members | 1,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

SQL row filtering

Hello,

I've been working on this problem in vain for several days now. I
essentially am looking for one row per hour where that row is the youngest
row of that hour.

So if there are say three rows labeled (17:00, 17:41, 17:43) I only want
17:43's row to show for the 17th hour. Essentially I want to see what I
have in the table below but just with out the red rows (if you cant see the
red, its the 17:00 and 17:41 rows). How can I fix or rewrite this select
statement to get what I want.

I've looked at using group by/having, correlated subselects, between. I can
get a union of 24 correlated subselects to work, but its way too slow for
what I'd need.

Thanks inadvance for any help anyone can give.

- SQL Man

-----------------------------------------------------

SELECT hourlycounter, extract(hour from utcissuetime) as hour,
extract(minute from utcissuetime) as minute, CumulativePrecipitation,
OneHourPrecipitation

FROM observations, awos
WHERE stationid='YIO'
and hourlyidentifier = 201
and utcissuedate = '20031006'
and utcissuetime >= '00:00:00'
and utcissuetime <= '19:00:00'
and observations.obsid = awos.obsid
ORDER BY hour DESC, minute DESC;

hourlycounter | hour | minute | cumulativeprecipitation |
onehourprecipitation
---------------+------+--------+-------------------------+----------------
------
0 | 19 | 0 | 46.5 |
0.5
0 | 18 | 0 | 46 |
0.5
3 | 17 | 43 | 78 |
2 | 17 | 41 | 78 |
0 | 17 | 0 | 45.5 |
0.5
0 | 16 | 0 | 45 |
0
0 | 15 | 0 | 45 |
0.5
0 | 14 | 0 | 44.5 |
0
0 | 13 | 0 | 44.5 |
0
0 | 12 | 0 | 44.5 |
0
0 | 11 | 0 | 44.5 |
0.5
0 | 10 | 0 | 44 |
0.5
0 | 9 | 0 | 43.5 |
0
0 | 8 | 0 | 43.5 |
0
0 | 7 | 0 | 43.5 |
0
0 | 6 | 0 | 43.5 |
0
0 | 5 | 0 | 43.5 |
0
0 | 4 | 0 | 43.5 |
0
0 | 3 | 0 | 43.5 |
0
0 | 2 | 0 | 43.5 |
0
0 | 1 | 0 | 43.5 |
0
0 | 0 | 0 | 43.5 |
0
(22 rows)
Jul 19 '05 #1
0 1463

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: N.K. | last post by:
Hello, I'm trying to find a way to flexibly filter a column in a row of data read from a file. I will have data type of that column, the operation to be performed on it (like >, <, <= etc.) and the...
3
by: Alex Ayzin | last post by:
Hi, I have a problem that might be easy to solve(possibly, I've just overlooked an easy solution). Here we go: I have a dataset with 2 datatables in it. Now, I need to do the following: if...
3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
5
by: Richard | last post by:
Hi, I have a form that take some time to load due to many comboboxes and at least 8 subforms. When I filter or sort the main form I get an error message and then Access shuts down. They ask if...
10
by: milk-jam | last post by:
I'm trying to set my datagridview so that the first row will be left blank and to use it as a filtering filed for the datagridview. Until now I was using 2 datagridview the upper one with a header...
2
by: Konrad | last post by:
Hi Can you point examples in .NET of filtering (avoiding) displaying web pages with unwanted content on machine with ie? Thanks Konrad
7
by: | last post by:
Hello, Does anyone have an idea on how I can filter the data in the gridview control that was returned by an sql query? I have a gridview that works fine when I populate it with data. Now I...
2
by: JUAN ERNESTO FLORES BELTRAN | last post by:
Hi you all, I am developping a python application which connects to a database (postresql) and displays the query results on a treeview. In adittion to displaying the info i do need to implement...
3
by: Shawn Ramirez | last post by:
As with most web applications speed is a huge deal to me in my applications. My customers don't really care if my app is a true 3 tier application or not, they just want it to be faster then it was...
3
by: Harry Haller | last post by:
Hello, I want to implement a generic list which will be used to display 7 columns in a GridView. One should be able to sort, filter and page each of the 7 columns. Ideally the filter should be...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.