943,831 Members | Top Members by Rank

Web Development > ASP > sql help
Ad:
  • ASP Discussion Thread
  • Unsolved
  • Views: 1731
  • ASP RSS
Permalink
Oct 24th, 2008
0

sql help

Expand Post »
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.
Reputation Points: 10
Solved Threads: 0
Light Poster
dips255 is offline Offline
36 posts
since Jun 2008
Permalink
Oct 25th, 2008
0

Re: sql help

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

ASP Syntax (Toggle Plain Text)
  1.  
  2. 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!
Last edited by Baradaran; Oct 25th, 2008 at 3:51 pm.
Reputation Points: 11
Solved Threads: 7
Junior Poster in Training
Baradaran is offline Offline
88 posts
since Feb 2007
Permalink
Oct 25th, 2008
0

Re: sql help

Hi,
To make it very simple, add a unique ID to the table, then use
ASP Syntax (Toggle Plain Text)
  1.  
  2. 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!
Last edited by Baradaran; Oct 25th, 2008 at 3:51 pm.
Reputation Points: 11
Solved Threads: 7
Junior Poster in Training
Baradaran is offline Offline
88 posts
since Feb 2007
Permalink
Oct 26th, 2008
0

Re: sql help

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.
Reputation Points: 10
Solved Threads: 0
Light Poster
dips255 is offline Offline
36 posts
since Jun 2008
Permalink
Oct 26th, 2008
0

Re: sql help

Hi,
you have to use group by in your sql command:
ASP Syntax (Toggle Plain Text)
  1. 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!
Reputation Points: 11
Solved Threads: 7
Junior Poster in Training
Baradaran is offline Offline
88 posts
since Feb 2007
Permalink
Oct 27th, 2008
0

Re: sql help

still not getting

sql Syntax (Toggle Plain Text)
  1. 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.
Last edited by peter_budo; Nov 2nd, 2008 at 5:13 am. Reason: Keep It Organized - please use [code] tags
Reputation Points: 10
Solved Threads: 0
Light Poster
dips255 is offline Offline
36 posts
since Jun 2008
Permalink
Oct 27th, 2008
0

Re: sql help

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

sql Syntax (Toggle Plain Text)
  1. SELECT max(HitId) as [ID], LoginName, Prop_code, Visitdate FROM HitTable GROUP BY LoginName, Prop_code, Visitdate.
Last edited by peter_budo; Nov 2nd, 2008 at 5:13 am. Reason: Keep It Organized - please use [code] tags
Reputation Points: 11
Solved Threads: 7
Junior Poster in Training
Baradaran is offline Offline
88 posts
since Feb 2007
Permalink
Oct 31st, 2008
0

Re: sql help

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:

sql Syntax (Toggle Plain Text)
  1. 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?
Last edited by peter_budo; Nov 2nd, 2008 at 5:14 am. Reason: Keep It Organized - please use [code] tags
Reputation Points: 10
Solved Threads: 0
Light Poster
dips255 is offline Offline
36 posts
since Jun 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in ASP Forum Timeline: Accessing IIS site
Next Thread in ASP Forum Timeline: View multiple pages using single file





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC