Thread: Getting a specific row from a table
<p><font face="Verdana" size="2">My problem is that I want to pull to specific rows from a query result.</font><p><font face="Verdana"size="2">First here is the query:</font><br /><font face="Times New Roman">SQL-query:<br /> select card, status,time_stamp, comp_date<br /> from test_record<br /> where id = 45<br /> order by card, comp_date </font><p><font face="Verdana"size="2">Next here is the result:</font><p><u><b><font face="Verdana" size="2">card status time stamp Comp_Date</font></b></u><br /><font face="Times New Roman">ER16-04ER16-08 No 2001-06-13 13:56:52-06 6/10/01 </font><br /><font face="TimesNew Roman">ER16-04 ER16-08 Yes 2001-06-11 11:37:04-06 6/11/01 </font><br/><font face="Times New Roman">ER16-04 ER16-08 No 2001-06-11 13:13:07-06 6/11/01 </font><br /><font face="Times New Roman">ER16-04 ER16-08 No 2001-06-1113:13:37-06 6/11/01 </font><br /><font face="Times New Roman">ER16-04 ER16-08 No 2001-06-13 13:57:41-06 6/12/01 </font><br /><font face="Times New Roman">ER16-04 ER16-08 Yes 2001-06-13 13:10:46-06 6/13/01 </font><br /><b><font face="Times New Roman">ER16-04 ER16-08 No 2001-06-13 13:35:29-06 6/13/01 </font></b><br /><font face="Times New Roman">SSR-ATM29-02SSR-FDDI-02 No 2001-06-11 13:21:01-06 6/11/01 </font><br /><font face="Times NewRoman">SSR-ATM29-02 SSR-FDDI-02 Yes 2001-06-11 13:21:17-06 6/11/01 </font><br /><font face="TimesNew Roman">SSR-ATM29-02 SSR-FDDI-02 No 2001-06-11 13:21:41-06 6/11/01 </font><br /><b><fontface="Times New Roman">SSR-ATM29-02 SSR-FDDI-02 Yes 2001-06-11 13:22:21-06 6/11/01 </font></b><p><fontface="Verdana" size="2">What I want is the two rows that are bold. However this list will continue togrow and have more card types. I always want the last card type in the card group, because this has the comp_date thatI am looking for.</font><p><font face="Verdana" size="2">Thanks,</font><p><font face="Arial"> </font><br /><b><font face="Verdana"size="2">RAY HUNTER</font></b><br /><font face="Verdana" size="2">Automated Test Group</font><p><font face="Arial"> </font><br/><b><font face="Verdana" size="2">ENTERASYS NETWORKS</font></b><br /><font face="Arial"> </font><br/><font face="Verdana" size="2">Internal: 53888</font><br /><font face="Verdana" size="2">Phone: 801 887-9888</font><br /><font face="Verdana" size="2">Fax: 801 972-5789</font><br /><fontface="Verdana" size="2">Cellular: 801 698-0622</font><br /><font face="Verdana" size="2">E-mail: </font><u><font color="#0000FF" face="Verdana" size="2">rhunter@enterasys.com</font></u><br /><fontface="Arial"> </font><br /><u><font color="#0000FF" face="Verdana" size="2">www.enterasys.com</font></u>
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