Help on SQL syntax

Dear Anyone,
I am no expert with SQL server but I am trying obtain
some data from this server using SQL syntax, I would like
to average out the data by correction_col_date below, can
anyone help please!
SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME = SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
CORRECTED_COL_DATE
Thanks,
AnnetteYou can use AVG() aggregate function with GROUP BY clause, for example:
SELECT SAMPLE.CORRECTED_COL_DATE, AVG(SAMPLE.PRODUCT_GRADE)
FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS, PRODUCT
WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND
PRODUCT.NAME = SAMPLE.PRODUCT AND
PRODUCT_SPEC.VERSION = SAMPLE.PRODUCT_VERSION AND
PRODUCT_SPEC.GRADE = SAMPLE.PRODUCT_GRADE AND
PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME LIKE '%SD'))
AND
RESULT.UNITS=UNITS.UNIT_CODE AND
RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'
GROUP BY SAMPLE.CORRECTED_COL_DATE
ORDER BY CORRECTED_COL_DATE
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Annette Malini" <anonymous@.discussions.microsoft.com> wrote in message
news:731d01c48423$63065e20$a501280a@.phx.gbl...
> Dear Anyone,
> I am no expert with SQL server but I am trying obtain
> some data from this server using SQL syntax, I would like
> to average out the data by correction_col_date below, can
> anyone help please!
> SELECT SAMPLE.PRODUCT_VERSION, PRODUCT.DESCRIPTION,
> SAMPLE.PRODUCT, SAMPLE.PRODUCT_GRADE,
> SAMPLE.CORRECTED_COL_DATE, SAMPLE.COLLECTION_TIME,
> SAMPLE.LOCATION, PRODUCT_SPEC.NOMINAL_VALUE,
> PRODUCT_SPEC.MAX_VALUE, PRODUCT_SPEC.MIN_VALUE,
> RESULT.IN_SPEC, SAMPLE.LOGIN_BY, UNITS.DISPLAY_STRING,
> RESULT.NAME, RESULT.ALIAS_NAME, RESULT.REPORTED_NAME,
> RESULT.ENTRY FROM PRODUCT_SPEC, SAMPLE, RESULT, UNITS,
> PRODUCT WHERE SAMPLE.PRODUCT = <<PROD_BRWSER>> AND
> SAMPLE.CORRECTED_COL_DATE BETWEEN <<DATERANGE>> AND
> SAMPLE.LOCATION= <<LOCATION_BROWSE>> AND
> PRODUCT_SPEC.PRODUCT = SAMPLE.PRODUCT AND PRODUCT.NAME => SAMPLE.PRODUCT AND PRODUCT_SPEC.VERSION => SAMPLE.PRODUCT_VERSION AND PRODUCT_SPEC.GRADE => SAMPLE.PRODUCT_GRADE AND
> PRODUCT_SPEC.ANALYSIS = RESULT.ANALYSIS AND
> PRODUCT_SPEC.COMPONENT = RESULT.NAME AND
> RESULT.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND
> ((RESULT.ALIAS_NAME LIKE '%MEAN') OR (RESULT.ALIAS_NAME
> LIKE '%SD')) AND RESULT.UNITS=UNITS.UNIT_CODE AND
> RESULT.STATUS = 'A' AND SAMPLE.STATUS = 'A'ORDER BY
> CORRECTED_COL_DATE
> Thanks,
> Annette
>

Help obtain a window of rows from a table

Hi,
I have a client program in vb.net that access a SQL server database. Each
time the client program need some data it retrieve the whole table, so it is
pretty slow.
I wonder if it is possible, for the client, to retrieve only a window of
rows around the actual value he is using. That is, if he is actually in row
4000 he will retrieve from row 3000 to 5000 but not the complete table. If
this is possible what I need is something like:
1) The client send SQL-Server a string with the actual ordering and the ID
of the actual row.
2) SQL-Server order the table following the order specified in the string
send by the client.
3) Using the ordered table SQL-Server "find" the ID of the actual row.
4) SQL-Server return a number of rows before and after the Id of the actual
row (no idea how to do this).
Any help.
Thanks,
JamesHi James,
Yes - its basically paging.
The basics are this...
declare @.results table (
idrow int not null identity,
yourresultcol1...
yourresultcol2...
)
insert @.results ( yourresultscol1, yourresultscol2 )
select yourresultscol1, yourresultscol2
from table...
where ...
order by ...
select *
from @.results
where idrow between @.start and @.finish
I know its not a complete working example but does that give you enough
idea?
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||Sorry, i forgot to mention, in SQL 2005 its a whole lot easier.
We have the rownumber() function and cte that does it all for us - there are
some really useful examples in bol.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uhnuUbNHGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi James,
> Yes - its basically paging.
> The basics are this...
> declare @.results table (
> idrow int not null identity,
> yourresultcol1...
> yourresultcol2...
> )
> insert @.results ( yourresultscol1, yourresultscol2 )
> select yourresultscol1, yourresultscol2
> from table...
> where ...
> order by ...
> select *
> from @.results
> where idrow between @.start and @.finish
> I know its not a complete working example but does that give you enough
> idea?
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "James" <info@.pricetech.es> wrote in message
> news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
>|||James
I think Tom Moreau had already answered the same or almost the same question
a few days ago. Pls search on internet
"James" <info@.pricetech.es> wrote in message
news:OTkWPANHGHA.1628@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
> is pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in
> row 4000 he will retrieve from row 3000 to 5000 but not the complete
> table. If this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the
> actual row (no idea how to do this).
> Any help.
> Thanks,
> James
>
>|||James wrote:
> Hi,
> I have a client program in vb.net that access a SQL server database. Each
> time the client program need some data it retrieve the whole table, so it
is
> pretty slow.
> I wonder if it is possible, for the client, to retrieve only a window of
> rows around the actual value he is using. That is, if he is actually in ro
w
> 4000 he will retrieve from row 3000 to 5000 but not the complete table. If
> this is possible what I need is something like:
> 1) The client send SQL-Server a string with the actual ordering and the ID
> of the actual row.
> 2) SQL-Server order the table following the order specified in the string
> send by the client.
> 3) Using the ordered table SQL-Server "find" the ID of the actual row.
> 4) SQL-Server return a number of rows before and after the Id of the actua
l
> row (no idea how to do this).
> Any help.
> Thanks,
> James
Take a look at:
http://www.aspfaq.com/show.asp?id=2120
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--