sub-query to scalar

[Luzian]

how do I:

INSERT INTO Modifications
(
	type
)
VALUES
(
	(
		SELECT [red]FirstRow(id)[/red]
		FROM ModificationTypes_ENUM
		WHERE name = 'create'
	)
)

I want to select the "id" column from the first row I get.

 

[JarlH]

INSERT INTO Modifications
(
type
)
SELECT MIN(id)
FROM ModificationTypes_ENUM
WHERE name = 'create'

 

[unclejimbob]

Do you want:

a) the ID from the first row returned in the sub query
or
b) the minimum ("first") id from the ModificationTypes_ENUM
table ?

min() will give you b) however to get a) I think you will have to resort to whatever functions come with your DB because off hand I can't think of a set-based function in ANSI SQL that will do this (unless you can uniquely identify the required row in some way).

Example for SQL Server 2005 AdventureWorks database to illustrate the point:

select top 1 CreditCardID from Sales.ContactCreditCard
- gives you the CreditCardID value from the first row in the result set

select min(CreditCardID) from Sales.ContactCreditCard
- gives you the first CreditCardID in the table

Any further thoughts anyone ?

ujb