Order.

  • Thread starter Thread starter Rodolfo Fontes
  • Start date Start date
R

Rodolfo Fontes

Hi group,

Sorry for asking it again, but does anyone know how to get to coluun
order like the example below?

Codigo SubCodigo Order
1 1 1
1 5 2
1 7 3
2 2 1
2 8 2
3 6 1
4 8 1
5 9 1
5 8 2
5 1 3

For each new "Codigo" the order must reset.
So, for "Codigo" = 1, there would be 3 "SubCodigo" and their order.

I've been thinking, and maybe another solution should be making a code for
that.

Thanks for any help,
Rodolfo Fontes
 
Dear Rodolfo:

I looks as though you would just sort by Codigo then Order:

ORDER BY Codigo, [Order]

Having a column named Order is not a great idea as it is a reserved
word.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Tom,

The trouble is that i do not have any value for "Order"
What i want to do is to fill that.

Tks,
Rodolfo Fontes

Tom Ellison said:
Dear Rodolfo:

I looks as though you would just sort by Codigo then Order:

ORDER BY Codigo, [Order]

Having a column named Order is not a great idea as it is a reserved
word.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi group,

Sorry for asking it again, but does anyone know how to get to coluun
order like the example below?

Codigo SubCodigo Order
1 1 1
1 5 2
1 7 3
2 2 1
2 8 2
3 6 1
4 8 1
5 9 1
5 8 2
5 1 3

For each new "Codigo" the order must reset.
So, for "Codigo" = 1, there would be 3 "SubCodigo" and their order.

I've been thinking, and maybe another solution should be making a code for
that.

Thanks for any help,
Rodolfo Fontes
Click to expand...
Click to expand...
 
Dear Rodolfo:

I believe then you are saying you need to generate the Order column as
a "ranking."

I cannot easily see on what basis you expect to create this. For the
first 7 rows it looks like you are raking within groups by Codigo
according to the ascending values in SubCodigo. But for the last 3
rows where Codigo = 5 the Order does not follow that rule.

In order to create the Order column (which I'll call the "Rank" rather
than use a reserved word, as I warned earlier) I need to know what the
rule is. I'll try to procede when you explain that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom,

The trouble is that i do not have any value for "Order"
What i want to do is to fill that.

Tks,
Rodolfo Fontes

Tom Ellison said:
Dear Rodolfo:

I looks as though you would just sort by Codigo then Order:

ORDER BY Codigo, [Order]

Having a column named Order is not a great idea as it is a reserved
word.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi group,

Sorry for asking it again, but does anyone know how to get to coluun
order like the example below?

Codigo SubCodigo Order
1 1 1
1 5 2
1 7 3
2 2 1
2 8 2
3 6 1
4 8 1
5 9 1
5 8 2
5 1 3

For each new "Codigo" the order must reset.
So, for "Codigo" = 1, there would be 3 "SubCodigo" and their order.

I've been thinking, and maybe another solution should be making a code for
that.

Thanks for any help,
Rodolfo Fontes
Click to expand...
Click to expand...
Click to expand...
 
Hi Tom,

When i've done that sample, i've just put the same situation that i have
here.
For solving it, you should just look for the "Codigo" and then "Order" that
is exactly a rank, as you said.
So, for each new Codigo, the Order get back to "1".
Do you know how to solve it?

Thanks for helping,
Rodolfo Fontes


Tom Ellison said:
Dear Rodolfo:

I believe then you are saying you need to generate the Order column as
a "ranking."

I cannot easily see on what basis you expect to create this. For the
first 7 rows it looks like you are raking within groups by Codigo
according to the ascending values in SubCodigo. But for the last 3
rows where Codigo = 5 the Order does not follow that rule.

In order to create the Order column (which I'll call the "Rank" rather
than use a reserved word, as I warned earlier) I need to know what the
rule is. I'll try to procede when you explain that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom,

The trouble is that i do not have any value for "Order"
What i want to do is to fill that.

Tks,
Rodolfo Fontes

Tom Ellison said:
Dear Rodolfo:

I looks as though you would just sort by Codigo then Order:

ORDER BY Codigo, [Order]

Having a column named Order is not a great idea as it is a reserved
word.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 21 Sep 2004 11:12:47 -0300, "Rodolfo Fontes"

Hi group,

Sorry for asking it again, but does anyone know how to get to coluun
order like the example below?

Codigo SubCodigo Order
1 1 1
1 5 2
1 7 3
2 2 1
2 8 2
3 6 1
4 8 1
5 9 1
5 8 2
5 1 3

For each new "Codigo" the order must reset.
So, for "Codigo" = 1, there would be 3 "SubCodigo" and their order.

I've been thinking, and maybe another solution should be making a code for
that.

Thanks for any help,
Rodolfo Fontes
Click to expand...
Click to expand...
Click to expand...
 
Dear Rodolfo:

Ignoring for now the apparent contradiction in the way the rows of
your example would rank those rows where Codigo = 5, and ordering
within each Codigo group according to the values in SubCodigo in
ascending order, the query would be:

SELECT Codigo, SubCodigo,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Codigo = T.Codigo
AND T1.SubCodigo < T.SubCodigo) AS Rank
FROM YourTable T
ORDER BY Codigo, SubCodigo

This matches what you requested except for the reveresed order for
those rows where Codigo = 5. I don't know whether this is because
your example is wrong, or if you really intend something else.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom,

When i've done that sample, i've just put the same situation that i have
here.
For solving it, you should just look for the "Codigo" and then "Order" that
is exactly a rank, as you said.
So, for each new Codigo, the Order get back to "1".
Do you know how to solve it?

Thanks for helping,
Rodolfo Fontes


Tom Ellison said:
Dear Rodolfo:

I believe then you are saying you need to generate the Order column as
a "ranking."

I cannot easily see on what basis you expect to create this. For the
first 7 rows it looks like you are raking within groups by Codigo
according to the ascending values in SubCodigo. But for the last 3
rows where Codigo = 5 the Order does not follow that rule.

In order to create the Order column (which I'll call the "Rank" rather
than use a reserved word, as I warned earlier) I need to know what the
rule is. I'll try to procede when you explain that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi Tom,

The trouble is that i do not have any value for "Order"
What i want to do is to fill that.

Tks,
Rodolfo Fontes

"Tom Ellison" <[email protected]> escreveu na mensagem
Dear Rodolfo:

I looks as though you would just sort by Codigo then Order:

ORDER BY Codigo, [Order]

Having a column named Order is not a great idea as it is a reserved
word.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Tue, 21 Sep 2004 11:12:47 -0300, "Rodolfo Fontes"

Hi group,

Sorry for asking it again, but does anyone know how to get to coluun
order like the example below?

Codigo SubCodigo Order
1 1 1
1 5 2
1 7 3
2 2 1
2 8 2
3 6 1
4 8 1
5 9 1
5 8 2
5 1 3

For each new "Codigo" the order must reset.
So, for "Codigo" = 1, there would be 3 "SubCodigo" and their order.

I've been thinking, and maybe another solution should be making a code
for
that.

Thanks for any help,
Rodolfo Fontes
Click to expand...
Click to expand...
Click to expand...
 
Back
Top