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 2012 Forums
 Transact-SQL (2012)
 select from two table

Author  Topic 

Aleks
Starting Member

3 Posts

Posted - 2013-01-19 : 05:57:21
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-19 : 06:13:09
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 - 2013-01-19 : 08:43:11
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-19 : 12:06:31
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 - 2013-01-19 : 12:36:33
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-19 : 15:01:09
You are very welcome - glad it helped.
Go to Top of Page
   

- Advertisement -