|
Re: Sorting and Grouping with two different fields
by Limbic~Region (Chancellor) on Jul 12, 2007 at 13:07 UTC
|
vcTheGuru,
This is actually an interesting sort order. If you were going to do this in perl, it would look something like (untested):
my %data;
while (my @col = $dbh->fetchrow_array) {
if (exists $data{$col[1]}) {
$data{$col[1]}{min} = $col[0] if $col[0] < $data{$col[1]}{min}
+;
push @{$data{$col[1]}{entry}}, \@col1;
}
else {
$data{$col[1]}{min} = $col[0];
$data{$col[1]}{entry} = [\@col];
}
}
for my $state (sort {$a->{min} <=> $b->{min}} keys %data) {
for my $entry (sort {$a->[0] <=> $b->[0]} values %{$data{$state}{e
+ntry}}) {
print "@$entry\n";
}
}
Basically, you keep a low water mark for each state. Order the states by their lowest id and then for each entry for that state by id in ascending order.
| [reply]
[d/l] |
|
|
Thanks for the perl Solution. but . . .
I am getting the error "Can't coerce array into hash at" for my $entry (sort {$a->[0] <=> $b->[0]} values %{$data{$state}{entry}}) I googled it and refered "Can't coerce array into hash at", but couldnt fix it. Anybody please help!
Thanks In advance for the help
--VC
| [reply]
[d/l] |
|
|
vcTheGuru,
As I stated, the code was untested. It worries me that you are testing code you don't understand and that you can't generate your own code using my description of the algorithm. I believe the solution is:
for my $entry (sort {$a->[0] <=> $b->[0]} @{$data{$state}{entry}}) {
# ...
}
| [reply]
[d/l] |
|
|
|
|
Re: Sorting and Grouping with two different fields
by dsheroh (Monsignor) on Jul 12, 2007 at 15:10 UTC
|
Let's see if I've got this right... You want to sort the data such that all the records for each state are together and the states should be ordered based on the lowest id present for each state, correct?
SQL can do that.
SELECT * FROM states;
id | state | city
----+------------+----------------
1 | Virginia | Norfolk
2 | Virginia | Chesapeake
3 | Virginia | Virginia Beach
4 | Indiana | Evansville
5 | Indiana | Fort Wayne
6 | Virginia | Falls Church
7 | Indiana | Indianapolis
8 | Washington | Seatle
9 | Washington | Spokane
SELECT s.id AS id, s.state AS state, s.city AS city FROM states s JOIN
+ (SELECT min(id) AS min_id, state FROM states GROUP BY state) j ON s.
+state = j.state ORDER BY j.min_id, s.id ;
id | state | city
----+------------+----------------
1 | Virginia | Norfolk
2 | Virginia | Chesapeake
3 | Virginia | Virginia Beach
6 | Virginia | Falls Church
4 | Indiana | Evansville
5 | Indiana | Fort Wayne
7 | Indiana | Indianapolis
8 | Washington | Seatle
9 | Washington | Spokane
(Tested using PostgreSQL. Your syntax may vary.) | [reply]
[d/l] |
|
|
| [reply] |
Re: Sorting and Grouping with two different fields
by arkturuz (Curate) on Jul 12, 2007 at 12:10 UTC
|
Notice that if id field is 'auto_increment' you cannot have two 7s. Also, it's better to use SQL on your data as they might increase in uncontrollable way. That's what the databases are for in the first place.
For your data you can use SQL like this (MySQL):
ORDER BY state DESC, id ASC
Implementing this in Perl would be a waste of time, as the database can do it for you already.
| [reply]
[d/l] |
|
|
I can not use "OREDR BY state DESC" so that Virginia will come up. When I sort by state with " DESC state, id ASC" the order of states will be "Washington, Virginia,Indiana". But I need it sorted by id, but grouped with state.
--VC
| [reply]
[d/l]
[select] |
|
|
I would just add another field SMALLINT from 1-50 called sort_order and then you can apply your arbitrary sort order to each state...
UPDATE table1 SET sort_order = 1 WHERE state = "Virginia"
Then you can
ORDER BY sort_order DESC, city DESC
| [reply]
[d/l]
[select] |
|
|
Re: Sorting and Grouping with two different fields
by ForgotPasswordAgain (Vicar) on Jul 12, 2007 at 12:00 UTC
|
This has nothing to do with Perl... but you can use ORDER BY - you just order by State, ID. | [reply] |
Re: Sorting and Grouping with two different fields
by moritz (Cardinal) on Jul 12, 2007 at 12:09 UTC
|
BTW your ID is not a very primary key, '6' is occuring twice. Probably just a typo ;)
| [reply] |