sql - postgresql order by group -
i use below query rows 1,3,4,5,6
in first table, want know how order parenttagid
order name final 2 arrays/rows 1,3,5
, 1,4,6
var query = 'select * "articletag" left outer join "tag" on ( "articletag"."tagid" = "tag"."tagid" ) "articleid" = $1 order "parenttagid" desc'; create table if not exists "tag"( "tagid" serial not null, "parenttagid" integer, "name" varchar, primary key ("tagid") ); tagid | parenttagid | name | 1 | | | 2 | | b | 3 | 1 | z | 4 | 1 | f | 5 | 3 | g | 6 | 4 | z | create table if not exists "articletag"( "articletagid" serial not null, "articleid" integer not null, "tagid" integer not null, foreign key ("articleid") references "article" ("articleid") on delete cascade on update cascade, foreign key ("tagid") references "tag0" ("tagid") on delete cascade on update cascade, primary key ("articletagid") ); articletagid | articleid | tagid | 0 | 0 | 1 | 1 | 0 | 3 | 2 | 0 | 4 | 3 | 0 | 5 | 4 | 0 | 6 |
update
wondering possible return multiple grouped rows.
hope result above example
(number tagid)
there [1,3,4,5,6]
in articletag
table , in tag
table 1>3>5
1>4>6
parent child relation, there 2 path, return 2 array [1,3,5] , [1,4,6]
another example
if there [1,2,3,4,5,6]
in articletag table, in tag table 1>3>5
1>4>6
2
, 3 path, return 3 array [1,3,5] , [1,4,6] , [2]
im not sure question returns tagid 1,4,3,5
select * "articletag" left join "tag" on ("articletag"."tagid" = "tag"."tagid") order "parenttagid" nulls first, "name" ;
output
| articletagid | articleid | tagid | tagid | parenttagid | name | |--------------|-----------|-------|-------|-------------|------| | 0 | 0 | 1 | 1 | (null) | | | 2 | 0 | 4 | 4 | 1 | f | | 1 | 0 | 3 | 3 | 1 | z | | 3 | 0 | 5 | 5 | 3 | g |
Comments
Post a Comment