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
225 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 500 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

828 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