SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 select from two table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Aleks
Starting Member

3 Posts

Posted - 01/19/2013 :  05:57:21  Show Profile  Reply with Quote
Hy,i am new here and i will be grateful if you help me with my problem.
I have two table:
first: id name age second: id order_number km min
1 alex 15 1 1 2 3
2 john 17 1 2 3 4
1 3 4 5
1 4 5 6
2 1 2 3
2 2 3 4
2 3 4 5
2 4 5 6

I must do select with join to look like this:
name age km min where order_number is 4(or max num.)

thanks in advance

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 01/19/2013 :  06:13:09  Show Profile  Reply with Quote
What are those additional four columns in the first table? Do they need to be used in some way? What is the output you would expect to see for the sample data you posted?
Go to Top of Page

Aleks
Starting Member

3 Posts

Posted - 01/19/2013 :  08:43:11  Show Profile  Reply with Quote
sorry I didn't watch the editor so the data are set to be understandably,i explain again.
In first table is columns "id,name,age" with values in first row:"1,alex,15", sec. row :"2,john,17
In second table is columns "id,order_number,km,min" with values in first row:"1,1,2,3" sec.row "1,2,3,4" third row:"1,3,4,5"
four row:"2,1,2,3" ,fifth row:"2,2,3,4",sixth row:"2,3,4,5".
So i wont with query get :name,age(from 1.T) and km,min(from 2.T but where order_number is the top number).
The tables are conected with "id" columns
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 01/19/2013 :  12:06:31  Show Profile  Reply with Quote
Can you try this?
SELECT
	t1.name, t1.age, t2.km, t2.min
FROM
	Table1 t1
	CROSS APPLY
	(
		SELECT TOP (1) km, min
		FROM Table2 t2
		WHERE t2.id = t1.id
		ORDER BY order_number DESC
	) t2
Go to Top of Page

Aleks
Starting Member

3 Posts

Posted - 01/19/2013 :  12:36:33  Show Profile  Reply with Quote
Thank you very much,it's work for me,i am very grateful for your time and knowledge
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 01/19/2013 :  15:01:09  Show Profile  Reply with Quote
You are very welcome - glad it helped.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000