get latest DATE - 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

get latest DATE

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-08-07 : 18:13:34

How do you get firstname, lastname for the latest DATE?
Thanks.

create table computert1 (id int identity (1,1), firstname varchar(20), lastname varchar(20))
insert computert1 (firstname, lastname)
select 'daniel', 'wade' union all
select 'joe', 'walker' union all
select 'daniel', 'serious' union all
select 'sam', 'jordan'

create table computert2 (id int identity (1,1), dates datetime)
insert computert2 (dates)
select getdate() union all
select '2008-10-01' union all
select '2005-10-01'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-07 : 18:20:41

There is no relation between the two tables.

No, you're never too old to Yak'n'Roll if you're too young to die.

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-08-07 : 18:21:57

ID is the relation.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-07 : 19:48:21

ID is an identity column in both tables. How can you ensure the ID of same value is related ?

KH
[spoiler]Time is always against us[/spoiler]

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-08-08 : 13:17:21

Based on your requirements , you'll either need to create a another column or a joining table .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2010-08-08 : 14:10:28

ID is the key to join the table.

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-09 : 01:48:09

if it was not a identity column it is just a int or varchar column or any thing. then how can we get the result by using the join

With Regards
Kashyap M

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-09 : 04:11:18

You can use below -


SELECT FirstName, LastName FROM Computert1 c1
INNER JOIN
	(
		SELECT ID, ROW_NUMBER() OVER( PARTITION BY 1 ORDER BY Dates DESC ) RowNumber FROM Computert2
	) A
ON C1.ID = A.ID AND RowNumber = 1

Note that If your program is Inserting the data in both the tables at the same time
then identity column will be same then only this can be used.
If you can change the database design then dont use identity column in dates table.
Get the identity column value with Scope_Identity() function and insert in the second table with that ID.
and use foreign key constraint for referential integrity.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

kashyap_sql
Posting Yak Master

174 Posts

Posted - 2010-08-09 : 07:28:45

ya it works

With Regards
Kashyap M

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-09 : 12:56:33

Another way:

SELECT 
	FirstName, 
	LastName 
FROM 
	Computert1 c1
INNER JOIN
	(
		SELECT TOP 1 ID
		FROM Computert2
		ORDER BY Dates DESC 
	) A
	ON c1.ID = A.ID

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-09 : 13:59:20

quote:
Originally posted by basicconfiguration

ID is the key to join the table.

No, it is not

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources