pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / 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> 2001-06-14 12:02 ` Re: Getting a specific row from a table Richard Huxton <dev@archonet.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-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> 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