CODEWITHSUNDEEP
sql-server
user1531040
user1531040

Reputation: 2291

How to select the value of the first ID in SQL Server stored procedure?

DECLARE @Good INT
 SELECT @Good = Good
   FROM MyTable
  WHERE DateDeleted IS NULL
  GROUP BY ID
 HAVING ID = MIN(ID)

In a stored procedure I wanna set a variable @Good. The conditions I need are that the DateDeleted is null and the minimum ID.

Example:

ID   Good   DateDeleted
-----------------------
1    5      15-12-2013
2    3      null
3    4      null
4    1      null

The result has to be 3

The error message I got is:

Msg 8120, Level 16, State 1, Line 4
Column 'MyTable.Good' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What am I doing wrong?

Can anybody help me?

Thanks

Upvotes: 0

Views: 1573

Answers (4)

huMpty duMpty
huMpty duMpty

Reputation: 14470

DECLARE @Good INT
Select top 1 @Good = Good
From MyTable
Where DateDeleted is null
Order by Id

Fiddle

Upvotes: 3

user3401335
user3401335

Reputation: 2405

a solution could be

select @Good = Good
from MyTable 
where id = (select MIN(ID) from MyTable WHERE DateDeleted IS NULL)

Upvotes: 0

Kevin Hogg
Kevin Hogg

Reputation: 1781

Using the sample data provided we can get the desired value with the following SQL:

DECLARE @Good INT

 SELECT @Good = Good
   FROM MyTable
  WHERE Id =
        (
         SELECT MIN(Id)
           FROM MyTable
          WHERE DateDeleted IS NULL
        )

Upvotes: 0

NePh
NePh

Reputation: 1006

If I understand you correct you want to have the good with the minimum ID which is not deleted.

I think it should look like this:

DECLARE @Good INT

SELECT  @Good = Good
FROM    MyTable
WHERE   ID = ( SELECT   MIN(ID)
               FROM     MyTable
               WHERE    DateDeleted IS NULL
             )

Upvotes: 0

Related Questions