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
224 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to join on ID, with prefix? 15 60
Help  needed 3 26
How can I update a 2nd table with what is inserted into the 1st? 5 38
T-SQL Query to include null values 3 33
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 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