Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    36

    Question Unanswered: Return Values on conditions

    Hi all
    I'm trying to return an integer from a query.

    I have a table which has a PK(int), Type(int), DateLastUpdated(DateTime) and some other columns...

    My query is trying to return the PK integer value of the row which has the most recent date in (DateLastUpdated) defined by a certain type.

    QUERY:
    Select TOP 1 Table.PK from Table
    WHERE Table.Type = @Type
    ORDER BY Table.DateLastUpdated DESC

    This query returns the correct result.
    How do I formulate it so that it returns Table.PK as an int
    OR returns -1 if no records are returned(because there are no records with a certain @Type)?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Huh? PK is already an INT in your table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2006
    Posts
    36

    yeah, i Got that

    How do I return -1 if the record doesn't exist?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, TOP is proprietary to Microsoft, so perhaps you might want this thread moved from the SQL forum (SQL is a language) to the Microsoft SQL Server forum?

    in SQL, your query would be:
    Code:
    SELECT PK 
         , Other
         , Columns 
      FROM Table
     WHERE Type = @Type
       AND DateLastUpdated =
           ( SELECT MAX(DateLastUpdated)
               FROM Table
              WHERE Type = @Type )
    regarding returning -1, the query will return NULL if no row satisfies its criteria, so perhaps your application level can detect that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    What about the following:
    Code:
    WITH t AS (SELECT pk, DateLastUpdated
               FROM table WHERE type = @type
               UNION ALL
               VALUES (-1, CAST('0001-01-01' AS date)))
    SELECT t1.pk
    FROM   t AS t1 LEFT OUTER JOIN t AS t2
           ON t1.DateLastUpdated < t2.DateLastUpdated
    WHERE  t2.pk IS NULL
    The CTE t contains only the rows with the selected type.
    The outer join is a self-join of t, which will link none of the rows of t2 to the "max" row of t1.
    This explains the "WHERE t2.pk IS NULL".
    When there is no row in table with type = @type, the row added by the UNION will be returned.
    Last edited by Peter.Vanroose; 05-18-07 at 06:18.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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