One query or procedure - 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

SQL Server 2008 Forums

Transact-SQL (2008)

One query or procedure

Author

Topic

Bertin
Starting Member

7 Posts

Posted - 2011-04-01 : 14:53:35

I hava a table appointments with columns: id, datetime, client_id and remarks

So everytime a client has an appointment one record is created.

Can I create a query that returns me only the last record of each client or do I have to write some procedure that selects the appointsments of 1 client and then gets the latest appointment.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-01 : 15:00:20

what do you mean by latest? what is you entered two appointments for Larry
1. one for April 12, 2011
2. one for April 22, 2011

the latest appointment is April 22, 2011

what do you mean by latest? latest entered or latest appointment date?

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-01 : 15:06:45

[code]
DECLARE @appointments TABLE(id INT IDENTITY(1,1), appointment_date datetime, client_id INT, remarks NVARCHAR(255))

INSERT INTO @appointments
( appointment_date ,
client_id ,
remarks
)
SELECT GETDATE(),
1,
'bad teeth'
UNION
SELECT GETDATE() + 3,
1,
'needs new gallbladder'
UNION
SELECT GETDATE(),
2,
'a'

UNION
SELECT GETDATE(),
3,
'a'

SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY client_id, appointment_date ORDER BY appointment_date DESC) latest
FROM @appointments ) latests
WHERE latest = 1

[/code]

If you don't have the passion to help people, you have no passion

Bertin
Starting Member

7 Posts

Posted - 2011-04-01 : 15:40:12

1. one for April 12, 2011
2. one for April 22, 2011

the latest appointment is April 22, 2011

what do you mean by latest? latest entered or latest appointment date?

With latest I mean latest date, so in the example you gave April 22, 2011

Bertin
Starting Member

7 Posts

Posted - 2011-04-01 : 15:43:45

[quote]Originally posted by yosiasz


       
SELECT * FROM 
(       
SELECT *, ROW_NUMBER() OVER(PARTITION BY client_id, appointment_date ORDER BY appointment_date DESC) latest
 FROM @appointments           ) latests
 WHERE latest = 1

This construction is new for me. Have to read about and test it before I understand what it does. Thanks for the quick reply

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-01 : 18:14:23

[code]
select client_id, max(appoitment_date)
from appoitments a
group by client_id
[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Bertin
Starting Member

7 Posts

Posted - 2011-04-04 : 06:07:16

[quote]Originally posted by Bertin

[quote]Originally posted by yosiasz


       
SELECT * FROM 
(       
SELECT *, ROW_NUMBER() OVER(PARTITION BY client_id, appointment_date ORDER BY appointment_date DESC) latest
 FROM @appointments           ) latests
 WHERE latest = 1

Works perfect, thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources