Re: Subquery Help?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hve not used T-SQL in a while. I don't think it was available in 2000, but
may be in SQL Server 2005.

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Jay" wrote:

Hi Dale, Thanks for the help. I should have mentioned that my Month field
is simply an integer i.e 1,8,9.And I'm going to have to right this in an
..adp so is DateSerial available in T-sql?

Regards,

Jason

"Dale Fye" <dale.fye@xxxxxxxxxx> wrote in message
news:AA72BBFB-9CEB-46B1-B509-1A973C948658@xxxxxxxxxxxxxxxx
Jason,

From my perspective, the easiest way to do this would be to use the
DateSerial function to create a date based on the YearField, MonthField,
and
the first day of the month Notice that I have replaced your Month and
Year
with MonthField and YearField; Month and Year are reserved words in Access
and should not be used as field names.

The subquery below identifies the maximum date for each ID and then uses
that to filter the other query.

SELECT ID, MonthField, YearField
FROM yourTable
WHERE DateSerial(YearField, MonthField, 1) = (SELECT
Max(DateSerial(YearField, MonthField, 1)) FROM yourTable T1 WHERE T1.ID =
yourTable.ID)

Another method would be:
SELECT ID, MonthField, YearField
FROM yourTable
INNER JOIN (SELECT ID, MAX(DateSerial(YearField,MonthField,1)) as MaxDate
FROM yourTable
GROUP BY ID) as T1
ON yourTable.ID = T1.ID
AND Dateserial(YearField, MonthField, 1) = T1.MaxDate

The second query would have to be written in the SQL view, and would not
be
editable or viewable in the query design view because of the join on the
DateSerial functions value.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Jay" wrote:

Hi,

I have the following 3 fields ID, Month & Year. And I need a query to
return the highest 'Year' value for each ID, but then the highest 'Month'
field for that Year & ID.

Can anyone help,

Regards.....Jasonm






.

Relevant Pages

  • Re: Update or Delete querys with Joins
    ... This is were I was missing the boat Steve. ... update multiple columns in the ANSI with the same right-side of query. ... that time as Microsoft deems it suitable for inclusion into T-SQL. ... > but SQL Server doesn't implement it. ...
    (microsoft.public.sqlserver.programming)
  • Re: What are these queries used for?
    ... A query exactly like the one above is rather pointless. ... T-SQL are also defined in ANSI, but there are some features that T-SQL ... has added in addition to the ANSI standard, ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)
  • Re: Slow Query
    ... It looks like you're using SQL Server so, you'd use T-SQL syntax & language to create the query. ... In the ODBC Connect Str property put the correct ODBC connection ...
    (microsoft.public.access.queries)
  • Re: DTS Global Variables + SQL query
    ... they query I'm trying to run isn't as straight forward as I ... > would do in T-SQL. ... > Global Variables and SQL statements in DTS ... > Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)