help writing query

[michaelgr1]

Hello, I have for example the following table NICKNAME JOBNAME EQUIPNAME STARTTIME SNB_A0_01_0719 051_A0_marbles_stress_AB_tie2vss post -FIB testing 2009-09-10 11:33:46.880 SNB_A0_01_0719 051_A0_marbles_stress_AB_tie2vss Post-FIB Testing Cabinet0 IS 2009-09-10 11:33:19.203 SNB_A0_01_0719 051_A0_marbles_stress_AB_tie2vss TESTING Cabinet 2009-09-10 11:34:13.233 SNB_A0_01_0720 051_A0_marbles_stress_AB_tie2vss post -FIB testing 2009-09-10 12:11:24.790 SNB_A0_01_0720 051_A0_marbles_stress_AB_tie2vss Post-FIB Testing Cabinet0 IS 2009-09-10 12:10:39.340 SNB_A0_01_0720 051_A0_marbles_stress_AB_tie2vss Pre-FIB Testing Cabinet0 IS 2009-09-10 07:58:20.937 SNB_A0_01_0720 051_A0_marbles_stress_AB_tie2vss TESTING Cabinet 2009-09-10 12:11:43.603 SNB_A0_01_0721 052_A0_marbles_stress_A_tie2vss Pre FIB Testing 2009-09-10 11:13:59.960 SNB_A0_01_0721 052_A0_marbles_stress_A_tie2vss Pre-FIB Testing Cabinet0 IS 2009-09-10 09:28:10.480 SNB_A0_01_0722 053_A0_marbles_stress_b_tie2vss Pre FIB Testing 2009-09-10 10:33:12.183 SNB_A0_01_0722 053_A0_marbles_stress_b_tie2vss Pre-FIB Testing Cabinet0 IS 2009-09-10 10:23:06.260 SNB_A1_00_0718 050_A1_DDR_Rx_Delay_pp Pre-FIB Testing Cabinet0 IS 2009-09-10 06:34:43.330 I need to choose distinct nicknames,jobnames and equipnames where the starttime is the biggest for example for nickname SNB_A0_01_0719 i need only the 3rd row. How can i do it?

[Mycroft Holmes]

Use a CTE with a Row_Number() partitioned over nickname, ordered by starttime desc with the appropriate where filter. From that select the record with row_number() = 3

[Johan Hakkesteegt]

Or:

SELECT * FROM TEST_TABLE
WHERE STARTTIME IN (SELECT MAX(STARTTIME)
FROM TEST_TABLE
GROUP BY NICKNAME, JOBNAME)

My advice is free, and you may get what you paid for.

modified on Friday, September 11, 2009 8:04 AM