October 30, 2007 at 12:51 pm
I have a query similar to this...
Select field1, MAX(SomeDateField) AS XDATE
From MyStuff SomeJoins...
Where SomeDateField IS NOT NULL
Group By field1
It returns some stuff like this...
field1 XDATE
ABCD 1900-12-31 00:00:00:000
DEFG 1900-12-30 00:00:00:000
HIJK 1900-12-29 00:00:00:000
and so on....
I want the output to go into a table with the following columns...where VIOLATOR is varchar(25) and INCIDENTDATE is DateTime.
VIOLATOR INCIDENTDATE
ABCD 1900-12-31 00:00:00:000
DEFG 1900-12-30 00:00:00:000
HIJK 1900-12-29 00:00:00:000
Thanks
October 30, 2007 at 1:28 pm
I'm not sure I understand your question - are you just looking to create a table for your select statement? i.e.
create table #BLAH
(VIOLATOR varchar(25),INCIDENTDATE datetime)
insert into #BLAH
select VIOLATOR, max(INCIDENTDATE) AS INCIDENTDATE
from SOMETABLE
group by VIOLATOR
select *
from #BLAH
DROP TABLE #BLAH
Tommy
Follow @sqlscribeOctober 30, 2007 at 1:29 pm
From BOL
The SELECT subquery in the INSERT statement can be used to add values into a table from one or more other tables or views. Using a SELECT subquery also lets more than one row be inserted at one time.
This INSERT statement inserts into a separate table some of the data from all the rows in titles whose type is modern cooking:
USE pubs
INSERT INTO MyBooks
SELECT title_id, title, type
FROM titles
WHERE type = 'mod_cook'
The select list of the subquery must match the column list of the INSERT statement. If no column list is specified, the select list must match the columns in the table or view being inserted into
October 30, 2007 at 2:57 pm
Perfect!
Thanks everyone.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply