[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Problem with sorting in Full Outer Join table

MacChojnacki@googlemail.com

3/15/2007 1:55:00 PM

Hello,
I stucked on strange problem with data order in dataset.
Any clue or suggests will be appreciated.
Let's start from the beggining. We have to tables:

Table1:
Import Nr
18
19
20
21
22
23
24
25
26
27
28
29
32
33
34

Table2:
Import Nr
20
21
23
25
26
28
29
32
34
36
37
39

This is my result which doesn't statisfy me:
Import Nr ImportNr2
18
19
20 20
21 21
22
23 23
24
25 25
26 26
27
28 28
29 29
33
34 34
32
36
39
37

The Code:
SELECT TOP 100 PERCENT dbo.temp1.[Import Nr], dbo.temp2.[Import
Nr] AS Expr1
FROM dbo.temp1 FULL OUTER JOIN
dbo.temp2 ON dbo.temp1.[Import Nr] = dbo.temp2.
[Import Nr]

As you can see there are some problems with sorting in second column.
I would like to get the result like below:
Import Nr ImportNr2
18
19
20 20
21 21
22
23 23
24
25 25
26 26
27
28 28
29 29
32
33
34 34
36
37
39
4 Answers

Uri Dimant

3/15/2007 2:01:00 PM

0

At the first glance you don't use ORDER BY clause, have you try it?
Also why you are using TOP 100 PERCENT ? What does it give you?




<MacChojnacki@googlemail.com> wrote in message
news:1173966901.837580.61880@y66g2000hsf.googlegroups.com...
> Hello,
> I stucked on strange problem with data order in dataset.
> Any clue or suggests will be appreciated.
> Let's start from the beggining. We have to tables:
>
> Table1:
> Import Nr
> 18
> 19
> 20
> 21
> 22
> 23
> 24
> 25
> 26
> 27
> 28
> 29
> 32
> 33
> 34
>
> Table2:
> Import Nr
> 20
> 21
> 23
> 25
> 26
> 28
> 29
> 32
> 34
> 36
> 37
> 39
>
> This is my result which doesn't statisfy me:
> Import Nr ImportNr2
> 18
> 19
> 20 20
> 21 21
> 22
> 23 23
> 24
> 25 25
> 26 26
> 27
> 28 28
> 29 29
> 33
> 34 34
> 32
> 36
> 39
> 37
>
> The Code:
> SELECT TOP 100 PERCENT dbo.temp1.[Import Nr], dbo.temp2.[Import
> Nr] AS Expr1
> FROM dbo.temp1 FULL OUTER JOIN
> dbo.temp2 ON dbo.temp1.[Import Nr] = dbo.temp2.
> [Import Nr]
>
> As you can see there are some problems with sorting in second column.
> I would like to get the result like below:
> Import Nr ImportNr2
> 18
> 19
> 20 20
> 21 21
> 22
> 23 23
> 24
> 25 25
> 26 26
> 27
> 28 28
> 29 29
> 32
> 33
> 34 34
> 36
> 37
> 39
>


Russell Fields

3/15/2007 2:02:00 PM

0

You need an ORDER BY statement, of course.

Try: ORDER BY ISNULL(temp1.ImportNr, temp2.ImportNr)

RLF
<MacChojnacki@googlemail.com> wrote in message
news:1173966901.837580.61880@y66g2000hsf.googlegroups.com...
> Hello,
> I stucked on strange problem with data order in dataset.
> Any clue or suggests will be appreciated.
> Let's start from the beggining. We have to tables:
>
> Table1:
> Import Nr
> 18
> 19
> 20
> 21
> 22
> 23
> 24
> 25
> 26
> 27
> 28
> 29
> 32
> 33
> 34
>
> Table2:
> Import Nr
> 20
> 21
> 23
> 25
> 26
> 28
> 29
> 32
> 34
> 36
> 37
> 39
>
> This is my result which doesn't statisfy me:
> Import Nr ImportNr2
> 18
> 19
> 20 20
> 21 21
> 22
> 23 23
> 24
> 25 25
> 26 26
> 27
> 28 28
> 29 29
> 33
> 34 34
> 32
> 36
> 39
> 37
>
> The Code:
> SELECT TOP 100 PERCENT dbo.temp1.[Import Nr], dbo.temp2.[Import
> Nr] AS Expr1
> FROM dbo.temp1 FULL OUTER JOIN
> dbo.temp2 ON dbo.temp1.[Import Nr] = dbo.temp2.
> [Import Nr]
>
> As you can see there are some problems with sorting in second column.
> I would like to get the result like below:
> Import Nr ImportNr2
> 18
> 19
> 20 20
> 21 21
> 22
> 23 23
> 24
> 25 25
> 26 26
> 27
> 28 28
> 29 29
> 32
> 33
> 34 34
> 36
> 37
> 39
>


MacChojnacki@googlemail.com

3/15/2007 2:14:00 PM

0

On 15 Mar, 15:01, "Uri Dimant" <u...@iscar.co.il> wrote:
> At the first glance you don't use ORDER BY clause, have you try it?
> Also why you are using TOP 100 PERCENT ? What does it give you?
>
> <MacChojna...@googlemail.com> wrote in message
>
> news:1173966901.837580.61880@y66g2000hsf.googlegroups.com...
>
>
>
> > Hello,
> > I stucked on strange problem with data order in dataset.
> > Any clue or suggests will be appreciated.
> > Let's start from the beggining. We have to tables:
>
> > Table1:
> > Import Nr
> > 18
> > 19
> > 20
> > 21
> > 22
> > 23
> > 24
> > 25
> > 26
> > 27
> > 28
> > 29
> > 32
> > 33
> > 34
>
> > Table2:
> > Import Nr
> > 20
> > 21
> > 23
> > 25
> > 26
> > 28
> > 29
> > 32
> > 34
> > 36
> > 37
> > 39
>
> > This is my result which doesn't statisfy me:
> > Import Nr ImportNr2
> > 18
> > 19
> > 20 20
> > 21 21
> > 22
> > 23 23
> > 24
> > 25 25
> > 26 26
> > 27
> > 28 28
> > 29 29
> > 33
> > 34 34
> > 32
> > 36
> > 39
> > 37
>
> > The Code:
> > SELECT TOP 100 PERCENT dbo.temp1.[Import Nr], dbo.temp2.[Import
> > Nr] AS Expr1
> > FROM dbo.temp1 FULL OUTER JOIN
> > dbo.temp2 ON dbo.temp1.[Import Nr] = dbo.temp2.
> > [Import Nr]
>
> > As you can see there are some problems with sorting in second column.
> > I would like to get the result like below:
> > Import Nr ImportNr2
> > 18
> > 19
> > 20 20
> > 21 21
> > 22
> > 23 23
> > 24
> > 25 25
> > 26 26
> > 27
> > 28 28
> > 29 29
> > 32
> > 33
> > 34 34
> > 36
> > 37
> > 39- Ukryj cytowany tekst -
>
> - Pokaz cytowany tekst -

Thanks Russel You're suggestion helped me!
I've tried ORDER BY , but not with ISNULL, and it doesn't work.
But when I used ISNULL it is all as i wanted.
So simple.

Uri Sorry for TOP 100 PERCENT , this is a remain from Sql server
2000 wich have add it and i haven't noticed it !

Thanks again for your help.

PL

6/19/2011 2:07:00 PM

0

On 19/06/2011 15:59, jat wrote:
> On 06/19/2011 08:05 AM, TORREBLANCA? wrote:
>> On 19 jun, 01:46, VivaChavez<TruthSetsUFr...@yahoo.com> wrote:
>>> QUE CHAVEZ SE TUVO QUE IR A CUBA...JE, JE, JE
>>
>> ademas de que no confia en los " socialistas revolucionarios
>> bolivarianos " .......sabe que lo linchan a la menor oportunidad,
>> porque ellos tambien sufren las calamidades y desastrosas realidades
>> que esta padeciendo el pueblo venezolano.
> El otro chiste es que los cubanos tienen secuestrado a Ch?vez... :-D
>
Chavez, no.
Es el que paga todo all?.
Esta bien en la parte oficial de la "apartheid" del sistema de salud

"Sistema de Salud en Cuba
Informaci?n sobre el sistema de salud en Cuba. La verdad que vive el
pueblo, no le mentira oficial."
http://saludcuba.blo...

"Cuba Apartheid
Apartheid en Cuba"
http://cubaapartheid.i...

"II. Medical apartheid"
http://www.cubaverdad.net/apartheid_in_cuba.htm#II._Medical....


Pero si secuestran a muchos otros:
http://cubapresospoliticos.i...

en malas condiciones:

"Carceles en Cuba
Informaci?n sobre como el r?gimen de Castro trata a los reclusos en los
c?rceles en Cuba. Los abusos. La tortura. Condiciones de vida inhumanas."
http://carcelesencuba.blo...

"Tortura en Cuba"
http://cubatortura.i...