How do i Search/Filtering using by date
-
I have create a view based on 2 tables that are related See below
The situation: -
Fields in table 1 (tblItemType) are
ItId (auto number, int, Pk)
ItemType
Gender
ModifiedDate (datetime, getdate())
Fields in table 2 (tblItemColour) are
ItId (Fk, int)
ItColourID (auto number, PK, int)
colour
ModifiedDate (datetime, getdate())
As you can see tblItemType and tblItemColour are related through the column "ItID"
I have create view to display
tblItemType
Columns:-
ItId (auto number, int, Pk)
ItemType
tblItemType
Columns:-
colour
ModifiedDate (datetime, getdate())
Now the question can/how can i do a search based on the Itid and Modifieddate field (i would like see the most record of the colour inserted in to the tables).
I know that i must use tha "MAX" function in the modifieddate column as a criteria but what else do i write with "MAX" i've tried many things
Hope you understand the question..thanks in advance
David
-
Not that clear. Can you post a sample data?
Are you looking for the most recent inserted record or the greatest value for itColourID?
Steve Jones
-
i am looking for the most current record inserted by the user. I thought of usering the modifieddate field as the search criteria.
i've tried using the MAX and ALL function
chears
-
you probably want to do a subquery
select colour, ..
from MyView
where modified = ( select max( modified) from MyTable)
Steve Jones
-
thanks very much for that i'll have a bash tommorrow at work, i'll let you know how i got on.
-
This reply/solution is curtosy of Steven Jones
I wrote/questioned " I need to see the lst row inserted by all users i.e colum names are
Uid ItId ItemType Colour Time
1 50 shirt black 12.30am
2 51 trousers green 12.31am
3 52 hat yellow 12.32am
1 140 blouse white 12.40am
3 501 socks black 12.50am
therefore i would for e.g need to get
UID 1, ItId 140..entered at time 12.40 am AND UID 3, ItId 201..entered at time 12.50 am "
STEVES SOLUTION IS AS FOLLOWS
Add the UID in the column list and the group by, then add UID in the subquery.
Note from Dood to Steve, hope you had a good holiday, thank you for the help
-
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply