CODEWITHSUNDEEP
sqlsql-server-2008
Gross
Gross

Reputation: 17

find min/max of date fields

I have a database with multiple dates for the same unique ID. I am trying to find the first (min) and last (max) date for each unique ID. I want the result to be: Unique ID, first Date, last date, field1, field2,field3

Select  max(date_Executed) as Last_Date,
    (select unique_ID, MIN(date_Executed)as First_Date
from Table_X
group by unique_ID, Field1, field2,field3
)
from Table_X
where unique_ID = unique_ID
group by unique_ID, Field1, field2,field3
order by unique_ID,First_Permit_Date

The error message I get is:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Msg 207, Level 16, State 1, Line 19 Invalid column name 'First_Permit_Date'.

I’m new to SQL… Thanks for your help-

Upvotes: 1

Views: 141

Answers (2)

emmics
emmics

Reputation: 1063

Shouldn't it just be something like this? I have no further insight in your tables, but the second 'select' statement seems to cause the error.

SELECT unique_ID, min(date_Executed) as First_Date, max(date_Executed) as Last_Date, field1, field2, field3

FROM Table_X
GROUP BY unique_ID, Field1, field2, field3
order by unique_ID, First_Permit_Date

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

why not a simple select with group by 

Select  max(date_Executed) as Last_Date, MIN(date_Executed) as First_Date
from Table_X
group by unique_ID, Field1, field2,field3
order by unique_ID,First_Permit_Date

You can use more then one aggregation function ina select .. (with the same group by clause)

Upvotes: 3

Related Questions