Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Course Of The Month
8 days, 6 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
I need more columns in my SELECT portion than are contained in my GROUP BY
Posted on 2013-11-05
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
Active today
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.
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.
I am reading records from one table (input table-PatientDataArr20132)
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
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
Active 2 days ago
SELECT
*, MIN(admdate) OVER(PARTITION BY providerhs, linkno) AS MinAdmitDate
--INTO <new_table_name>
FROM PatientDataArr20132
*, MIN(admdate) OVER(PARTITION BY providerhs, linkno) AS MinAdmitDate
--INTO <new_table_name>
FROM PatientDataArr20132
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.
Jim, thank you also. Your article was very helpful, I will reference it in the future.
Featured Post
Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
by Mark Wills
Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server... So if you are the accidental DBA, or, simp…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data.
I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 6 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.