ASP RSS ASP RSS

sql help

Reply

•
•
Join Date: Jun 2008
Posts: 25
Reputation: dips255 is an unknown quantity at this point 
Solved Threads: 0
dips255 dips255 is offline Offline
Light Poster

sql help

 
0
  #1
Oct 24th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2007
Posts: 66
Reputation: Baradaran is an unknown quantity at this point 
Solved Threads: 4
Baradaran Baradaran is offline Offline
Junior Poster in Training

Re: sql help

 
0
  #2
Oct 25th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2007
Posts: 66
Reputation: Baradaran is an unknown quantity at this point 
Solved Threads: 4
Baradaran Baradaran is offline Offline
Junior Poster in Training

Re: sql help

 
0
  #3
Oct 25th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 25
Reputation: dips255 is an unknown quantity at this point 
Solved Threads: 0
dips255 dips255 is offline Offline
Light Poster

Re: sql help

 
0
  #4
Oct 26th, 2008
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2007
Posts: 66
Reputation: Baradaran is an unknown quantity at this point 
Solved Threads: 4
Baradaran Baradaran is offline Offline
Junior Poster in Training

Re: sql help

 
0
  #5
Oct 26th, 2008
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!
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 25
Reputation: dips255 is an unknown quantity at this point 
Solved Threads: 0
dips255 dips255 is offline Offline
Light Poster

Re: sql help

 
0
  #6
Oct 27th, 2008
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2007
Posts: 66
Reputation: Baradaran is an unknown quantity at this point 
Solved Threads: 4
Baradaran Baradaran is offline Offline
Junior Poster in Training

Re: sql help

 
0
  #7
Oct 27th, 2008
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jun 2008
Posts: 25
Reputation: dips255 is an unknown quantity at this point 
Solved Threads: 0
dips255 dips255 is offline Offline
Light Poster

Re: sql help

 
0
  #8
Oct 31st, 2008
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
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the ASP Forum
Thread Tools Search this Thread



archive asp asp.net aspandmssqlserver2005 aspandmssqlserver2005connection aspconnection connection database databaseconnection diagnostics dreamweaver excel fso iis microsoft msmsql mssql2005 mssqlserver2005 mssqlserver2005andasp mssqlserverandasp opentextfile record searchbox selectoption server single specfic sqlserver sqlserverconnection toolkit webserver windows7
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC