Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to create a view of one table with unique rows with the maximum values on two columns

Posted on 2013-01-17
6
Medium Priority
?
230 Views
Last Modified: 2013-01-18
Hi gurus!

I have a very simple problem I am not able figure out on my own. Perhaps some of you can see where my thinking goes wrong.

Real life situation: My coworkers are filling out forms that describe our cases. Usually only one form for each case is filled. However, sometimes the situation changes and a new form is filled, and this information 'replaces' the old information.  I need to make a list of the date of the last completed form for each case.  

I have a view that puts together a list of ALL completed forms (vis_ArkivsakID_for_fullførte_klageskjema). From this I want to select the rows that have the last form for each distinct case. There are two different columns that determine the last form:
1) the  date of completing the form (FullførtSkjema_Dato)
2) ResponseID, which is needed if there are more than one form filled out on the last date.


As you can see I have tried to do two nested select statements. If I keep these separate, they work, but I get too many cases. Therefore, I think that nesting them by first selecting the forms with max date within each case, and then within each date the max ResponseID.

CREATE VIEW [dbo].[vis_ArkivsakID_for_sist_fullført_klageskjema]
AS
SELECT    
    [Sas_ArkivSakID]
      ,FullførtSkjema_Dato
      ,MAX(ResponseID) AS ResponseID
FROM(SELECT [Sas_ArkivSakID]
      ,MAX([FullførtSkjema_Dato]) AS FullførtSkjema_Dato
      ,ResponseID
  FROM [SelectSurveyNet].[dbo].[vis_ArkivsakID_for_fullførte_klageskjema]
  GROUP BY Sas_ArkivSakID,  ResponseID ) 
GROUP BY Sas_ArkivSakID,  FullførtSkjema_Dato

Open in new window


If I run only the SELECT part of this code, I get following error message:
Incorrect syntax near the keyword 'GROUP'

I hope some of you are smarter than me!

Tangofil
0
Comment
Question by:tangofil
6 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 38788277
Need to add value for the select group just add a value

CREATE VIEW [dbo].[vis_ArkivsakID_for_sist_fullført_klageskjema]
AS
SELECT    
    [Sas_ArkivSakID]
      ,FullførtSkjema_Dato
      ,MAX(ResponseID) AS ResponseID
FROM(SELECT [Sas_ArkivSakID]
      ,MAX([FullførtSkjema_Dato]) AS FullførtSkjema_Dato
      ,ResponseID
  FROM [SelectSurveyNet].[dbo].[vis_ArkivsakID_for_fullførte_klageskjema]
  GROUP BY Sas_ArkivSakID,  ResponseID )  GROUP1
GROUP BY Sas_ArkivSakID,  FullførtSkjema_Dato
0
 
LVL 11

Expert Comment

by:Simone B
ID: 38788310
When using nested queries in this fashion, you need an alias for the subquery:

CREATE VIEW [dbo].[vis_ArkivsakID_for_sist_fullført_klageskjema]
AS
SELECT    
    [Sas_ArkivSakID]
      ,FullførtSkjema_Dato
      ,MAX(ResponseID) AS ResponseID
FROM(SELECT [Sas_ArkivSakID]
      ,MAX([FullførtSkjema_Dato]) AS FullførtSkjema_Dato
      ,ResponseID
  FROM [SelectSurveyNet].[dbo].[vis_ArkivsakID_for_fullførte_klageskjema]
  GROUP BY Sas_ArkivSakID,  ResponseID
   ) X
GROUP BY Sas_ArkivSakID,  FullførtSkjema_Dato

Open in new window

0
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 38788688
select [Sas_ArkivSakID], FullførtSkjema_Dato, ResponseID from
(select [Sas_ArkivSakID], FullførtSkjema_Dato, ResponseID,
 row_number() over (partition by [Sas_ArkivSakID] order by FullførtSkjema_Dato,ResponseID) as rn
 from vis_ArkivsakID_for_fullførte_klageskjema) as x
where rn = 1;
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Closing Comment

by:tangofil
ID: 38792248
This gives the correct answer and works like a charm. The two first solutions work, but do not give the correct result as they both did include
double forms. For example:

/*** Sas_ArkivSakID, FullførtSkjema_Dato, ResponseID ***/
/*** 2008001075       14.04.2010                 1765 ***/
/*** 2008001075       16.12.2008                 761 ***/

Whereas the correct solution does include only the first one of these lines.
Thank you!
0
 

Author Comment

by:tangofil
ID: 38792381
I just noticed that I had to add DESC to the solution to change the ORDER BY, so that it picks the last ResponceID and not the first one.
0
 
LVL 32

Expert Comment

by:awking00
ID: 38793609
I knew you wanted the earliest date, but wasn't sure about the responseid and I meant to ask, but I'm glad you figured that part out :-)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

824 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