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
228 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
[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
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

628 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