|
View previous topic :: View next topic
|
| Author |
Message |
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
I have a table(itemlist) containing
name date item
rakesh 2007-10-02 shoe
Suresh 2008-12-03 book
vinodh 2009-11-06 mobile
rakesh 2009-07-06 shoe
Suresh 2008-12-03 pen
rakesh 2009-07-06 bike
Mahesh 2010-02-06 bag
vinodh 2004-16-09 mobile
I want to get all the names and number of items they have bought lately
The result will be like
name max date count
rakesh 2009-07-06 2
Suresh 2008-12-03 2
Mahesh 2010-02-06 1
vinodh 2009-11-06 1
The query i wrote was
| Code: |
| select name,max(date),count(*) from itemlistgroup by name; |
but this gives me
the entire count and not count for the latest date
name max date count
rakesh 2009-07-06 3
Suresh 2008-12-03 2
Mahesh 2010-02-06 1
vinodh 2009-11-06 2
can you please point out what i might be missing or where i am going wrong in the query ?
Thanks in advance |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Rakesh,
When does the latest date start ?
After knowing that, you can put a condition based on that.
Sushanth |
|
| Back to top |
|
|
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
i might have confused a bit .
i just want to know how much each person has bought in their last shopping date.
For example.
though rakesh (the one in the example provided) had bought 1 item on 2007-10-02 . i just want to know how much he had bought on 2009-07-06 which happens to be his last date he had gone for shopping |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select name, date_col, count(*)
from tab1 A
where date_col = (select max(date_col) from tab1 B where a.name = b.name)
group by name,date |
The best performance would be if there was an index on or beginning with (name,date_col) |
|
| Back to top |
|
|
|
|
