Can anyone reccomend a fast-response SQL forum. And can anyone help in the meantime?

Factual Questions
1
  1. Can you reccomend a fast-response SQL forum of experts?
  2. Until I have that I’ll tap THIS fast-response forum for help…
    SQL is good/clever and all that, but often I can’t work out how to do remarkably simple things with it, maybe it’s not possible or maybe I’m not that good at SQL.
    For example… Imagine a table which shows a lot of transactions… they are bets (with amounts, dates, and the balance after the transaction)

I need to run a query on the table which, in one go can show the START balance, and then sums of other columns.
In other words I need to return the FIRST record only of balance forward, and a SUM of other columns.

Can this be done without constructing sub-queries within queries???
My intuition is to construct a query like this…

select top 1 bal_forward,sum(bets) from trans_table, group by date, order by date,time

But I know that won’t work… because ‘top 1’ acts on the entire query.

2

I am an Oracle SQL expert and I can do that type of thing easily by not so much on other SQL flavors. You need SUBSELECTS to do that which really aren’t that hard once you wrap you mind around it.

If you just learn a single SUBSELECT that you want, things will become much more clear and you can use the same principle over and over. The SQL I work with and write is often several thousand lines combined with other SQL statements that are the same. They are just built off of these pieces however. You example is very simplistic SQL. You probably need to take it up a couple of levels.

3

I don’t have a SQL tool running on this machine, so I can’t test these, but try eliminating the GROUP BY clause

select top 1 bal_forward,sum(bets) from trans_table order by date,time

If you need to limit by date / time, use the WHERE clause, as in

select top 1 bal_forward,sum(bets) from trans_table where date=‘2008-03-01 00:00:00.000’

4

Oracle has traditionally set itself ahead of the competition in this area by providing analytic functions to do these sorts of things efficiently, but analytic functions are by no means standard ANSI SQL. In any event the sort of stuff you seem to be interested in is exactly the sort of thing analytic functions were designed for.

I’ve heard that recent versions of Microsoft SQL Server have added at least some analytic functions as well, don’t know about any other platforms.