General SQL - GROUP BY

[cope]

To condense the select query I am trying to run:

A person has/can have many referrals. I am trying to display the person's most recent referral using the max() function and the corresponding referral reason. The former isn't a problem, select Person ID and max(referral date), and group by person ID.

However, I cannot display the most recent referral WITH the most recent referral's reason. The group by obviously displays both records for the unique person with the two referral reasons, rather than just one for the person. Is there a simlpe solution for this?

 

[plog]

Define simple. With the query you have created, you are halfway there. You need to use that as a sub-query back into your table.

Bring in the table and then that query. Link them by Person ID and then link MAX(referral date) to [referral date]. From the table, bring down [Person ID], [referal date] and [referral reason] fields. Run that and you have your results.

One thing to look out for though is if a person has multiple referrals on the same day--then your getting both results for them.

 

[cope]

The query is actually from Business Obj Web Intelligence. It still needs work but the process is going to be similar if not the same as that above. Had this been in Access I think I would have this by now, but such are the differences between the interfaces and the options (and the sql) ...

 

[namliam]

You can apply most anny access solution to most any database....

If you are using Oracle or SQL Server you may be intrested in the "Partition by"
Oracle: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm
SQL Server: http://technet.microsoft.com/en-us/library/ms186734.aspx

Which will allow you do to this in one query instead of two, instead of row_number there are different options like Last (Last_value) or first and some others too

 

[cope]

Thanks for the suggestion. I have read in to using ROW_NUMBER() (T-SQL) and it may well be the way to go.

I am as far as identifying multiple records based on the key field which works perfectly, but when trying include a WHERE clause, the column name is not recognised. SQL reads:

SELECT
person.IntegID,
person.Surname + ', ' + person.Forename,
stepareferral.StartDate,
ROW_NUMBER() OVER(PARTITION BY person.integID ORDER BY stepareferral.StartDate DESC) AS Row
FROM
(
select * from icsrefer with(nolock)
) stepareferral RIGHT OUTER JOIN (
select * from isperson with(nolock)
) person ON (stepareferral.PersonID=person.PersonID)

WHERE
Row = 1

Any suggestions?

 

[namliam]

Yeah you need to make it a subselect ...
Select *
From (
YourQuery
) SomeName
Where Row = 1

I personaly write my queries more like:

From             Somethign
Inner       Join anotherthing on x = y
Right outer join thatThing    on a = b

Just to try and keep things readable and thus (more) maintainable.

and fyi, use code blocks instead of quote blocks like you did to post code.

 

[cope]

Sorry to drag this out further, If you could ellaborate on the subselect it would be of much assistance (not used a subselect before, although I have read up and bookmarked it since your last post).

I have applied it to the person's id in the WHERE clause, the error generated is focused on something to do with the query name (List3).

WHERE
   stepareferral.StartDate  BETWEEN DATEADD (m, -3, GETDATE())   AND  GETDATE()
AND
   person.IntegID in
          (Select person.IntegID FROM List3 WHERE Row = 1)

P.S I posted a quick response earlier on which the quote tags are available and the code tags are not. Will use the correct tags in future.

 

[namliam]

Code tags simply by typing [cade] and [/cade] (replacing a for o) are always available

Your query with subselect would be ssomething like:

Select * 
From ( 
      SELECT
              person.IntegID,
              person.Surname + ', ' + person.Forename,
              stepareferral.StartDate,
              ROW_NUMBER() OVER(PARTITION BY person.integID ORDER BY stepareferral.StartDate DESC) AS Row
      FROM             ( select * from icsrefer with(nolock) ) stepareferral 
      RIGHT OUTER JOIN ( select * from isperson with(nolock) ) person         ON (stepareferral.PersonID=person.PersonID)
     )
WHERE
Row = 1

 

[cope]

Haven't been successful in getting this to function correctly. Instead I have implemented a work around using a filter to include only records with the value 1 in the 'Row' column. Still, I am much further ahead than I was this time on Monday so thank you for the help.

 

[cope]

SELECT
[INDENT]--person fields
--referral fields (including referral.StartDate)
[/INDENT]FROM
[INDENT]--tables and joins
[/INDENT]WHERE
[INDENT]--referral criteria
AND
(referral.StartDate =
[INDENT](
SELECT
[INDENT]MAX(referral.StartDate)
[/INDENT]FROM
[INDENT]--referral table
[/INDENT]WHERE
[INDENT]--referral table primary key = person table primary key
[/INDENT])
[/INDENT])
[/INDENT]

For anyone interested, that is a very vague template for the solution. As already mentioned, if there are >1 referrals on the same day, you may need to go down to MAX(PrimaryKey) level.
​