Post reply

After group, which one option in SQL should be selected to match Access "First" option?

  • May 11, 2017 at 8:39 am

    #323423

    I have a project to convert query in Access to SQL views.
    In Access query, once adding a group, there are many options under group such as "First".
    But in SQL views, once adding a group, there is only options "Min" or "Min Distinct"  under group.
    Which one option in SQL should be selected to match Access "First" option?

  • May 11, 2017 at 10:02 am

    #1942188

    adonetok - Thursday, May 11, 2017 8:39 AM

    I have a project to convert query in Access to SQL views.
    In Access query, once adding a group, there are many options under group such as "First".
    But in SQL views, once adding a group, there is only options "Min" or "Min Distinct"  under group.
    Which one option in SQL should be selected to match Access "First" option?

    Have a look at ROW_NUMBER().

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • May 11, 2017 at 10:28 am

    #1942190

    There really is no equivalent.  Chris does offer something that can help get close to it assuming your data supports an acceptable ordering for this purpose.

    Since Access doesn't have a separate storage engine, the reality is that physical order does matter there:  Tables are read in sequentially based on how the data is stored, so "first" yields the same record to a much greater level of reliability. Storage is very different in SQL Server and other RDBMS': caching and other optimizations play in to a much larger degree, so physical order isn't reliable.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 11, 2017 at 10:30 am

    #1942191

    Are you looking for FIRST_VALUE?  It uses and OVER clause instead of GROUP BY, but I'm sure you''d be able to make the necessary adjustments.

    John

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply