Solved

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

Posted on 2013-11-05
6
284 Views
Last Modified: 2013-11-06
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
Comment
Question by:JamesDBuskirk
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39625083
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
 

Author Comment

by:JamesDBuskirk
ID: 39625162
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
 

Author Comment

by:JamesDBuskirk
ID: 39625316
It would appear your article has what I need, let me work with it for a bit.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 39625640
SELECT
    *, MIN(admdate) OVER(PARTITION BY providerhs, linkno) AS MinAdmitDate
--INTO <new_table_name>
FROM PatientDataArr20132
0
 

Author Closing Comment

by:JamesDBuskirk
ID: 39626311
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39627467
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now