Vb.net and Sql Select From Group
-
I have an application into which data on horse races is entered, I then click calculate to work out and save the top 3 horses into a seperate database.
I am trying to find the highest value within any column in consecutive groups of 3 rows in a datagrid.
The 3 rows are grouped using a flag in the last column on the 3rd row.
I am looking at running various queries on the data within the screen capture.
1) Identifying highest value in the column labelled 1 for each group of 3.
2) Identifying highest value in the column labelled 2 for each group of 3.
3) Identifying data within columns labelled Bld and >> in a similar style to the queries mentioned in 1) and 2).
I also have to be able to perform these queries on results of data returned from a previous query.
4) Once I deduct the value in the column labelled 4 from the value in the column labelled 5, I have to identify when the highest score within the 3 results falls on the same line as highest B and Lowest FDB.
Using VB.net and Sql is relatively new to me, so your assistance is greatly appreciated.
Thanks
-
I cannot give you a real detailed answer without DDL's and sample data (see http://www.sqlservercentral.com/articles/Best+Practices/61537/). However the basic query type that you would use in SQL would look like this:
SELECT Flag, Max([J%]), MAX([T%]), MAX([Bld]), MAX([>>])
From Top3ResultsTable
Group By Flag
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
I have to ask: Why are you using lowercase thorn ("þ") as a column name?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
As rbarryyoung posted, we need more detail on how your data is structured. I look at your "datagrid", and can't determine if the column headings are part of the actual data, or even if the 3 sets of data are all in the same set of columns in a single table. Also, the column names are not terribly descriptive, and we have no data types. Additionally, exactly what constitutes a "group of 3"? Is it any 3 consecutive records in the table (1,2,3, or 2,3,4, or 3,4,5 etc.), or is it more rigid (1,2,3 or 4,5,6, or 7,8,9 etc.)? Finally, when you say "falls on the same line as highest B and lowest FDB", does that mean that the highest B value also has to appear in the same record that the lowest FDB value appears in, or is it that the high score has to appear on EITHER the same line as the highest B OR on the same line as the lowest FDB". You'll find that you need to not make ANY assumptions about the meaning of the written word when stating a detailed technical problem. Let us know...
Steve
(aka smunson)
:):):)
techsupt (10/10/2008)
I have an application into which data on horse races is entered, I then click calculate to work out and save the top 3 horses into a seperate database.I am trying to find the highest value within any column in consecutive groups of 3 rows in a datagrid.
The 3 rows are grouped using a flag in the last column on the 3rd row.
I am looking at running various queries on the data within the screen capture.
1) Identifying highest value in the column labelled 1 for each group of 3.
2) Identifying highest value in the column labelled 2 for each group of 3.
3) Identifying data within columns labelled Bld and >> in a similar style to the queries mentioned in 1) and 2).
I also have to be able to perform these queries on results of data returned from a previous query.
4) Once I deduct the value in the column labelled 4 from the value in the column labelled 5, I have to identify when the highest score within the 3 results falls on the same line as highest B and Lowest FDB.
Using VB.net and Sql is relatively new to me, so your assistance is greatly appreciated.
Thanks
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
The datagrid is not the actual datagrid from my application but a screen capture of a report, I included this to help illustrate the queries I am trying to execute.
I have added a screen capture of my actual datagrid below.
From the grid, you can see the column headings are not part of the data and the data does come from the same table.
The screen capture in the previous post is one of the reports that the data will be used to generate.
The columns I am trying to run queries are on of type integer.
Each group of 3 is the result of a calculation to identify the strongest 3 horses from any race.
I am also looking at criteria which will help me identify the two examples as illustrated in the screen capture below.
The screen captures I provided are from a daily racing analysis we provided, we have two sets of criteria used to identify different types of bets.
We only bet if the criteria highlights selections on the first or second row of each group of 3.
One of the common factors for all selections is that they must have the highest b and lowest fdb otherwise they do not qualify as a selection.
The majority of the time we will always produce the 3 best, the only exception is on the rare occasion where there are only 2 horses in that race.
There are instances where you can have joint b or joint fdb values or instances where one of the conditions is met but not the other.
There are other conditions to be met in regards to the selection criteria but identifying B and FDB is the first condition we need to look for.
I think to give you a better idea, I'll upload a copy of the analysis file.
I'll highlight rows matching selection criteria 1 in yellow and those matching selection criteria 2 in blue.
I hope this information is sufficient to help me with my original query
Thanks
-
If you're hoping for a tested solution on the basis of what's been provided, you're going to be disappointed. While what you did provide does help clarify portions of the logic, we still don't have any detail on the table structure (this means DDL - aka what you get when you use SSMS to script out the table create statement). Also, we have no idea what the queries are that produce those groups of 3, nor how those datum have been materialized. Please re-read rbarryyoung's post on what to provide. You really need to come forward with the table structure (DDL) for the data the desired query is expected to operate on, sample data for that table in the form of INSERT statement(s), and the exact output you would expect the desired query to produce, given the sample data. Finally, you need to clarify what you mean by "Once I deduct the value in the column labelled 4 from the value in the column labelled 5, I have to identify when the highest score within the 3 results falls on the same line as highest B and Lowest FDB.", as it's not clear what you do with the value resulting from the subtraction, nor is it clear what "highest score" means. Is it the value resulting from the subtraction, or is it something else?
Try to remember that we don't have your data or your application, so we're looking at this with nothing but some sample data in a format we can't really make much use of. Most of us are also unlikely to have much exposure to specific terms and their meaning in the context of betting on horse racing. It's not necessarily essential to producing a working query, but it appears you're making several assumptions of that nature when you describe the data.
It now also appears that this is quite likely code that's intended to become part of a commercial software product for profit, and in an industry that seems designed to take people's money, as betting on horse racing longer term is mathematically doomed to failure. That's going to limit how I contribute to pseudeocode only, and I also have to wonder if anyone you're working with on this project is aware that portions of your methodology are now effectively public, which might have the potential to make any software copyright of questionable value. Helping us help you might well make things worse from that perspective, so keep that in mind.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply