select top - 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

select top

Author

Topic

mana
Posting Yak Master

102 Posts

Posted - 2014-09-13 : 17:24:24

hello

i have the following code and i want to choose the top vonaddresse from dbo.v_ME_Montage_Adressen but the result is wrong.
in fact for each teil nummer there are different von addresse but i just need the first one in the final table (dbo.Bewegungen_Inventur_Korrekturen)
can you help me pleASEEE??

update dbo.Bewegungen_Inventur_Korrekturen
set Von_Addresse = F.vonaddresse
FROM dbo.Bewegungen_Inventur_Korrekturen M
inner join (select top 1 Von_Adresse vonaddresse,Teil_Nummer
from dbo.v_ME_Montage_Adressen ) F
ON
F.Teil_Nummer = M.Teil_Nummer;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-13 : 18:38:09

You nerd to specify a sort order in the subquery

mana
Posting Yak Master

102 Posts

Posted - 2014-09-13 : 19:03:14

thank you
do you know how i have to write this because i tried it out but it didn't work

quote:
Originally posted by mana

hello

i have the following code and i want to choose the top vonaddresse from dbo.v_ME_Montage_Adressen but the result is wrong.
in fact for each teil nummer there are different von addresse but i just need the first one in the final table (dbo.Bewegungen_Inventur_Korrekturen)
can you help me pleASEEE??

update dbo.Bewegungen_Inventur_Korrekturen
set Von_Addresse = F.vonaddresse
FROM dbo.Bewegungen_Inventur_Korrekturen M
inner join (select top 1 Von_Adresse vonaddresse,Teil_Nummer
from dbo.v_ME_Montage_Adressen ) F
ON
F.Teil_Nummer = M.Teil_Nummer;

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-13 : 19:06:15

Before the right parentheses put ORDER BY followed by the column name you want.

mana
Posting Yak Master

102 Posts

Posted - 2014-09-14 : 03:46:13

hello

i wrote the following code but it doesn't work

update dbo.Bewegungen_Inventur_Korrekturen
set Von_Addresse = F.Von_Adresse
FROM dbo.Bewegungen_Inventur_Korrekturen M
inner join (select top 1 Von_Adresse ,Teil_Nummer
from dbo.v_ME_Montage_Adressen order by Teil_Nummer ) F
ON
F.Teil_Nummer = M.Teil_Nummer;

quote:
Originally posted by mana

thank you
do you know how i have to write this because i tried it out but it didn't work

quote:
Originally posted by mana

hello

i have the following code and i want to choose the top vonaddresse from dbo.v_ME_Montage_Adressen but the result is wrong.
in fact for each teil nummer there are different von addresse but i just need the first one in the final table (dbo.Bewegungen_Inventur_Korrekturen)
can you help me pleASEEE??

update dbo.Bewegungen_Inventur_Korrekturen
set Von_Addresse = F.vonaddresse
FROM dbo.Bewegungen_Inventur_Korrekturen M
inner join (select top 1 Von_Adresse vonaddresse,Teil_Nummer
from dbo.v_ME_Montage_Adressen ) F
ON
F.Teil_Nummer = M.Teil_Nummer;

mana
Posting Yak Master

102 Posts

Posted - 2014-09-14 : 03:46:14

quote:
Originally posted by mana

thank you
do you know how i have to write this because i tried it out but it didn't work

quote:
Originally posted by mana

hello

i have the following code and i want to choose the top vonaddresse from dbo.v_ME_Montage_Adressen but the result is wrong.
in fact for each teil nummer there are different von addresse but i just need the first one in the final table (dbo.Bewegungen_Inventur_Korrekturen)
can you help me pleASEEE??

update dbo.Bewegungen_Inventur_Korrekturen
set Von_Addresse = F.vonaddresse
FROM dbo.Bewegungen_Inventur_Korrekturen M
inner join (select top 1 Von_Adresse vonaddresse,Teil_Nummer
from dbo.v_ME_Montage_Adressen ) F
ON
F.Teil_Nummer = M.Teil_Nummer;

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-14 : 06:45:20

[code]update M
set Von_Addresse = F.Von_Adresse
FROM dbo.Bewegungen_Inventur_Korrekturen M
Cross apply
(select top 1 Von_Adresse F
from dbo.v_ME_Montage_Adressen
Where F.Teil_Nummer = M.Teil_Nummer
order by Teil_Nummer ) F;[/code]Although the query above will run without errors, I don't think it is logically correct. Try order by Von_Adresse.

mana
Posting Yak Master

102 Posts

Posted - 2014-09-15 : 04:06:07

hello thank you forthe response
i wrote below query but i have the same von_addresse for all of teil nummer. can you help me please? i don't know where the problem is.

update M
set Von_Addresse = F.Von_Adresse
FROM dbo.Bewegungen_Inventur_Korrekturen M
Cross apply
(select top 1 Von_Adresse F
from dbo.v_ME_Montage_Adressen
Where F.Teil_Nummer = M.Teil_Nummer
order by Teil_Nummer ) F;

quote:
Originally posted by James K

update M
set Von_Addresse = F.Von_Adresse
FROM dbo.Bewegungen_Inventur_Korrekturen M
Cross apply
(select top 1 Von_Adresse F
from dbo.v_ME_Montage_Adressen
Where F.Teil_Nummer = M.Teil_Nummer
order by Teil_Nummer ) F;
Although the query above will run without errors, I don't think it is logically correct. Try order by Von_Adresse.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-15 : 10:02:22

"order by Teil_Nummer" is wrong. Let me ask:

If there are three addresses for some Nummer, which one do you want the subquery to return? How do you choose between the three?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources