pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Getting a specific row from a table
2+ messages / 2 participants
[nested] [flat]

* Getting a specific row from a table
@ 2001-06-13 21:27  Hunter, Ray <rhunter@enterasys.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Hunter, Ray @ 2001-06-13 21:27 UTC (permalink / raw)
  To: pgsql-sql

My problem is that I want to pull to specific rows from a query result.

First here is the query:
SQL-query: 
select card, status, time_stamp, comp_date 
from test_record 
where id = 45 
order by card, comp_date 

Next here is the result:

card					status		time stamp
Comp_Date
ER16-04 ER16-08  			No 		2001-06-13
13:56:52-06 	6/10/01 
ER16-04 ER16-08 			Yes 		2001-06-11
11:37:04-06 	6/11/01 
ER16-04 ER16-08 			No 		2001-06-11
13:13:07-06 	6/11/01 
ER16-04 ER16-08 			No 		2001-06-11
13:13:37-06 	6/11/01 
ER16-04 ER16-08 			No 		2001-06-13
13:57:41-06 	6/12/01 
ER16-04 ER16-08 			Yes 		2001-06-13
13:10:46-06 	6/13/01 
ER16-04 ER16-08 			No 		2001-06-13
13:35:29-06 	6/13/01 
SSR-ATM29-02 SSR-FDDI-02 	No 		2001-06-11 13:21:01-06
6/11/01 
SSR-ATM29-02 SSR-FDDI-02 	Yes 		2001-06-11 13:21:17-06
6/11/01 
SSR-ATM29-02 SSR-FDDI-02 	No 		2001-06-11 13:21:41-06
6/11/01 
SSR-ATM29-02 SSR-FDDI-02 	Yes 		2001-06-11 13:22:21-06
6/11/01 

What I want is the two rows that are bold.  However this list will continue
to grow and have more card types.  I always want the last card type in the
card group, because this has the comp_date that I am looking for.

Thanks,

 
RAY HUNTER
Automated Test Group

 
ENTERASYS NETWORKS
 
Internal:     53888
Phone:       801 887-9888
Fax:          801 972-5789
Cellular:      801 698-0622
E-mail:       rhunter@enterasys.com
 
www.enterasys.com

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Getting a specific row from a table
@ 2001-06-14 12:02  Richard Huxton <dev@archonet.com>
  parent: Hunter, Ray <rhunter@enterasys.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Huxton @ 2001-06-14 12:02 UTC (permalink / raw)
  To: Hunter, Ray <rhunter@enterasys.com>; pgsql-sql

From: "Hunter, Ray" <rhunter@enterasys.com>

> My problem is that I want to pull to specific rows from a query result.
>
> First here is the query:
> SQL-query:
> select card, status, time_stamp, comp_date
> from test_record
> where id = 45
> order by card, comp_date
>

> What I want is the two rows that are bold.  However this list will
continue
> to grow and have more card types.  I always want the last card type in the
> card group, because this has the comp_date that I am looking for.

I'm assuming here that id,card,time_stamp can't have duplicates and that you
want the most recent time_stamp for a specific id,card.

SELECT id,card,status,time_stamp,comp_date FROM cards c1
WHERE c1.id=45
AND c1.time_stamp =
 (SELECT max(time_stamp)
  FROM cards c2
  WHERE c2.id=c1.id
  AND c2.card=c1.card);

What we're doing here is only selecting records where the current time_stamp
matches the maximum time_stamp for a specific id/card. If you have duplicate
time_stamp values for a specific id/card this won't work.

If this is too slow, use a temporary table to assemble
id,card,max(time_stamp) and join to the temporary table.

HTH

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-06-14 12:02 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-13 21:27 Getting a specific row from a table Hunter, Ray <rhunter@enterasys.com>
2001-06-14 12:02 ` Richard Huxton <dev@archonet.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox