Trying To Avoid Two Database Calls... (Double-Post)

Sorry, I double-posted this to comp.databases.mysql, big apologies. I
forgot that I was subscribed to this NG as well....

Hi there

I am trying to get some information from a table, and I think I might be
asking too much in one query. I need to get the maximum PlanNum (easy),
but also the Alias that goes along with that PlanNum (can't seem to do
it).

Here's the table:

FloorID | BuildingID | Stack | Alias | PlanNum
----------------------------------------------
307 30 1 Floor 1 1
308 30 2 Floor 2 1
309 30 3 Floor 3 1
310 30 -1 Floor -1 1
493 30 -1 Floor -1 2
494 30 -1 Basement 3


My current query is:
SELECT FloorID,Stack,MAX(PlanNum) as PlanNum,Alias FROM pm_Floors WHERE
BuildingID=30 GROUP BY Stack ORDER BY Stack

That gives me:
310 / -1 / 3 / Floor -1
307 / 1 / 1 / Floor 1
308 / 2 / 1 / Floor 2
309 / 3 / 1 / Floor 3

I'm looking for that first result (stack -1) to give me the alias of
"Basement" (it's latest alias) instead of "Floor -1"....

Possible with one call?

Thanks!

Re: Trying To Avoid Two Database Calls... (Double-Post)

On Oct 11, 11:18 am, Good Man wrote:
> Sorry, I double-posted this to comp.databases.mysql, big apologies. I
> forgot that I was subscribed to this NG as well....
>
> Hi there
>
> I am trying to get some information from a table, and I think I might be
> asking too much in one query. I need to get the maximum PlanNum (easy),
> but also the Alias that goes along with that PlanNum (can't seem to do
> it).
>
> Here's the table:
>
> FloorID | BuildingID | Stack | Alias | PlanNum
> ----------------------------------------------
> 307 30 1 Floor 1 1
> 308 30 2 Floor 2 1
> 309 30 3 Floor 3 1
> 310 30 -1 Floor -1 1
> 493 30 -1 Floor -1 2
> 494 30 -1 Basement 3
>
> My current query is:
> SELECT FloorID,Stack,MAX(PlanNum) as PlanNum,Alias FROM pm_Floors WHERE
> BuildingID=30 GROUP BY Stack ORDER BY Stack
>
> That gives me:
> 310 / -1 / 3 / Floor -1
> 307 / 1 / 1 / Floor 1
> 308 / 2 / 1 / Floor 2
> 309 / 3 / 1 / Floor 3
>
> I'm looking for that first result (stack -1) to give me the alias of
> "Basement" (it's latest alias) instead of "Floor -1"....
>
> Possible with one call?
>
> Thanks!

You query appears to rely on MySQL's very loose GROUP BY rules, here's
a variation that might work for you in Postgres:

Result:
stack | plan_slash_alias
-------+------------------
-1 | 3/Basement
1 | 1/Floor 1
2 | 1/Floor 2
3 | 1/Floor 3
(4 rows)

Query:
BEGIN ;

CREATE TABLE public.floors (
floor_id integer not null
,building_id integer not null
,stack integer not null
,alias varchar(32) not null
,plan_num integer not null
) ;

COPY public.floors (floor_id, building_id, stack, alias, plan_num)
FROM stdin DELIMITER '|';
307|30|1|Floor 1|1
308|30|2|Floor 2|1
309|30|3|Floor 3|1
310|30|-1|Floor -1|1
493|30|-1|Floor -1|2
494|30|-1|Basement|3
\.

SELECT stack, max(plan_num||'/'||alias) as plan_slash_alias
FROM public.floors
WHERE building_id=30
GROUP BY 1
ORDER BY 1 ;

ROLLBACK ;