sql help

[dips255]

Friends please help

LoginName Prop_code Visitdate
scorpio84 RS32650 10/23/08 12:14 PM
scorpio84 RS29589 10/23/08 12:14 PM
fatima17124 RS47255 10/23/08 6:32 AM
vinit32831 RS33019 10/22/08 2:31 AM
vinit32831 RS33019 10/22/08 2:31 AM
vinit32831 RS39680 10/22/08 2:30 AM
vinit32831 RS47246 10/22/08 2:26 AM
sashi133 RS46091 10/20/08 4:38 AM
sashi133 RS46091 10/20/08 4:38 AM
anilrsonawane RS47101 10/19/08 11:24 AM
monaz75922 RS34734 10/18/08 1:16 PM
indianaproperties RS26666 10/18/08 4:58 AM
anujapatel2005 RS39284 10/18/08 4:24 AM
anujapatel2005 RS39284 10/18/08 4:23 AM
anujapatel2005 RS39284 10/18/08 4:23 AM
anujapatel2005 RS39284 10/18/08 4:21 AM
anujapatel2005 RS39284 10/18/08 4:21 AM
anuja15206 RS39822 10/18/08 4:11 AM
indianaproperties RS39499 10/18/08 3:55 AM
ashok77499 RS38133 10/18/08 1:32 AM
able62266 RS28374 10/16/08 3:21 AM
able62266 RS28374 10/16/08 3:21 AM
able62266 RS29815 10/16/08 3:20 AM

I want to display only the top most prop_code of latest date for each user; pls help me for the query.

[Baradaran]

Hi,
To make it very simple, add a unique ID to the table, then use

select max(id), LoginName Prop_code Visitdate from TABLE_NAME

As every entry gets a higher ID, you automatically get the latest login date!

Good luck!

[Baradaran]

Hi,
To make it very simple, add a unique ID to the table, then use

select max(id), LoginName Prop_code Visitdate from TABLE_NAME

As every entry gets a higher ID, you automatically get the latest login date!

Good luck!

[dips255]

i tried it but got following errors
Server: Msg 8118, Level 16, State 1, Line 1
Column 'hittable.LoginName' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'hittable.prop_code' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'hittable.visitdate' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

[Baradaran]

Hi,
you have to use group by in your sql command:

select max(id) as ID, LoginName Prop_code Visitdate from TABLE_NAME Group by LoginName;

This way you only get one record from each LoginName with the latest login data!

Good luck!

[dips255]

still not getting

select max(HitId) as [ID], LoginName, Prop_code, Visitdate from HitTable Group by LoginName

Server: Msg 8120, Level 16, State 1, Line 1
Column 'HitTable.Prop_code' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'HitTable.Visitdate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[Baradaran]

Hi,
Everything you select on the left side, must be in the group by clause.

select max(HitId) as [ID], LoginName, Prop_code, Visitdate from HitTable Group by LoginName, Prop_code, Visitdate.

[dips255]

hi

Thanks for all your replies
I'm still having problem

i'll explain u what i'm trying to do
when a user sends enquiry for a product it goes to table propertyhittable where hitid the index. if the receiver replies to the enquiry, his reply goes to table mailbox and all subsequent mail correspondence goes to mailbox. now on my Inbox Page i have to dispaly all the enquiries like gmail (each mail clustered with all past correspondence)

I used the following query:

select HitId,hitid as mailid,Login_Id,Star,Subject,Prop_Code,visitdate, replydate,mailread from propertyhittable where login_id<>'71727' and deleted=0 and prop_code in (select prop_code from resi_sell where login_id='71727' union select prop_code from comm_sell where login_id='71727' union select prop_code from land_sell where login_id='71727') union (select mb1.Login_Id,mb1.Star,mb1.Subject,mb1.Prop_Code,mb1.maildate as visitdate, mb1.maildate as replydate,mb1.mailread,mb2.hitid,mb2.mailid from (select hitid,max(mailid) as mailid from mailbox group by hitid) as mb2 inner join mailbox as mb1 on mb1.hitid = mb2.hitid and mb2.mailid = mb1.mailid where login_id<>71727 and deleted=0 and mb2.hitid in (select hitid from propertyhittable where login_id='71727') ) order by replydate desc

i got
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Any help?