Professional Web Applications Themes

SQL Server - Alternate for First() Last() functions available in MS Access - Microsoft SQL / MS SQL Server

Hi, I am trying to find out an alternate way to implement the First() & Last() functions (MS Access) in the SQL SELECT statement of SQL Server. Here is the actual problem I am facing: I am having a table Table1 Name SeqNo Nam1 4 Nam1 5 Nam1 1 Nam2 10 Nam2 15 Nam2 5 In MS Access I can write a SELECT statement: SELECT Name, FIRST(SeqNo), LAST(SeqNo) FROM Table1 GROUP BY Name The above select statement will return values like: Nam1 4 1 Nam2 10 5 The First() function in MS access will return the first keyed-in/available data of ...

  1. #1

    Default SQL Server - Alternate for First() Last() functions available in MS Access

    Hi,
    I am trying to find out an alternate way to implement the First() & Last()
    functions (MS Access) in the SQL SELECT statement of SQL Server.

    Here is the actual problem I am facing:

    I am having a table Table1

    Name SeqNo
    Nam1 4
    Nam1 5
    Nam1 1
    Nam2 10
    Nam2 15
    Nam2 5

    In MS Access I can write a SELECT statement:
    SELECT Name, FIRST(SeqNo), LAST(SeqNo) FROM Table1 GROUP BY Name

    The above select statement will return values like:

    Nam1 4 1
    Nam2 10 5

    The First() function in MS access will return the first keyed-in/available
    data of the resultset and Last() vice versa. This function can be used in MS
    access as the Aggregate function. The problem I am facing is, I am trying to
    convert this SQL to SQL Server and I couldn't find a way to implement this
    First() and Last() in SQL Server SELECT.

    Any help highly appreciated

    Thanks In Advance,
    Arun


    Arun Subramanian Guest

  2. #2

    Default Re: SQL Server - Alternate for First() Last() functions available in MS Access

    > The First() function in MS access will return the first keyed-in/available
    > data of the resultset and Last() vice versa. This function can be used in
    MS

    If you really want the first-entered or last-entered value then you need to
    make sure you capture that information. Put an Entry DATETIME on the table
    or populate a sequence column in Access before you import the data. Without
    that your data is inclomplete. Remember that a table in SQLServer has no
    inherent ordering.

    If you just want a value from some arbitrary "first" or "last" row then use
    the primary key.

    You didn't tell us what the PK is. But assuming you have a sequence number
    or unique column called "Keycol":

    SELECT [name],
    MIN(CASE keycol WHEN R.firstrow THEN seqno END) AS first_seqno,
    MIN(CASE keycol WHEN R.lastrow THEN seqno END) AS last_seqno,
    FROM Table1 AS T
    JOIN
    (SELECT [name],
    MIN(keycol) AS firstrow,
    MAX(keycol) AS lastrow
    FROM Table1
    GROUP BY [name]) AS R
    ON T.[name]=R.[name]
    GROUP BY name

    (untested)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Arun Subramanian" <arunsgslycos.com> wrote in message
    news:uUiAqomRDHA.3236TK2MSFTNGP10.phx.gbl...
    > Hi,
    > I am trying to find out an alternate way to implement the First() & Last()
    > functions (MS Access) in the SQL SELECT statement of SQL Server.
    >
    > Here is the actual problem I am facing:
    >
    > I am having a table Table1
    >
    > Name SeqNo
    > Nam1 4
    > Nam1 5
    > Nam1 1
    > Nam2 10
    > Nam2 15
    > Nam2 5
    >
    > In MS Access I can write a SELECT statement:
    > SELECT Name, FIRST(SeqNo), LAST(SeqNo) FROM Table1 GROUP BY Name
    >
    > The above select statement will return values like:
    >
    > Nam1 4 1
    > Nam2 10 5
    >
    > The First() function in MS access will return the first keyed-in/available
    > data of the resultset and Last() vice versa. This function can be used in
    MS
    > access as the Aggregate function. The problem I am facing is, I am trying
    to
    > convert this SQL to SQL Server and I couldn't find a way to implement this
    > First() and Last() in SQL Server SELECT.
    >
    > Any help highly appreciated
    >
    > Thanks In Advance,
    > Arun
    >
    >

    David Portas Guest

  3. #3

    Default Re: SQL Server - Alternate for First() Last() functions available in MS Access

    The table you have supplied is an unordered list. In order to write a query
    to accomplish what you need, I suspect you will require additional
    information by which to sort the data by. You cannot rely on the table to
    always return data in the order it is entered. Ideally you should have an
    additional column such as a createdate, or unique identity by which to sort.
    Without this I don't see how you can reliably query the data.

    "Arun Subramanian" <arunsgslycos.com> wrote in message
    news:uUiAqomRDHA.3236TK2MSFTNGP10.phx.gbl...
    > Hi,
    > I am trying to find out an alternate way to implement the First() & Last()
    > functions (MS Access) in the SQL SELECT statement of SQL Server.
    >
    > Here is the actual problem I am facing:
    >
    > I am having a table Table1
    >
    > Name SeqNo
    > Nam1 4
    > Nam1 5
    > Nam1 1
    > Nam2 10
    > Nam2 15
    > Nam2 5
    >
    > In MS Access I can write a SELECT statement:
    > SELECT Name, FIRST(SeqNo), LAST(SeqNo) FROM Table1 GROUP BY Name
    >
    > The above select statement will return values like:
    >
    > Nam1 4 1
    > Nam2 10 5
    >
    > The First() function in MS access will return the first keyed-in/available
    > data of the resultset and Last() vice versa. This function can be used in
    MS
    > access as the Aggregate function. The problem I am facing is, I am trying
    to
    > convert this SQL to SQL Server and I couldn't find a way to implement this
    > First() and Last() in SQL Server SELECT.
    >
    > Any help highly appreciated
    >
    > Thanks In Advance,
    > Arun
    >
    >

    Stuey Guest

  4. #4

    Default Re: SQL Server - Alternate for First() Last() functions available in MS Access

    SQL server does not have any function like this. probably if you have an
    additional column which keeps track of the serial no of the row that has
    been inserted then you can use aggregate functions like min() and max() on
    this column
    Ex:(im making use of column "ky" here)
    create table #t(Name varchar(500),SeqNo int, Ky int)
    insert into #t
    select 'Nam1', 4, 1 union all select
    'Nam1', 5 ,2 union all select
    'Nam1', 1 ,3 union all select
    'Nam2', 10 ,1 union all select
    'Nam2', 15 ,2 union all select
    'Nam2', 5 ,3


    select a.name, max(case b.ky when a.mn then seqno else 0 end) min, max(case
    b.ky when a.mx then seqno else 0 end) max
    from #t b,(select name, min(ky) mn, max(ky) mx
    from #t
    group by name) a
    where a.name=b.name
    group by a.name

    --
    -Vishal

    "Arun Subramanian" <arunsgslycos.com> wrote in message
    news:uUiAqomRDHA.3236TK2MSFTNGP10.phx.gbl...
    > Hi,
    > I am trying to find out an alternate way to implement the First() & Last()
    > functions (MS Access) in the SQL SELECT statement of SQL Server.
    >
    > Here is the actual problem I am facing:
    >
    > I am having a table Table1
    >
    > Name SeqNo
    > Nam1 4
    > Nam1 5
    > Nam1 1
    > Nam2 10
    > Nam2 15
    > Nam2 5
    >
    > In MS Access I can write a SELECT statement:
    > SELECT Name, FIRST(SeqNo), LAST(SeqNo) FROM Table1 GROUP BY Name
    >
    > The above select statement will return values like:
    >
    > Nam1 4 1
    > Nam2 10 5
    >
    > The First() function in MS access will return the first keyed-in/available
    > data of the resultset and Last() vice versa. This function can be used in
    MS
    > access as the Aggregate function. The problem I am facing is, I am trying
    to
    > convert this SQL to SQL Server and I couldn't find a way to implement this
    > First() and Last() in SQL Server SELECT.
    >
    > Any help highly appreciated
    >
    > Thanks In Advance,
    > Arun
    >
    >

    Vishal Parkar Guest

  5. #5

    Default Re: SQL Server - Alternate for First() Last() functions available in MS Access

    You need a sequence number, This is based on David's and Vishal's samples.

    DROP TABLE #A
    CREATE TABLE #A([Name] VarChar(15), SeqNo INT)

    INSERT INTO #A
    SELECT 'Nam1', 4
    UNION ALL
    SELECT 'Nam1', 5
    UNION ALL
    SELECT 'Nam1', 1
    UNION ALL
    SELECT 'Nam2', 10
    UNION ALL
    SELECT 'Nam2', 15
    UNION ALL
    SELECT 'Nam2', 5

    SELECT * FROM #A

    DROP TABLE #B
    CREATE TABLE #B([Name] VarChar(15), SeqNo INT, KeyCol INT Identity)

    INSERT INTO #B
    SELECT * FROM #A

    SELECT BB.[Name],
    MIN(CASE KeyCol WHEN R.FirstRow THEN SeqNo END) AS First_SeqNo,
    MIN(CASE KeyCol WHEN R.LastRow THEN SeqNo END) AS Last_SeqNo
    FROM #B AS BB INNER JOIN
    (
    SELECT C.[Name], MIN(C.KeyCol) AS FirstRow, MAX(C.KeyCol) AS LastRow
    FROM #B AS C
    GROUP BY C.[Name]
    ) AS R
    ON BB.[Name]=R.[Name]
    GROUP BY BB.[Name]



    HTH

    Jose

    "Arun Subramanian" <arunsgslycos.com> wrote in message
    news:uUiAqomRDHA.3236TK2MSFTNGP10.phx.gbl...
    > Hi,
    > I am trying to find out an alternate way to implement the First() & Last()
    > functions (MS Access) in the SQL SELECT statement of SQL Server.
    >
    > Here is the actual problem I am facing:
    >
    > I am having a table Table1
    >
    > Name SeqNo
    > Nam1 4
    > Nam1 5
    > Nam1 1
    > Nam2 10
    > Nam2 15
    > Nam2 5
    >
    > In MS Access I can write a SELECT statement:
    > SELECT Name, FIRST(SeqNo), LAST(SeqNo) FROM Table1 GROUP BY Name
    >
    > The above select statement will return values like:
    >
    > Nam1 4 1
    > Nam2 10 5
    >
    > The First() function in MS access will return the first keyed-in/available
    > data of the resultset and Last() vice versa. This function can be used in
    MS
    > access as the Aggregate function. The problem I am facing is, I am trying
    to
    > convert this SQL to SQL Server and I couldn't find a way to implement this
    > First() and Last() in SQL Server SELECT.
    >
    > Any help highly appreciated
    >
    > Thanks In Advance,
    > Arun
    >
    >

    Jose Hernandez Guest

  6. #6

    Default Re: SQL Server - Alternate for First() Last() functions available in MS Access

    Is there is any way, I can do it without creating a new column. Cuz, the
    schema already there and I do not want to change the schema. Something like
    a row identifier (if any) stored inside the SQL table (hidden columns, if
    any).. which will make a record to be unique that I can use it in the SQL
    selection with the MIN and the MAX function. The MS Access FIRST()/LAST()
    are the very good examples. The jet SQL does it without creating the
    SEQUENCE #.. just wondering, whether I can utilize any of the in-built
    mechanism/system functions hidden secrets of the SQL server to achieve this,
    without creating a new column 'SEQUENCE #'

    I do appreciate you all for the immediate responses.

    Thanks,
    Arun

    "Jose Hernandez" <jh3rnand3z_hotmail.com> wrote in message
    news:O$e%23zinRDHA.2148TK2MSFTNGP10.phx.gbl...
    > You need a sequence number, This is based on David's and Vishal's samples.
    >
    > DROP TABLE #A
    > CREATE TABLE #A([Name] VarChar(15), SeqNo INT)
    >
    > INSERT INTO #A
    > SELECT 'Nam1', 4
    > UNION ALL
    > SELECT 'Nam1', 5
    > UNION ALL
    > SELECT 'Nam1', 1
    > UNION ALL
    > SELECT 'Nam2', 10
    > UNION ALL
    > SELECT 'Nam2', 15
    > UNION ALL
    > SELECT 'Nam2', 5
    >
    > SELECT * FROM #A
    >
    > DROP TABLE #B
    > CREATE TABLE #B([Name] VarChar(15), SeqNo INT, KeyCol INT Identity)
    >
    > INSERT INTO #B
    > SELECT * FROM #A
    >
    > SELECT BB.[Name],
    > MIN(CASE KeyCol WHEN R.FirstRow THEN SeqNo END) AS First_SeqNo,
    > MIN(CASE KeyCol WHEN R.LastRow THEN SeqNo END) AS Last_SeqNo
    > FROM #B AS BB INNER JOIN
    > (
    > SELECT C.[Name], MIN(C.KeyCol) AS FirstRow, MAX(C.KeyCol) AS LastRow
    > FROM #B AS C
    > GROUP BY C.[Name]
    > ) AS R
    > ON BB.[Name]=R.[Name]
    > GROUP BY BB.[Name]
    >
    >
    >
    > HTH
    >
    > Jose
    >
    > "Arun Subramanian" <arunsgslycos.com> wrote in message
    > news:uUiAqomRDHA.3236TK2MSFTNGP10.phx.gbl...
    > > Hi,
    > > I am trying to find out an alternate way to implement the First() &
    Last()
    > > functions (MS Access) in the SQL SELECT statement of SQL Server.
    > >
    > > Here is the actual problem I am facing:
    > >
    > > I am having a table Table1
    > >
    > > Name SeqNo
    > > Nam1 4
    > > Nam1 5
    > > Nam1 1
    > > Nam2 10
    > > Nam2 15
    > > Nam2 5
    > >
    > > In MS Access I can write a SELECT statement:
    > > SELECT Name, FIRST(SeqNo), LAST(SeqNo) FROM Table1 GROUP BY Name
    > >
    > > The above select statement will return values like:
    > >
    > > Nam1 4 1
    > > Nam2 10 5
    > >
    > > The First() function in MS access will return the first
    keyed-in/available
    > > data of the resultset and Last() vice versa. This function can be used
    in
    > MS
    > > access as the Aggregate function. The problem I am facing is, I am
    trying
    > to
    > > convert this SQL to SQL Server and I couldn't find a way to implement
    this
    > > First() and Last() in SQL Server SELECT.
    > >
    > > Any help highly appreciated
    > >
    > > Thanks In Advance,
    > > Arun
    > >
    > >
    >
    >

    Arun Subramanian Guest

  7. #7

    Default Re: SQL Server - Alternate for First() Last() functions available in MS Access

    > any).. which will make a record to be unique that I can use it in the SQL


    Are you saying that this table doesn't have a primary key? EVERY table
    should have a primary key. If you don't fix this you will have lots more
    problems. In SQL many operations are impossible or at least extremely
    difficult without a primary key.

    > The jet SQL does it without creating the
    > SEQUENCE #.. just wondering, whether I can utilize any of the in-built
    > mechanism/system functions hidden secrets of the SQL server to achieve
    this,
    > without creating a new column 'SEQUENCE #'
    No. There is no hidden solution. You need a unique sequence number or at
    least some column which can identitfy the "first" and "last" rows. The power
    of SQL is in its set-based nature which is conceptually quite different to
    the record-by-record type of processing familiar to you from Jet. That is
    it's strength but it also puts the onus on the developer to design the
    database architecture according to relational principles. Fix your schema
    design.


    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Arun Subramanian" <arunsgslycos.com> wrote in message
    news:OW$gyCxRDHA.2240TK2MSFTNGP11.phx.gbl...
    > Is there is any way, I can do it without creating a new column. Cuz, the
    > schema already there and I do not want to change the schema. Something
    like
    > a row identifier (if any) stored inside the SQL table (hidden columns, if
    > any).. which will make a record to be unique that I can use it in the SQL
    > selection with the MIN and the MAX function. The MS Access FIRST()/LAST()
    > are the very good examples. The jet SQL does it without creating the
    > SEQUENCE #.. just wondering, whether I can utilize any of the in-built
    > mechanism/system functions hidden secrets of the SQL server to achieve
    this,
    > without creating a new column 'SEQUENCE #'
    >
    > I do appreciate you all for the immediate responses.
    >
    > Thanks,
    > Arun
    >
    > "Jose Hernandez" <jh3rnand3z_hotmail.com> wrote in message
    > news:O$e%23zinRDHA.2148TK2MSFTNGP10.phx.gbl...
    > > You need a sequence number, This is based on David's and Vishal's
    samples.
    > >
    > > DROP TABLE #A
    > > CREATE TABLE #A([Name] VarChar(15), SeqNo INT)
    > >
    > > INSERT INTO #A
    > > SELECT 'Nam1', 4
    > > UNION ALL
    > > SELECT 'Nam1', 5
    > > UNION ALL
    > > SELECT 'Nam1', 1
    > > UNION ALL
    > > SELECT 'Nam2', 10
    > > UNION ALL
    > > SELECT 'Nam2', 15
    > > UNION ALL
    > > SELECT 'Nam2', 5
    > >
    > > SELECT * FROM #A
    > >
    > > DROP TABLE #B
    > > CREATE TABLE #B([Name] VarChar(15), SeqNo INT, KeyCol INT Identity)
    > >
    > > INSERT INTO #B
    > > SELECT * FROM #A
    > >
    > > SELECT BB.[Name],
    > > MIN(CASE KeyCol WHEN R.FirstRow THEN SeqNo END) AS First_SeqNo,
    > > MIN(CASE KeyCol WHEN R.LastRow THEN SeqNo END) AS Last_SeqNo
    > > FROM #B AS BB INNER JOIN
    > > (
    > > SELECT C.[Name], MIN(C.KeyCol) AS FirstRow, MAX(C.KeyCol) AS LastRow
    > > FROM #B AS C
    > > GROUP BY C.[Name]
    > > ) AS R
    > > ON BB.[Name]=R.[Name]
    > > GROUP BY BB.[Name]
    > >
    > >
    > >
    > > HTH
    > >
    > > Jose
    > >
    > > "Arun Subramanian" <arunsgslycos.com> wrote in message
    > > news:uUiAqomRDHA.3236TK2MSFTNGP10.phx.gbl...
    > > > Hi,
    > > > I am trying to find out an alternate way to implement the First() &
    > Last()
    > > > functions (MS Access) in the SQL SELECT statement of SQL Server.
    > > >
    > > > Here is the actual problem I am facing:
    > > >
    > > > I am having a table Table1
    > > >
    > > > Name SeqNo
    > > > Nam1 4
    > > > Nam1 5
    > > > Nam1 1
    > > > Nam2 10
    > > > Nam2 15
    > > > Nam2 5
    > > >
    > > > In MS Access I can write a SELECT statement:
    > > > SELECT Name, FIRST(SeqNo), LAST(SeqNo) FROM Table1 GROUP BY Name
    > > >
    > > > The above select statement will return values like:
    > > >
    > > > Nam1 4 1
    > > > Nam2 10 5
    > > >
    > > > The First() function in MS access will return the first
    > keyed-in/available
    > > > data of the resultset and Last() vice versa. This function can be used
    > in
    > > MS
    > > > access as the Aggregate function. The problem I am facing is, I am
    > trying
    > > to
    > > > convert this SQL to SQL Server and I couldn't find a way to implement
    > this
    > > > First() and Last() in SQL Server SELECT.
    > > >
    > > > Any help highly appreciated
    > > >
    > > > Thanks In Advance,
    > > > Arun
    > > >
    > > >
    > >
    > >
    >
    >

    David Portas Guest

Similar Threads

  1. Replies: 1
    Last Post: April 28th, 06:48 AM
  2. VB Functions in Access Module
    By Chris Hohmann in forum ASP Database
    Replies: 2
    Last Post: September 3rd, 08:36 PM
  3. Accessing an alternate SQL Server within a proc
    By rebam in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 11th, 03:34 PM
  4. Can't access machine with alternate name assigned in WINS
    By Sundev Bal in forum Windows Networking
    Replies: 0
    Last Post: July 11th, 03:47 AM
  5. SQL Server dialect functions
    By Giovanni in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 3rd, 10:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139