Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: First statement from Access to SQL

    I'm trying to convert this from Access to SQL:

    SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC
    FROM ORDER_DETAIL

    But it won't accept the First statement, specifically "First" is not recognized.

    First(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC

    Does anyone know a way around?
    Thanks,
    Bill

  2. #2
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    First vs Top

    I'd test this in the SQL Anaylyser:


    SELECT ORDER_DETAIL.ORDER_NUMBER, Substring([ITEM_NUMBER],2,7) AS ITEMNO, Top(ORDER_DETAIL.ITEM_DESC) AS FirstOfITEM_DESC
    FROM ORDER_DETAIL

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Hey

    Let a brother know if it works

    And remember

    The RedSkins are back and Championship will follow.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Understand that the concept of first and last doesn't have any meaning

    Don't know how access doesn't...but you can do

    Code:
    SELECT   ORDER_NUMBER
    	, SUBSTRING(ITEM_NUMBER,2,7) AS ITEMNO
    	, MAX(ITEM_DESC) AS MAX_ITEM_DESC
      FROM ORDER_DETAIL
    GROUP BY  ORDER_NUMBER
    	, SUBSTRING(ITEM_NUMBER,2,7)
    Or

    Code:
    SELECT   TOP 1 ORDER_NUMBER
    	, SUBSTRING(ITEM_NUMBER,2,7) AS ITEMNO
    	, MAX(ITEM_DESC) AS MAX_ITEM_DESC
      FROM ORDER_DETAIL
    ORDER BY  SUBSTRING(ITEM_NUMBER,2,7)
    You need to know if you want the MAX or MIN, or to order by something

    The order of data in a database has no meaning...

    Go Ellie!

    PS> Another fluf post on my part...damn....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i wouldn't exactly call it a fluf post, but your second code example is wrong, it has a non-aggregate expression in the SELECT list along with the MAX aggregate, but it's lacking the GROUP BY clause

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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