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
223 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

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

Title # Comments Views Activity
SQL Query to display duplicates ? 6 38
CROSS APPLY 4 44
Can Unique column have more than one Null? 8 45
SQL Help - SELECT Statement 6 39
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
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, f…

911 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

22 Experts available now in Live!

Get 1:1 Help Now