Return Values on conditions

grooverinthesouth · #1 (**permalink**) 05-10-07, 10:14

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)?

blindman · #2 (**permalink**) 05-10-07, 12:09

Huh? PK is already an INT in your table.

grooverinthesouth · #3 (**permalink**) 05-10-07, 12:21

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

r937 · #4 (**permalink**) 05-10-07, 13:37

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

Peter.Vanroose · #5 (**permalink**) 05-18-07, 05:13

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech