last record recorded - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

last record recorded

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-09-27 : 06:42:42

Hi,

I have the following query,

SELECT gprdsql.TblPracDetails.prac_no,
gprdsql.TblPracDetails.practice_name,
gprdsql.TblContact.title,
gprdsql.TblContact.first_name,
gprdsql.TblContact.surname,
gprdsql.TblContact.role,
gprdsql.TblContact.contact_type,
gprdsql.TblContact.contact_change
FROM gprdsql.TblPracDetails INNER JOIN
gprdsql.TblContact ON gprdsql.TblPracDetails.prac_no = gprdsql.TblContact.prac_no
WHERE (gprdsql.TblPracDetails.prac_state = 'active') AND (gprdsql.TblContact.contact_type = 'collections')

On my table the result iS:
prac_no practice_name Title first_name surname role contact_type contact_change
10 London Hospital Mrs Jade Jules Administrator Collections NULL
10 London Hospital NULL NULL NULL Administrator Collections 21/01/09 13:02:10
10 London Hospital Mrs Yvonne Ruhindo Administrator Collections 10/05/10 12:12:00
16 WestMind Medical Centre NULL NULL NULL Collections NULL 21/01/09 13:02:30
16 WestMind Medical Centre Mrs Alia Omar Receptionist Collections NULL
16 WestMind Medical Centre NULL NULL NULL Collections 01/06/09 10:20:18

I want the result to be:

prac_no practice_name Title first_name surname role contact_type contact_change
10 London Hospital Mrs Yvonne Ruhindo Administrator Collections 10/05/10 12:12:00
16 WestMind Medical Centre NULL NULL NULL Collections 01/06/09 10:20:18

Notice: I Only took the last record from prac_no = 10 and the same for prac_no = 16.

Thanks

Eddie M
Starting Member

14 Posts

Posted - 2011-09-27 : 07:14:33

Do you mean you want the rows that have the highest value in col A?

If so, try this:


Select A, B , C
from tbltest
where (C = 'True') 
and A = (SELECT MAX(A) FROM tbltest)

If I've misunderstood the question, my apologies.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 07:16:56

you need to tell us basis for selecting the output rows as prac_no is same for all in a group and also there's no column to determine the order. Please note that there's no concept of first and last in sql table unless you specify it by means of an ORDER BY

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-09-27 : 07:18:08

Sory I changed the query with more realistic values..

Hope this helps to explain what I want to achieve?

Can I use the RowNum() and determine the last record for each prac_no?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 07:20:07

still my questions stands...how do you define last here? ie. on which column basis?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-09-27 : 07:23:25

Ok, Yes, the last is the last added record for that prac_no..

Then based on the Prac_no ..

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 07:38:01

lastly added? how do you find that?
As told multiple times before, there's no concept of first and last so unless you've an identity field or audit date field order of insertion cant be tracked

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-09-27 : 07:50:30

Ok - added the last field of date - gprdsql.TblContact.contact_change (when a new change has been added) - Please review the #1 which I have updated with this and the results too..

Now, is it possible to get the last date and display the record.. many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-27 : 11:33:19

[code]
IF OBJECT_ID('tempdb..#temp) IS NOT NULL
DROP TABLE #temp
SELECT gprdsql.TblPracDetails.prac_no,
gprdsql.TblPracDetails.practice_name,
gprdsql.TblContact.title,
gprdsql.TblContact.first_name,
gprdsql.TblContact.surname,
gprdsql.TblContact.role,
gprdsql.TblContact.contact_type,
gprdsql.TblContact.contact_change INTO #Temp
FROM gprdsql.TblPracDetails INNER JOIN
gprdsql.TblContact ON gprdsql.TblPracDetails.prac_no = gprdsql.TblContact.prac_no
WHERE (gprdsql.TblPracDetails.prac_state = 'active') AND (gprdsql.TblContact.contact_type = 'collections')

SELECT t.*
FROM #temp t
INNER JOIN (SELECT prac_no,MAX(contact_change) AS latest
FROM #Temp
GROUP BY prac_no)t1
ON t1.latest=t.contact_change
AND t1.prac_no = t.prac_no
[/code]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-27 : 12:01:18

Here is another way, untested due to lack of sample data:

SELECT
    <Column list>
FROM
(    
    SELECT gprdsql.TblPracDetails.prac_no, 
    gprdsql.TblPracDetails.practice_name,
    gprdsql.TblContact.title, 
    gprdsql.TblContact.first_name, 
    gprdsql.TblContact.surname, 
    gprdsql.TblContact.role, 
    gprdsql.TblContact.contact_type,
    gprdsql.TblContact.contact_change,
    ROW_NUMBER() OVER (PARTITION BY gprdsql.TblPracDetails.prac_no ORDER BY COALESCE(gprdsql.TblContact.contact_change, '19000101') DESC) AS RowNum
    FROM gprdsql.TblPracDetails INNER JOIN
    gprdsql.TblContact ON gprdsql.TblPracDetails.prac_no = gprdsql.TblContact.prac_no
    WHERE (gprdsql.TblPracDetails.prac_state = 'active') AND (gprdsql.TblContact.contact_type = 'collections') 
) AS T
WHERE RowNum = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources