[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

I need more columns in my SELECT portion than are contained in my GROUP BY

Below is a query I created that works ok. I need the MINIMUM admdate by provider by linkno (I.E. the first admit for each patient in a hospital or health system)

select min(admdate) as MinAdmitDate, providerhs, linkno from PatientDataArr20132
group by providerhs, linkno
union
select min(admdate) as MinAdmitDate, providerhs, linkno from PatientDataArr20131
group by providerhs, linkno
order by providerhs, linkno, MinAdmitDate

What I need to do now is add all the columns to the query so that I can build a new table of the same structure but with one extra column. I ‘m sure I need an embedded select statement but I just can’t place it correctly. Can someone please help? .

PatientDataArr20131 is the input table
EventDateArr20131 is the output table, same as above but with one additional column

I have attached a screenshot with the structures of the two tables.
0
JamesDBuskirk
Asked:
JamesDBuskirk
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No screenshot.  Also not sure what you mean by input and output table.

In case it helps, I have an article on there on SQL Server GROUP BY Solutions, and if you scroll down to point #5 you'll see an example of how to JOIN tables with an aggregate like MIN() from the inner table.
0
 
JamesDBuskirkAuthor Commented:
I am reading records from one table (input table-PatientDataArr20132) in the query shown above, then I need to insert these records into an almost identical table with one added column (the output table-EventDateArr20132).

I plan to do the inserting with a reader loop, but I need to pull every column from the input table.

I re-attached the JPG screenshot. I did the attach but never clicked UPLOAD, sorry.
sql.jpg
0
 
JamesDBuskirkAuthor Commented:
It would appear your article has what I need, let me work with it for a bit.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
SELECT
    *, MIN(admdate) OVER(PARTITION BY providerhs, linkno) AS MinAdmitDate
--INTO <new_table_name>
FROM PatientDataArr20132
0
 
JamesDBuskirkAuthor Commented:
Thank you very much Scott, that worked perfectly. I am not familiar with the OVER or PARTITION commands, I need to get back into class...

Jim, thank you also. Your article was very helpful, I will reference it in the future.
0
 
Scott PletcherSenior DBACommented:
Yeah, the new windowing functions are great.  But they take time to get used to using ... I'm still working on that part myself :-) .
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now