0

I have made a view(joining four tables) like below:

ID  |  BookID  |    date    |  points  |
 1  |    11    | 2014-11-01 |    15    |
 1  |    11    | 2015-01-01 |    16    |
 1  |    11    | 2014-12-01 |    17    |
 1  |    12    | 2014-02-11 |    18    |
 1  |    12    | 2014-03-11 |    19    |
 1  |    12    | 2014-04-11 |    15    |
 1  |    13    | 2014-12-23 |    121   |
 1  |    14    | 2014-01-15 |    113   |
 1  |    14    | 2014-02-08 |    112   |

I want the result of this view as below

ID  |  BookID  |    Date     |  points  |
 1  |    11    |  2015-01-01 |    16    |
 1  |    12    |  2014-04-11 |    15    |
 1  |    13    |  2014-12-23 |    121   |
 1  |    14    |  2014-02-08 |    112   |

It should be like Distincit Book ID with max date and showing as seprate points. So far i have tried the group by with join and group by with date. But it is getting a bit over as i am unable to find a solution to this.

My Query is:

SELECT m1.* FROM viewPoints m1 LEFT JOIN viewPoints m2 
ON (m1.BookID = m2.BookID AND m1.Date < m2.Date)
WHERE m1.ID= 1 and m2.Date IS NULL

ORDER BY m1.BookID

Any help! Thanks in Advance.

DDay
  • 100
  • 1
  • 1
  • 9
  • also share your query , so that we can know table structure – MONTS_MIND_Hacker Feb 23 '15 at 11:00
  • Probably find your solution in [this so question and answer](http://stackoverflow.com/a/612268/1720332) – JBA Feb 23 '15 at 11:02
  • @JBA yes it works partially for me as i found this solution too. what if for some books my points are NULL and i want to skip those? as it is joining on BookID which takes all and displays a repeat value for null value? – DDay Feb 23 '15 at 11:10
  • Can we see the CREATE VIEW query? And maybe some proper DDLs – Strawberry Feb 23 '15 at 11:46

3 Answers3

1

Maybe this is what you want?

select v.* 
from viewPoints v
join (
    select 
       BookID, 
       max(date) max_date 
    from viewPoints
    where points is not null 
    group by BookID
) v2 on v.BookID = v2.BookID and v.date = v2.max_date
where v.points is not null
order by v.BookID

Sample SQL Fiddle

Sample output:

| ID | BOOKID |                            DATE | POINTS |
|----|--------|---------------------------------|--------|
|  1 |     11 |  January, 01 2015 00:00:00+0000 |     16 |
|  1 |     12 |    April, 11 2014 00:00:00+0000 |     15 |
|  1 |     13 | December, 23 2014 00:00:00+0000 |    121 |
|  1 |     14 | February, 08 2014 00:00:00+0000 |    112 |
jpw
  • 44,361
  • 6
  • 66
  • 86
  • I get the repeating bookID's against which the points are null. which i don't want to. am i missing something? what's the problem? totally exhausted with this little problem, :( – DDay Feb 23 '15 at 11:39
  • @DDay Not sure what you mean as there are no null values in your sample data; in any case the rows with null points should be excluded from the result altogether (from the where clause in the derived table). Could you perhaps add some sample data that produces the error? – jpw Feb 23 '15 at 11:41
  • @DDay I think I understand your problem now; if there are other BookIDs with the same date as the max date but null points they were included too; I've changed the query to exclude them now. – jpw Feb 23 '15 at 12:02
  • 1
    Thanks for your time :) it worked for me a long time ago. but unfortunately i forget to thank you. – DDay May 28 '15 at 07:48
0
CREATE VIEW [BOOKLIST] AS
SELECT m1.* FROM viewPoints m1 LEFT JOIN viewPoints m2 
ON (m1.BookID = m2.BookID AND m1.Date < m2.Date)
WHERE m1.ID= 1 and m2.Date IS NULL
ORDER BY m1.BookID    

SELECT ID, DISTINCT BookID, Date, points FROM BOOKLIST
WHERE Date BETWEEN "start date" AND "end date"
JoeC
  • 36
  • 6
  • i don't know the between dates and also i have tried this by giving hard coded date. it doesn't work. – DDay Feb 23 '15 at 11:13
0
SELECT * 
 FROM tablename
  WHERE DATE
 IN (

   SELECT MAX( DATE ) 
   FROM tablename
   GROUP BY bookid
   ORDER BY DATE DESC
  )
 ORDER BY DATE DESC
Shailesh Katarmal
  • 2,757
  • 1
  • 12
  • 15