1

I have this data:

idqueries---queryDate-----------------sets_idsets-----result--site
13646-------2011-11-16-13:22:31-------1---------------3-------1
13536-------2011-11-16-12:26:23-------1---------------6-------1
13435-------2011-11-16-12:12:15-------1---------------3-------1
13334-------2011-11-16-11:37:13-------1---------------4-------1
13332-------2011-11-16-09:21:20-------1---------------7-------1
13325-------2011-11-15-12:09:18-------1---------------2-------1
13140-------2011-11-15-08:48:41-------1---------------1-------1
12976-------2011-11-15-08:37:57-------1---------------6-------1
12839-------2011-11-15-08:29:06-------1---------------3-------1
12769-------2011-11-14-16:21:14-------1---------------34------1
12584-------2011-11-14-09:53:29-------1---------------9-------1
12583-------2011-11-14-09:53:00-------1---------------23------1
12582-------2011-11-14-09:52:26-------1---------------2-------1
12565-------2011-11-14-09:48:59-------1---------------6-------1
12557-------2011-11-14-09:47:38-------1---------------7-------1
12458-------2011-11-10-12:27:18-------1---------------10------1
12457-------2011-11-10-12:24:04-------1---------------10------0
12450-------2011-11-10-12:21:33-------1---------------1-------0
12448-------2011-11-10-12:16:57-------1---------------1-------0
12442-------2011-11-10-11:33:23-------1---------------1-------0
12435-------2011-11-10-11:16:48-------1---------------1-------0
12431-------2011-11-10-09:33:31-------1---------------10------0

And I need to select rows where each row is that with MIN value of result from each day.
Example:

13646-------2011-11-16-13:22:31-------1---------------3-------1
13140-------2011-11-15-08:48:41-------1---------------1-------1
12582-------2011-11-14-09:52:26-------1---------------2-------1
12450-------2011-11-10-12:21:33-------1---------------1-------0

If there is more than one row with minimal result like it was on 2011-11-10 in sample data (few rows with result value of 1) I need to select only one - newest one from that day.

Can anyone help?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
kdkwiat
  • 11
  • 2
  • Do a GROUP BY for the date, but round the date to day. Then SELECT min(result) – kol Nov 18 '11 at 00:23
  • I added the `greatest-n-per-group` tag, click on it and it'll take you to dozens of good answers for this question. – Bill Karwin Nov 18 '11 at 00:25
  • possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Bill Karwin Nov 18 '11 at 00:31

1 Answers1

0

Going to take a stab at this:

SELECT c.idqueries, t.queryDate, c.sets_idsets, t.result, c.site 
FROM
  (SELECT queryDate, MIN(result)
  OVER (ORDER BY queryDate DESC) AS 'result'
  FROM TBL GROUP BY queryDate) AS c,
TBL t
WHERE c.queryDate = t.queryDate
Nick Rolando
  • 25,879
  • 13
  • 79
  • 119